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: http://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: http://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: http://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  


URL: http://sqlcat.com/whitepapers/archive/2009/04/14/troubleshooting-performance-problems-in-sql-server-2008.aspx


Date: 3/2009


 


<edit (4/2009): References added: SQL Server 2005 Waits and Queues & Troubleshooting Performance Problems in SQL Server 2008>

Comments (4)

  1. alikl says:

    Thanks for sharing this resource!

  2. I think the general preference is with Sql 2005, due to the ease of use and features.

  3. This is the first of a four-part series: Introduction to Query Parallelism (this post) Flipping the Bit