WAITFOR Wait Type In SQL Server

In most cases, the WAITFOR wait type does not indicate any performance issues. Though the wait type indicates a small amount of CPU usage when the WAITFOR T-SQL command is executed.

What is the WAITFOR Wait Type?

The WAITFOR wait type is a wait type that gets created in SQL Server when the WAITFOR command gets executed. The WAITFOR command is used to stop the execution for a specific amount of time specified. Usually, the WAITFOR commands are used inside a script to pause the execution for some time. There are many reasons why developers use WAITFOR command.

While using the WAITFOR command, the entire transaction will remain open until it completes. It means that the threads will remain open and other transactions will not be allowed to access the same.

In most cases, the WAITFOR commands are not alarming. The command is explicitly used by the person who runs the query.

Example of WAITFOR Demonstration

Step 1: Clear the entries from the sys.dm_os_wait_stats.

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

You might still see a value greater than 0. This is normal. 

Step 2: Run the WAITFOR DELAY command with 5 seconds interval

WAITFOR DELAY '00:00:05';

Step 3: Check the entry again in sys.dm_os_wait_stats to see the WAITFOR wait type generated

FROM sys.dm_os_wait_stats
WHERE wait_type = 'WAITFOR';

You will notice an increase in the wait_time_ms for WAITFOR wait type. 


The WAITFOR wait type gets generated while using the WAITFOR command in SQL Server. The wait type does not indicate any performance issues. If you are using more WAITFOR commands in the query then the WAITFOR wait type as well will be increased. Suppose you wish to lower the WAITFOR wait type, then check for your scripts to remove the WAITFOR commands. 


Post a Comment