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.

A wait a day: WAITFOR

This is part 1 of a series dedicated to SQL Server Waits. Part 1 will cover the WAITFOR statement, which does not introduce a wait, but I thought we could cover the statement.

The WAITFOR statement will block the processing of a batch, stored procedure or transaction based on the time or interval defined with the command.

The statement below waits 1 minute .
WAITFOR DELAY '00:01:00'

This statement below waits until 11:02 AM local time
WAITFOR TIME '11:02:00'

Example
DECLARE @HireTime Char(8)
SET @HireTime = '12:00:00'
--Select all employees in the system as of noon today
WAITFOR TIME @HireTime
SELECT * FROM AdventureWorks.HumanResources.Employee

If you have set the @HireTime variable far enough into the future, you can run the statement below to see what's happening behind the scenes.
SELECT r.Wait_type, r.wait_time, r.last_wait_type, r.open_transaction_count, t.text as SQLStatement
FROM sys.dm_exec_requests r .
cross apply sys.dm_exec_sql_text(r.Plan_handle) t.
WHERE session_id > 50

Be cautious when using the WAITFOR Statement as SQL Server uses a seperate thread for each WAITFOR occurance.
You cannot use WAITFOR in view definitions or when opening a CURSOR.