SQL WAITFOR Command to Delay SQL Code Execution

By:   |   Updated: 2022-08-08   |   Comments (5)   |   Related: More > TSQL


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:

  1. WAITFOR TIME - waits for a specific time of day before continuing with the next step in the SQL query.
  2. 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:

Mimic user input for testing

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.

DATETIME in WAITFOR command error

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.

WAITFOR TIME with a WHILE LOOP

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).

WAITFOR CPU usage-only Windows and SSMS

Here the two queries are running but separated by the WAITFOR command.

WAITFOR CPU usage-running 2 queries

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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Aubrey Love Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.

View all my tips


Article Last Updated: 2022-08-08

Comments For This Article




Tuesday, August 30, 2022 - 8:34:10 PM - Aubrey Back To Top (90430)
Van,
Thanks for your comment and for the screen shot. It was most helpful. The CPU usage provided in the article was just a generic result. Every ones results will vary depending on CPU size, CACHE size, memory size and speed, etc. The samples provided here are just vague reflections of possible performance issues but I didnít mean to imply that the WAITFOR command will always prompt issues.
The point I was trying to make is, make sure that your server can handle the work load if you create too many WAITFORs, WHILE LOOPS, etc.
If I have time, Iíll go back and reproduce this WAITFOR scenario with a thousand delays. Itís going to take some to build, but Iím interested in the performance hit.
Thanks for sharing.

Tuesday, August 30, 2022 - 12:47:48 PM - Van Back To Top (90428)
Ah I also made a screenshot of that result.
https://imgur.com/dvITR8S

Tuesday, August 30, 2022 - 12:36:08 PM - Van Back To Top (90427)
Good day to You, Aubrey!
I'd like to try and give some useful critics!
I wanted to experience the same you've described, regarding the cost of Delay command.
I've tried to open 15 *tabs* in SSMS, which are connections to the database.
In every *tab* I've started a Delay command followed by a simple Select command:

waitfor delay '00:50:00'
select top(1) * from <tableName>

For the time they were running, the CPU utilization of SSMS stayed below 5%.
sqlservr.exe stayed below 1% CPU for most of the time, though about once every 20 seconds it jumped to 10-15% for 3-4 seconds.
And after the requests were finished, I've observed the load of sqlservr.exe without those requests, and it seems that it is the server's usual behavior to jump to those 10-15% for 3-4sec every ~20sec.

With that all done and said, I would conclude that Delay command was fortunately not implemented with a dumb while loop. And it does not consume noticable amount of CPU. I would love to see some more info on that though, like what would be the load of 100 simultaneous Delays. A thousand? A million?

With kind regards.

Saturday, August 13, 2022 - 4:14:04 PM - Aubrey Back To Top (90379)
Kyle,

Thanks for the reply. Itís always nice to have some feedback. As for using WAITFOR in a proc, Iím glad itís working well for you. Some places donít have the resources (hardware) for running delayed jobs all day on multiple procs. It does take some degree of resources. There are other options for running scripts every few seconds or minutes all day. A couple I could suggest are:
1. SMA OpCon
2. GoAnywhere
Either service would do great for something like that.

Anyway, thanks for the feedback, perhaps others can benefit from your process as well.

Monday, August 8, 2022 - 12:56:59 PM - Kyle Back To Top (90364)
We use WAITFOR delay a lot in procs we have running all day every day. Instead of starting jobs every minute we have jobs that start in the morning and end in the evening and the proc they run has a WAITFOR delay of 1 minute usually, but some are 15 seconds.
We've never had a problem with it. (There, now I've jinxed it.)














get free sql tips
agree to terms