Share via

Wait Stats Introductory References

  • Article

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/jimmymay 

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

Comments

  • Anonymous
    April 26, 2009
    PingBack from http://www.anith.com/?p=32570

  • Anonymous
    April 27, 2009
    Wait stats rock. As I shared recently with friends, when it comes to SQL Server performance, wait stats

  • Anonymous
    May 14, 2009
    The comment has been removed