SQL WAITFOR Command to Delay SQL Code Execution
One of the most rarely used commands in SQL is the WAITFOR command. It is one option to invoke a delay in program execution in absentia. Because it is sparsely used, how and where it can be applied when needed is often forgotten. For example, we could use this to mimic a user response or input or perhaps to collect data at certain intervals during the day.
The SQL Server WAITFOR command allows you to wait for a specified time or a set amount of time to lapse before executing the next step in your sequence of events. We will take a look at a few examples.
It's important to note that there are only two options (parameters) available with the WAITFOR command:
- WAITFOR TIME - waits for a specific time of day before continuing with the next step in the SQL query.
- WAITFOR DELAY - dependent on a time-lapse in hours and minutes before executing the next step in the SQL query.
WAITFOR TIME Points of Interest
When using a WAITFOR TIME command, you cannot specify a date in this option directly. It's strictly based on the "time of day". Also, if you start the SQL query that contains a WAITFOR TIME command after that specified time of day has passed, the program will run until the specified time is reached on the following day. Note: If you use a variable, you can specify a date and time. See the examples in the "Variables in the WAITFOR Command" section below.
WAITFOR DELAY Points of Interest
When using the WAITFOR DELAY command, the time delay cannot exceed 23 hours and 59 seconds. Anything longer will return an error. The time format for the WAITFOR TIME and WAITFOR DELAY must be displayed in military format. An example is entering 1:00 pm as 13:00. You can specify the time or delay to the hundredths of a second, but no further. As an example, using 13:01:05.001 is acceptable, but 13:01:05.001.002 is not.
In its simplest form, the WAITFOR command can be written like the code samples below.
-- Delay the process by 20 seconds: WAITFOR DELAY '00:00:20'; GO -- Delay the process until 6:15 PM WAITFOR TIME '18:15:00'; GO
Consider the following examples. First, we need to monitor our system processes throughout the day at one-hour intervals. (This sample only shows three instances of the delay and query execution for simplicity's sake.)
-- Initial run SELECT GETDATE() AS 'Run Time', COUNT(*) AS 'Number Of System Processes' FROM sys.sysprocesses; GO -- Wait 1 hour WAITFOR DELAY '01:00'; -- Second run SELECT GETDATE() AS 'Run Time', COUNT(*) AS 'Number Of System Processes' FROM sys.sysprocesses; GO -- Wait 1 hour WAITFOR DELAY '01:00'; -- Third run SELECT GETDATE() AS 'Run Time', COUNT(*) AS 'Number Of System Processes' FROM sys.sysprocesses; GO
Check out the results:
Second, a DBA would like to mimic user input for testing. Below is a sample of what this process might look like.
INSERT INTO empTable (FirstName) VALUES ('Kim'); SELECT * FROM empTable; GO WAITFOR DELAY '00:00:05' INSERT INTO empTable(FirstName) VALUES ('Catherine'); SELECT * FROM empTable; GO WAITFOR DELAY '00:00:10' INSERT INTO empTable(FirstName) VALUES ('Aaron'); SELECT * FROM empTable ; GO WAITFOR DELAY '00:00:15' INSERT INTO empTable (FirstName) VALUES ('Ben'); WAITFOR DELAY '00:00:20' SELECT * FROM empTable;
And here are the results:
Using Variables with the WAITFOR Command
Earlier, we mentioned that you could not specify a date when using the WAITFOR TIME command. While true, you can use a variable in a WHILE LOOP to select a specific date for executing the WAITFOR command.
If you try putting the DATETIME directly in the WAITFOR command, you will receive the error "Incorrect time syntax in the time stringÖ." as shown below.
Another common mistake is using "WAITFOR DATETIME" instead of ‘WAITFOR TIME" with the WAITFOR command. This will also return an error, "ĎDATETIME' is not a recognized WAITFOR option."
Remember, your only two options are WAITFOR TIME and WAITFOR DELAY. There are no exceptions.
WAITFOR TIME with a WHILE LOOP
In the following scenario, we can create a variable with a DATETIME format to delay the query execution until a specified date, like tomorrow or the next day.
DECLARE @startTime1 DATETIME DECLARE @dummy1 INT SET @startTime1 = '2022-07-19 08:45:00' WHILE GETDATE() < @startTime1 SET @dummy1 = 0; GO SELECT GETDATE() AS 'Fist DateTime'; GO WAITFOR TIME '08:55:00' SELECT GETDATE() AS 'Second DateTime'; GO DECLARE @startTime2 DATETIME DECLARE @dummy2 INT SET @startTime2 = '2022-07-20 08:45:00' WHILE GETDATE() < @startTime2 SET @dummy2 = 0; GO SELECT GETDATE() AS 'Fist DateTime'; GO WAITFOR TIME '08:55:00' SELECT GETDATE() AS 'Second DateTime'; GO
Here are the results.
The above command ran on July 18, 2022, at 08:30 AM. It finished executing at 08:55 on July 20, 2022.
For this example, two variables were created, one to hold tomorrow's date and one to hold the following day's date. The variable was set for the WHILE loop to those two dates and compared to the current date/time. Once the conditions in the WHILE loops were met, it triggered the trailing WAITFOR command (the next step in the query).
The condition of the WHILE loops must be met before the WAITFOR command can run. This allows for the implementation of a date while using the WAITFOR command. Although, I don't see a real justification for doing it this way when you can simply use the WHILE loop to achieve the same outcome. However, it's an option for you to use if needed.
Getting Your Results on Time
One of the pitfalls when using the WAITFOR command, or any SQL command, is batch separation. If you want or need to see the results of your queries as they execute, you must pay attention to the placement and usage of the "GO" separator.
In this first sample, none of the results are displayed until the entire query is finished.
SELECT GETDATE() AS 'FirstRun'; WAITFOR DELAY '00:00:05'; SELECT GETDATE() AS 'SecondRun';
However, in the following scenario, which uses the "GO" separator, we see the first SELECT statement results immediately and the second SELECT statement results 5 seconds later.
SELECT GETDATE() AS 'FirstRun'; GO WAITFOR DELAY '00:00:05'; GO SELECT GETDATE() AS 'SecondRun'; GO
Try running the above queries to see the real-world results in action.
Drawbacks to the WAITFOR Command
Below are some drawbacks and issues to avoid when using the WAITFOR command.
WAITFOR in Triggers
When the WAITFOR command is used inside a trigger, it holds the transaction and any locks implied in an open status. It is generally recommended to never put anything slow, potentially slow, or delayed inside a trigger. The same applies to stored procedures or any other table manipulation query. Using the WAITFOR command to start a trigger is something entirely different.
Misused, the WAITFOR command could hinder other users from entering data into a table or even reading from a table. Not getting the correct data from a table due to a WAITFOR command holding locks open is called "dirty data".
WAITFOR CPU Usage
So, how much of a resource hog is the WAITFOR command? Surprisingly, not much. In the images below, we compare the computer baseline of CPU usage with the execution of a SQL query that returns 2,942,637 records from a sample database. The query then waits for 2 minutes using a WAITFOR command and reruns the same query to display the same 2,942,637 records.
The image below is the baseline, where nothing is running except Windows and SSMS (no queries open in SSMS).
Here the two queries are running but separated by the WAITFOR command.
As you can see, during the WAITFOR command, the CPU usage dropped to a minimal amount, as reflected in the baseline test chart sample. This is with only one query running. In all, the WAITFOR command accounted for about 10% CPU utilization.
Additionally, three WAITFOR commands ran simultaneously in three different SSMS instances and nothing else on the test system. During the WAITFOR command, the CPU utilization was about 30%. Please note that if you have 10 instances of the WAITFOR command running simultaneously, you can expect it to hit around 100% CPU utilization. (These numbers may vary based on your system's performance and query details.)
Below is the CPU utilization snapshot when three instances of SSMS were open, each running a WAITFOR command on the test system.
For the record, the system I used for testing is the following:
- Dell OptiPlex 7070
- Processor: 9th Generation Intel Core i7-9700 3.0GHz to 4.7GHz 8 core 12MB Cache
- RAM: 32gig DDR4 non-ECC
- Drive 1: Toshiba 512GB PCIe NVME Class 40
- Drive 2: Samsung 850 Pro GT 730 500GB SSD
- Video: NVIDIA GeForce GT 730 2GB
In conclusion, there are several options, variations, and limitations when using the WAITFOR command. Consider using a WHILE loop or creating a scheduled job if you need a delayed process.
- Try implementing the WAITFOR command in your daily testing.
- A learn-by-doing approach helps you retain what you have learned.
- Remember, never experiment with the WAITFOR command in your production environment.
- Check out the inspiration for this article: Delaying Code Execution with WAITFOR
- Microsoft Docs reference: WAITFOR
About the author
View all my tips
Article Last Updated: 2022-08-08