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

  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