Wait Stats Introductory References

For those of you who are already facile with SQL Server wait stats, there’s nothing new here.  However, if you’re a n00b, this is a great place to start.

As I commented here on SQL MVP Jason Massie’s blog, I first became aware of the importance of wait stats in an editorial for SQL Mag by Brian Moran several years ago:

Waits and Queues: Performance-Tuning Gems
Brian Moran
April 22, 2004
<subscription required>

Brian wrote that Tom Davidson’s article published a few months prior was “the first truly new SQL Server…performance-tuning information that I’ve run across in several years”.

Such a profound statement from one of my heroes got my attention!  So I re-visited Tom’s original article:

Opening Microsoft’s Performance-Tuning Toolbox
Tom Davidson
January 2004
<subscription required>

I’ve been an evangelist of wait stats ever since.

Today I Use Waits-&-Queues
Today one of my primary references is his whitepaper written for SQL Server 2005.  I reference it in this post:  SQL Server: 4-Step Performance Troubleshooting Methodology—Introduction.

SQL Server 2005 Waits and Queues
Authors: Tom Davidson
Updated By: Danny Tambs
Reviewer: Sanjay Mishra

Other References
Besides the citations above, Gert “The Data Dude” Drapers’s site www.sqldev.net used to have some of the only coherent information on wait stats.  The material is a bit dated now, but still worthy of your time, especially if you’re new to wait stats:  sp_waitstats, SQL Server 2000 Wait Types, & DBCC SQLPERF(WAITSTATS).

The BOL article is here:  sys.dm_os_wait_stats (Transact-SQL).

I’ve relied on this KB article many times:  Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005.

I most often use the wait stats query I wrote & published here:  Drum Roll, Please…The Debut of The SQL DMV All-Stars Dream Team!.  In addition to the built-in result set, the wait stats all-star calculates resource wait time for you.

Learning to interpret wait stats is fundamental to mastering SQL Server performance tuning.  What are you waiting on?  Good luck!


Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services

This post was written with the PracticeThis.com plugin for Windows Live Writer

Comments (3)

  1. Jimmy May has compiled a great white paper on Disk Partition Alignment Best Practices for SQL Server