SQL SERVER – DMV – sys.dm_os_wait_stats
Let us first run the following statement from DMV.
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
GO
“Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance.”
Above statement will show us few of the columns. Here it is quick explanation of each of the column.
wait_type – this is the name of the wait type. There can be three different kinds of wait types – resource, queue and external.
waiting_tasks_count – this incremental counter is a good indication of frequent the wait is happening. If this number is very high, it is good indication for us to investigate that particular wait type. It is quite possible that the wait time is considerably low, but the frequency of the wait is much high.
wait_time_ms – this is total wait accumulated for any type of wait. This is the total wait time and includes singal_wait_time_ms.
max_wait_time_ms – this indicates the maximum wait type ever occurred for that particular wait type. Using this, one can estimate the intensity of the wait type in past. Again, it is not necessary that this max wait time will occur every time; so do not over invest yourself here.
signal_wait_time_ms – this is the wait time when thread is marked as runnable and it gets to the running state. If the runnable queue is very long, you will find that this wait time becomes high.
Additionally, please note that this DMV does not show current wait type or wait stats. This is cumulative view of the all the wait stats since server (instance) restarted or wait stats have been cleared