This is a short note explaining he sys.dm_os_wait_stats DMV.
This DMV returns the following columns/values regarding waits encountered by executed threads:
- Wait_type
- waiting_tasks_count - The number of waits on this wait type.
- wait_time_ms - The total wait time for this wait type in milliseconds (including signal_wait_time_ms)
- max_wait_time_ms
- signal_wait_time_ms The difference between the time the waiting thread was signalled and when it started running.
Most of these columns are self-explanatory with the exception of the last one, signal_wait_time_ms, so I’ll explain the last column in a little more detail…
A thread enters a wait state when the resource it is waiting for is not available.
Once the resource becomes available, the waiting thread is signalled. However, the CPU might be busy at this point serving other threads.
The attribute signal_wait_time_ms indicates the time it took from the moment the thread is signalled that the resource is available until the thread gets CPU time and starts using the resource.
As you have probably guessed, high values in this attribute can typically indicate CPU problems.
The wait types encountered by SQL server are a good indicator of the queues or bottlenecks facing a SQL Server instance, so using this DMV is a useful tool when using the waits and queues troubleshooting methodology
You can reset the values in the sys.dm_os_wait_stats DMV by running the following command: You
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
GO
The values are also reset when SQL Server restarts.
