When it comes to troubleshooting performance issues with SQL Server queries, you don’t need an advanced degree in rocket surgery.
You just need an idea of what the possible bottlenecks are and then apply some common sense. I “bucketize” performance issues by following a process of asking some simple questions. It goes something like this:
Are all queries affected, or just a subset of queries affected?
If all queries are having performance issues then I will want to examine settings that affect the entire instance, such as memory settings, or high CPU utilization. I will do this first before trying to examine any one particular query.
If only a subset of queries (or users, or a particular application) are affected then I will focus efforts on those queries first. Otherwise I may be wasting time trying to fix one query without addressing the root cause of the performance issue affecting all queries. A great example has to do with virtualization. You do not want to waste time trying to tune one query when the reason is with the host. Better to spend five minutes talking to your server admins about the memory for your guest than to waste time trying to get a query to suddenly run faster with less memory available.
Just being able to diagnose “all versus some” in the first five minutes of triage in a production down situation is invaluable. You don’t want to waste time as you begin to form your action plan to correct the problem and bring performance back to within acceptable limits.
So I’ve started compiling a list of resources that I feel help to show how troubleshooting doesn’t have to be a hard exercise. Here is my list. I will try to keep these updated over time, too, in a similar way to my compilation of Hekaton resources:
Troubleshooting: Blog Posts
Here are a handful of the blog posts I find myself referring others to read whenever I believe they need to learn some of the fundamentals for troubleshooting SQL Server performance. Don’t let the SQL version numbers (2005, 2008) fool you, the content in the papers outlines a methodology that is still applicable today:
- Troubleshooting Performance Problems in SQL 2005: http://technet.microsoft.com/library/Cc966540
- A Performance Troubleshooting Methodology for SQL Server: https://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/
- How to Troubleshoot Connecting to the SQL Server Database Engine: http://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx
- I, Monitor: http://realsqlguy.com/i-monitor
Troubleshooting: Books
Here’s a list of books I recommend for the “accidental DBA” that needs some help on the basics of troubleshooting:
- DBA Survivor: Become a Rock Star DBA
- Troubleshooting SQL Server – A Guide for the Accidental DBA
- SQL Server 2012 Query Performance Tuning
Troubleshooting: Videos
Here is a video from Conor Cunningham and Boris Baryshnikov at the 2010 PASS Summit. The video is an hour long but well worth the time:
- Expert Query Performance Troubleshooting: http://youtu.be/Nbxg5crWq38
Troubleshooting: Whitepapers
Here is the gold standard for troubleshooting query performance problems. It’s the original whitepaper from Tom Davidson that discusses waits and queues. I also list out any other papers that I believe will help:
- SQL Server Best Practices Article: http://technet.microsoft.com/en-us/library/cc966413.aspx
- Plan Caching in SQL Server 2008: http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx
- Plan Caching and Recompilation in SQL Server 2012: http://msdn.microsoft.com/en-us/library/dn148262.aspx
6 thoughts on “SQL Server Troubleshooting Useful Links”