Delay SQL Code Execution with SQL WAITFOR

Problem

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.

Solution

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.

WAITFOR Command

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.

WAITFOR Examples

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:

monitor system processes throughout the day at one-hour intervals

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.

WAITFOR CPU usage-3 instances of SSMS open

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

Summary

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.

Next Steps

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

Resources

Community

Subscribe