Information About Resource Waits

Applies to: SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008.

If you need information about resource waits, the following command runs on all versions of SQL Server:


DBCC SQLPERF(WAITSTATS)

Since SQL Server 2005, you can use the the following dynamic management view also:
 

SELECT *

FROM sys.dm_os_wait_stats

Benefits.


They are useful for discovering:  CPU pressure, query parallelism bottlenecks (Cxpacket waits), blocking bottlenecks and IO bottlenecks.

For example, if your top 2 values for wait stats are IO wait stats, then there is an IO bottleneck. Run the query below to know the top 5 wait stats on your server:

 

SELECT TOP 5 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

References.

SQL Server Best Practices Article: "SQL Server 2005 Waits and Queues".
Tom Davidson. November 2006.
 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.