Wait Stats Introductory References
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
https://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
https://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-&-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
11/2006
https://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
https://blogs.msdn.com/jimmymayThis post was written with the PracticeThis.com plugin for Windows Live Writer