When called in to diagnose a performance issue, what are the 1st things you review? How much of the environment do you check before going straight to the line of code you think is at fault? Because of the urgency many DBAs and developers make the mistake of diving straight into the code looking for an answer. What if the solution is not in the code but rather in the overall environment? Environmental troubleshooting is often a quick and overlooked method for discovering and configuring SQL Server. Perform at a higher level without the need for application change controls!

NOTE: this article is the summary of a 3 part series on optimizing SQL Server configurations along with Windows Server and VMware. Please read these first then return to read the rest.

• Part 1: SQL Server Environmental Diagnostics Guide
• Part 2: SQL Server Environmental Diagnostics Guide – Windows Server
• Part 3: SQL Server Environmental Diagnostics Guide – VMware

What is Environmental?

Let’s start by defining terms. By environmental I mean factors that affect database performance outside of the code. The remediation of issues can be accomplished without altering SQL, no code change request, no release management necessary. Poor performance can often be addressed by doing a quick review of the overall system and ensuring there are not horrible defaults in use.

This is the low hanging fruit we can get before deep diving into the code

Before you start banging your head against the hood of the car take a walk around and look at the big picture first. It is a common thing to see junior DBAs go right for the most complex area they can identify and ignore the obvious issues which are plain to the eyes of a veteran DBA. Therefore I advise you to…

Trust, but verify! Start troubleshooting with a quick environmental look before deep diving into code.

Diagnosing SQL Server Performance Issues

There are many ways to diagnose performance issues with a SQL Instance and the infrastructure which the supported application runs on. After all – we are not spinning up databases for no reason but to hold application data. Here is a quick list of the resources a DBA has at their disposal to identify and properly diagnose performance issues

• DMVs – dynamic management views. Query catalog information and see what is running under the hood
• Activity Monitor – everyone likes a GUI and this is a good start. If you can start with DMVs, sp_who2, sys view that is even better
• PerfMon – Windows Server performance monitor
• SQL Profiler – deprecated I know but old habits die hard in some companies…
• Extended Events – Profiler’s more powerful cousin, shows much more info and better supported by Microsoft
• Microsoft Utilities – free, lightweight, and from the vendor
  • PSSDiag – MS support may want the output from this utility
  • SQLdiag – general purpose diagnostics collection utility
• 3rd Party Software – some of the common ones to be aware of are below. There are many others
  • Redgate SQL Monitor (developers – you are using SQL Prompt, right??)
  • Idera SQL Diagnostic Manager
  • SolarWinds Database Performance Analyzer
  • Quest (Dell) Spotlight
• Community Open Source – the Microsoft SQL Server community is an active group that is very helpful and full of zeal about their craft. They also have some well established scripts you should know about:
  • Glenn Berry’s SQL Server Performance Diagnostic Queries
  • Adam Machanic’s famous WhoIsActive is used in many shops.
  • Ola Hallengren has a SQL Server Maintenance Solution for common DBA tasks and some performance checks. The scripts are now on GitHub.
  • Brent Ozar Unlimited published and open sourced sp_Blitz along with some other goodies.
  • …and many more!

As you can see there are many different ways to keep on top of system performance. But even if you ignore those doing some quick checks of the basics can save time and frustration. Perform a quick system health check! Most of all…

Before diving into code or deep into analysis perform a quick system health check! Use the many tools available.

Benefits of a System Health Check

I can think of a few:

• Default are typically really bad – do not trust they are best for your environment.
• Best practices – I know rules are meant to be broken and sometimes are just guidelines but if you don’t know enough you’d best stick to them until you can make the case to deviate from them.
• System optimization – enterprise hardware isn’t cheap and software licenses add up. Typically administrators, developers, and engineers are well paid too (and perhaps those pesky end users too!). Why wouldn’t you want to get the most return on investment (ROI) that you can? Sometimes it really is as easy as clicking buttons on a GUI…
• Sometimes the poor environment can mask coding issues and make them look like bugs. For example – it is hard to hack through default power settings on Windows Server.
• The cost-benefit is in your favor. It doesn’t take a long time to have a quick check of the configurations in SQL Server, the OS, and possibly the hypervisor if investigation merits. This can save a lot of time.
• Your end users will thank you – a better running system is appreciated by everyone!

In conclusion, I hope this helps save someone time when troubleshooting a performance complaint. It can be tempting to jump right into execution plans and index strategies but before you dedicate that time be sure the system isn’t working against your glorious, well tested, clean, self-documenting code!

Has this post helped you out? Please subscribe!