SQL Server: 4-Step Performance Troubleshooting Methodology--Introduction
Introduction: The Classic Protocol, Amended
Introduction
My customers are often surprised to learn about the existence of a documented methodology for troubleshooting SQL Server performance. Though the document is well-known, many customers fail to appreciate just how important it is. The protocol was subsequently re-published in the most recent of the books from Kalen Delaney's Inside SQL Server 2005 series.
Methodology
The methodology is comprised of the following three groupings to which I amend a fourth step:
1. Resource bottlenecks:
i. Memory
ii. CPU
iii. IO
2. Tempdb Bottlenecks
3. Slow-running Queries
i. Statistics
ii. Missing Indexes
iii. Blocking
4. Plan Cache Analysis
Value
The vast majority of SQL Server performance challenges can be identified by analyzing these areas. This information is vital for efficiently troubleshooting SQL Server performance challenges. This methodology provides a systematic approach versus the timeless yet inefficient approach of “throw enough spaghetti at the wall, & some of it will stick”.
This methodology aligns with the use of engineering discipline. I use it faithfully in my work. Adopting this methodology will enhance your efficiency & accelerate the identification of root causes.
The now classic methodology is fully documented in the citations below. I use the full methodology in each-&-every one of my engagements. Plan cache analysis is my own addition to the methodology & has been an important ingredient for success at several customer sites. Stay tuned for additional information on the relationships involved, the tools I use & how to use them, & other aspects.
SQL Server 2005 vs. SQL Server 2008
You might ask, “But Jimmy May, this is so-o-o-o 2005. Why in the world would I use it now that SQL Server 2008 has been released?” Why? Because the methodology is fundamental, it applies to all versions of SQL Server, & it not merely works, it rocks.
References
Credit for the methodology goes to the authors of this classic TechNet article:
Title: Troubleshooting Performance Problems in SQL Server 2005
Authors: Sunil Agarwal, Boris Baryshnikov, Tom Davidson, Keith Elmore, Denzil Ribeiro, Juergen Thomas
URL: https://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Date: 10/1/2005
The article is available to read online or you can download the 972KB Word document.
Lead author Sunil Agarwal reprised the methodology in Chapter One “A Performance Troubleshooting Methodology” of the last of the Inside SQL Server 2005 books:
Title: Inside Microsoft SQL Server 2005: Query Tuning and Optimization
Authors: Kalen Delaney, Sunil Agarwal, Craig Freedman, Adam Machanic, Ron Talmage
URL: https://www.microsoft.com/MSPress/books/8565.aspx
Publisher: MS Press
Date: 9/26/2007
ISBN: 978073562196
Here are two Indispensible whitepapers from the SQL CAT team to implement the protocol:
Title: SQL Server 2005 Waits and Queues
Authors: Tom Davidson
Updated By: Danny Tambs
Reviewer: Sanjay Mishra
URL: https://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx
Date: 11/2006
Title: Troubleshooting Performance Problems in SQL Server 2008
Authors: Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng, Burzin Patel
Reviewers: Jerome Halmans, Fabricio Voznika, George Reynya
Date: 3/2009
<edit (4/2009): References added: SQL Server 2005 Waits and Queues & Troubleshooting Performance Problems in SQL Server 2008>