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

URL: https://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

  • Anonymous
    September 01, 2008
    Thanks for sharing this resource!

  • Anonymous
    September 30, 2008
    I think the general preference is with Sql 2005, due to the ease of use and features.

  • Anonymous
    November 28, 2008
    This is the first of a four-part series: Introduction to Query Parallelism (this post) Flipping the Bit

  • Anonymous
    April 27, 2009
    Preamble For those of you who are already facile with SQL Server wait stats, there’s nothing new here.