Preamble 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.
History 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 http://www.sqlmag.com/Article/ArticleID/42441/sql_server_42441.html <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 http://www.sqlmag.com/Article/ArticleID/40925/sql_server_40925.html <subscription required>
I've been an evangelist of wait stats ever since.
Today I Use Waits-&-QueuesToday 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 11/2006 http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx
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!
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev Senior Performance Consultant: SQL Server A.C.E.: Assessment, Consulting, & Engineering Services http://blogs.msdn.com/jimmymay This post was written with the PracticeThis.com plugin for Windows Live Writer
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev Senior Performance Consultant: SQL Server A.C.E.: Assessment, Consulting, & Engineering Services http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer