sys.dm_os_wait_stats :-
There are many Dynamic Management View (DMV) available in SQL Server but The key Dynamic Management View (DMV) that helps us to understand wait stats is sys.dm_os_wait_stats;
sys.dm_os_wait_stats DMV gives us a list of all the different types of wait that threads have encountered,the number of times they have waited on a resource to be available, and the amount of
time waited. The values provided are running totals, accumulated across all sessionssince the server was last restarted or the statistics were manually reset using the DBCC SQLPERF command.
The following columns are available in the sys.dm_os_wait_stats DMV:-
- wait_type – It provides the name of the wait type
- waiting_tasks_count – it gives cumulative total number of waits that have occurred
for the given wait_type and If this number is very high, it is good indication for us to investigate that particular wait type. - wait_time_ms – it gives total amount of time that tasks have waited on this given wait type;
this value includes the time in the signal_wait_time_ms column. - max_wait_time_ms – it represents the maximum amount of time that a task has been delayed.
- signal_wait_time_ms – represents total amount of time tasks took to start executing after being signaled; this is time spent on the runnable queue, and is pure CPU wait. If the runnable queue is very long, you will find that this wait time becomes high.
Finding the most common wait types in sql server through sys.dm_os_wait_stats
SELECT TOP 10 wait_type , waiting_tasks_count , wait_time_ms / 1000.0 AS [wait time in sec] , CASE WHEN waiting_tasks_count = 0 THEN NULL ELSE wait_time_ms / 1000.0 / waiting_tasks_count END AS avg_wait_time_sec , max_wait_time_ms / 1000.0 AS [max wait time in sec] , ( wait_time_ms - signal_wait_time_ms ) / 1000.0 AS [resource wait time in sec] FROM sys.dm_os_wait_stats ORDER BY waiting_tasks_count DESC
Output:-
