Here is a handy little SQL Snippet that will return information about those all important SQL Server Wait Types for your server. It uses the SQL Server Dynamic Management View (DMV) sys.dm_os_wait_stats in order to extrapolate the desired information. A column has also been added to provide details of the percentage of total wait time that a particular Wait Type is responsible for.
T-SQL Query to rank SQL Server Wait Types by highest percentage of total wait time
SELECT
wait_type,
waiting_tasks_count,
max_wait_time_ms,
resource_wait_time_ms = (wait_time_ms - signal_wait_time_ms),
PercentOfAllResourceWaitTime =
(cast((wait_time_ms - signal_wait_time_ms) AS decimal(19,2)) /
(SELECT sum((wait_time_ms - signal_wait_time_ms)) FROM sys.dm_os_wait_stats))
* 100
FROM sys.dm_os_wait_stats
ORDER BY PercentOfAllResourceWaitTime DESC
wait_type,
waiting_tasks_count,
max_wait_time_ms,
resource_wait_time_ms = (wait_time_ms - signal_wait_time_ms),
PercentOfAllResourceWaitTime =
(cast((wait_time_ms - signal_wait_time_ms) AS decimal(19,2)) /
(SELECT sum((wait_time_ms - signal_wait_time_ms)) FROM sys.dm_os_wait_stats))
* 100
FROM sys.dm_os_wait_stats
ORDER BY PercentOfAllResourceWaitTime DESC
I hope you find this SQL Snippet useful in your administration of SQL Server. If you have any queries regarding this snippet, SQL Server Wait Types or anything to do with SQL Server whatsoever then be sure to let me know!
Further Reading
- SQL Server Best Practice Article: Performance Tuning Wait Queues
- More DMV Queries: Identify All Active SQL Server Sessions
- How to identify the most costly SQL Server queries using DMV’s
