Wait types in SQL Server

Troubleshooting performance issues in SQL Server involves studying the wait types and wait times associated with the queries executing. However, the relevance of each of the various wait types in the output can be a mystery.


For SQL Server 2000, the cumulative wait types can be observed using the DBCC SQLPERF(WAITSTATS) command. To clear the current wait times, use DBCC SQLPERF(WAITSTATS, CLEAR). The KB article The waittype and lastwaittype columns in the sysprocesses table in SQL Server 2000 explains the more common ones. A more comprehensive reference is found in Tom Davidson’s excellent article Opening Microsoft’s Performance-Tuning Toolbox. The article’s code download has an extremely useful table which details almost all the wait types and explains their correlation with the performance counters.


For SQL Server 2005, we have a good reference in SQL Books Online itself, as part of the sys.dm_os_wait_stats reference documentation. And if you haven’t read it yet, don’t waste a minute and read the excellent Troubleshooting Performance Problems in SQL Server 2005 white paper.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s