Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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
Comments
Anonymous
April 26, 2009
PingBack from http://www.anith.com/?p=32570Anonymous
April 27, 2009
Wait stats rock. As I shared recently with friends, when it comes to SQL Server performance, wait statsAnonymous
May 14, 2009
The comment has been removed