SQL WAITFOR Delay Advanced Testing Options

By: Aaron Bertrand | Updated: 2023-12-11 | Comments | Related: > TSQL

Problem

WAITFOR is a very useful command to prevent further operations from occurring until a certain amount of time has passed (WAITFOR DELAY) or until the clock strikes a specific time (WAITFOR TIME). These commands are covered quite well (including pros and cons) in a previous tip, "SQL WAITFOR Command to Delay SQL Code Execution."

WAITFOR DELAY is also commonly used to "let SQL Server breathe." For example, when performing some operations in a loop, on a resource-constrained system, an artificial delay can potentially allow for more concurrency, even if it makes the intensive operation take longer.

But these commands can also be used to synchronize multiple query windows for local testing and debugging, and I thought I would share a recent example.

Solution

I was testing a new feature in Azure SQL Database called "optimized locking." At a very high level, this new approach to locking holds granular locks for very short bursts, even inside much longer-running transactions. This has the potential to radically improve scaling and concurrency without having to change queries or application code.

Naturally, I wanted to test the behavior of an existing system and compare it to the new methodology. To do so, I would need to:

Create an Azure SQL Database (and a similar database on-prem) with some large tables.
Set up multiple query windows that try to update many rows of overlapping data simultaneously.
- I could have used SQLQueryStress, but then I couldn't talk about WAITFOR!
While those were running, poll frequently for key metrics like lock counts, lock memory, blocking, and wait types.

I'll focus on the second bullet. I can skip the database creation part since you can all do that, and I will leave the metrics collection for another time.

I had a query that did something like this, and I wanted to synchronize the start time across multiple windows to give the best chance for blocking:

 WAITFOR TIME '16:20';
 BEGIN TRANSACTION; 
   DECLARE @i int = 1, @batch_size int = 500;
   WHILE @i <= 1000
   BEGIN
     WITH x AS 
     (
       SELECT ID, LastToucher
         FROM dbo.SomeTable
         WHERE ID >  (@batch_size*(@i-1))
           AND ID <= (@batch_size*(@i))
     )
     UPDATE x SET LastToucher = @@SPID;
     WAITFOR DELAY '00:00:01';
     SET @i += 1;
   END
 COMMIT TRANSACTION;

The problem is that whenever I want to run a new set of tests, I have to go to every query window and change the string to something that gives me enough buffer to finish the last query window before the first one starts. Also, if I wanted to increase the delay from 1 to 2 seconds, I'd need to make, say, eight changes – and that's when I only specify a delay in one place in each script.

To get around these limitations, I used a feature I don't see used often: WAITFOR.

WAITFOR Takes Variables

That's right! Most code examples you've seen have a hard-coded string literal, for example:

WAITFOR DELAY '00:00:05';
WAITFOR TIME '20231101 16:20';

But you can also do this:

DECLARE @seconds tinyint = 5; -- if < 60!
DECLARE @delay varchar(8) = '00:00:' + RIGHT(CONCAT('0', @seconds), 2);
WAITFOR DELAY @delay;

A couple of things to keep in mind:

If you want to define the delay as more than 59 seconds, you'll need something more robust that can change seconds to HH:mm:SS.
If you want the delay to be less than a second, you're in luck, as milliseconds (though rarely used) are supported, e.g., WAITFOR DELAY '00:00:00.25';
WAITFOR TIME accepts time without a date component, so be wary of specifying a time that is "too soon." If it is 16:19 and I scheduled for 16:20, I need to hit execute before the minute flips, or I'll need to wait 24 hours.

That said, if you have a bunch of WAITFOR commands in a single query window and want to experiment with different delay values, this could be a great way to make a change once and have it impact all the instances in the script.

Variables can't persist through batches, though – so if you have GO anywhere in there or have WAITFOR commands inside dynamic SQL or, like me, you want to impact multiple independent query windows, you'll still need to make the change in multiple places.

I worked around this latter issue using a configuration table. Fictitiously, but with a couple of self-preserving constraints:

USE DBRE;
GO
CREATE TABLE dbo.TestConfig
 (
   StartTime     varchar(20),
   DelaySeconds  decimal(6,2),
   CONSTRAINT    CK_Time  CHECK -- prevent garbage
   (TRY_CONVERT(datetime, StartTime) IS NOT NULL),
   CONSTRAINT    CK_Delay CHECK -- keep it under an hour
   (DelaySeconds BETWEEN 0 AND 3599) 
 );
 INSERT dbo.TestConfig(StartTime, DelaySeconds)
   VALUES('16:20', 1.25);

NOTE - While not intuitive, StartTime is a string simply to allow for full and unambiguous date/time formats without suffering loss of precision when converting to a string, as well as time only without inheriting 1900-01-01. Normally, I would frown upon storing a date/time as a string, but, in this case, for local testing where I am in complete control and the value can only be used as a string, I'll cut myself some slack.

Then, in each query window, we need a pretty complicated expression to account for all possible outcomes. Thankfully, we don't have to touch it during testing (and could opt to abstract it away into a function):

DECLARE @timeString  varchar(20), 
        @delayString varchar(11);
SELECT @timeString  = StartTime,
       @delayString = CONCAT_WS(':',
       RIGHT(CONCAT('0', CONVERT(int, DelaySeconds) / 6000), 2), 
       RIGHT(CONCAT('0', CONVERT(int, DelaySeconds) / 60),   2),
       RIGHT(CONCAT('0', DelaySeconds % 60), 5))
FROM dbo.TestConfig;
WAITFOR TIME @timeString;
BEGIN TRANSACTION; 
DECLARE @i int = 1, @batch_size int = 500;
WHILE @i <= 1000
BEGIN
 WITH x AS 
 (
   SELECT ID, LastToucher
     FROM dbo.SomeTable
     WHERE ID >  (@batch_size*(@i-1))
       AND ID <= (@batch_size*(@i))
 )
 UPDATE x SET LastToucher = @@SPID;
 WAITFOR DELAY @delayString;
 SET @i += 1;
END
COMMIT TRANSACTION;

Now, I still needed to hit F5 on all those query windows, but using this technique, I could change the target start time and loop delay in only one place. (I kept a separate query window open with a simple UPDATE command ready for changes.)

Conclusion

I hope this was a helpful demonstration of using variables and configuration tables to make WAITFOR more flexible. This is a niche implementation, but some lesser-known capabilities might be more broadly useful.

As an aside, I haven't truly assessed the real-world impact of optimized locking, but so far, it looks promising. One of the problems is that performance factors are just so different. One of the major things I came across in lower-tier Azure SQL Database, for example, is heavy log write throttling (LOG_RATE_GOVERNOR) that potentially has more impact on concurrency than locking ever could. Until this feature is made available on-prem, there is no way to perform legitimate apples-to-apples tests unless you contact Azure support and have the new feature turned off.

Next Steps

See these tips and other resources:

SQL WAITFOR Command to Delay SQL Code Execution
All Management Studio tips
All Azure Data Studio tips
Get an early look at optimized locking
Finally, don't discount SQLQueryStress as a useful load testing tool (and if you want a more automated solution, look at sqlstresscmd)

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-12-11

Advanced testing with WAITFOR in SQL Server client tools