Saturday, October 29, 2011

A wait a day: sys.dm_os_wait_stats

In part 1 of the A wait a day series, we discussed the self induced WAITFOR statement. Before discussing further waits, I thought it would be helpful to review the sys.dm_os_wait_stats dmv.

The sys.dm_os_wait_stats dmv returns details about waits that threads have encountered. The DMV returns five columns, wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms and signal_wait_time_ms.

In general, there are three types of waits: Resource waits, External waits and Queue waits.

Resource waits include locks, network, latches and disk waits.

Queue waits tend to be seen when a worker is waiting for work or if the worker is idle.

External waits happen when SQL Server is waiting for an external even to complete.

No comments:

Post a Comment