Jimmy May's Blog

SQL Server Performance, Best Practices, & Productivity

Wait Stats Introductory References

Wait Stats Introductory References

  • Comments 3

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-&-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
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

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post
Page 1 of 1 (3 items)