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.
Waits and Queues: Performance-Tuning Gems
April 22, 2004
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
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
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!