SQL Server performance troubleshooting is slowly becoming a complex art… The main idea for creating a separate page is to setup a roadmap to troubleshoot SQL Server performance issues. The approach defined in this roadmap will help identify the bottlenecks and root cause for majority of the performance issues that you face on your SQL Server instances.

The main bottlenecks that would affect performance are:

1. Memory: Physical or Virtual – Your server could be experiencing a physical crunch which would point us to the Available Physical RAM or a crunch on the Virtual Address Space. This would involve finding out the queries which are consuming high memory during compilation or execution and narrow down on the single biggest contributor and work towards rectifying the problem with the query.

2. Disk IO:There could be an issue with SQL Server queries running slowly due to large amount of IO being done by them or a disk performance issue. Steps for troubleshooting disk contention issues

3. CPU: It could be because the queries executing on your server are consuming high amount of CPU time. In such a case, it be statistics that are out-of-date or data that has increased which introduces data skew.

4. Blocking: The queries executing on the server could be blocked and waiting for a particular resource and thus increasing the query duration.

How to perform post-mortem analysis for SQL performance problems?

If you are facing a performance degradation on the SQL instance after an upgrade, we would need to analyze the PSSDIAG collected to find out the top bottlenecks. There are two ways to do this:

1. Comparison Analysis: You identify the a query batch or stored procedure which is a problem and provide us a baseline (w.r.t. normal execution time for the query in question). We capture a “fast” and a “slow” run and we perform a comparison between the two runs and we tell you what is biggest bottleneck: Memory, Disk, CPU or Database/Environment Settings like database statistics, query SET options, difference in parameters etc. Based on what is identified as the culprit, you will be able to identify/suggest action items to help remove the culprit.

2. Bottleneck Analysis: In case you do not have a baseline, you can perform a bottleneck analysis and then stack rank them for you in case there is more than one bottleneck. Based on the analysis, you will be able to identify the biggest bottleneck and can implement action items to mitigate the single largest bottleneck.

I had documented various means to troubleshoot SQL Server performance issues in a SSWUG article using tools available with a SQL Server installation which covers both live and post-mortem troubleshooting. Link: SQL Performance: How Do I Tackle You?

Live Troubleshooting examples
Troubleshooting SQL Blocking Scenarios with Activity Monitor
The Benefits of Using Performance Dashboard
Troubleshooting SQL Server Performance with Standard Reports

Post Mortem Analysis examples
Tackling long running queries with Management Data Warehouse
Performance Troubleshooting using SQL Nexus

This page is a work in progress and I will continue adding more information to this page to turn this into a comprehensive troubleshooting guide for SQL Server performance.