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
SELECT *
FROM sys.dm_os_wait_stats
WHERE
wait_type = 'WAITFOR';
You will
notice an increase in the wait_time_ms for WAITFOR wait type.
Bottomline
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.
0 comments:
Post a Comment