Friday, October 28, 2011

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.

No comments:

Post a Comment