How to Make SQL Server Act Like A Human By Using WAITFOR

Photo by Fischer Twins on Unsplash

You probably tune your queries for maximum performance.  You take pride in knowing how to add indexes and refactor code in order to squeeze out every last drop your server's performance potential.  Speed is usually king.

That's why you probably don't use SQL Server's WAITFOR command regularly - it actually makes your overall query run slower.

However, slowness isn't always a bad thing.  Today I want to show you two of my favorite ways for using the WAITFOR command.

Watch this week's video on YouTube

You can also watch this week's content on my YouTube channel.

1. Building A Human

Modern day computers are fast.  CPUs perform billions of actions per second, the amount of RAM manufactures can cram onto a stick increases regularly, and SSDs are quickly making disk I/O concerns a thing of the past.

While all of those things are great for processing large workloads, they move computers further and further away from "human speed".

But "human speed" is sometimes what you want.  Maybe you want to simulate app usage on your database or the load created by analysts running ad hoc queries against your server.

This is where I love using WAITFOR DELAY - it can simulate humans executing queries extremely welll:

-- Run forever
WHILE (1=1)
BEGIN
    --Insert data to simulate an app action from our app
    EXEC dbo.BuyDonuts 12
    -- We usually average an order every 3 seconds
    WAITFOR DELAY '00:00:03'
END

Throw in some psuedo-random number generation and some IF statements, and you have a fake server load you can start using:

WHILE (1=1)
BEGIN
    -- Generate command values 1-24
    DECLARE @RandomDonutAmount int = ABS(CHECKSUM(NEWID()) % 25) + 1
    -- Generate a delay between 0 and 5 seconds
    DECLARE @RandomDelay int = ABS(CHECKSUM(NEWID()) % 6)
    EXEC dbo.BuyDonuts @RandomDonutAmount
    WAITFOR DELAY @RandomDelay
END

2. Poor Man's Service Broker

Service Broker is a great feature in SQL Server.  It handles messaging and queuing scenarios really well, but requires more setup time so I usually don't like using it in scenarios where I need something quick and dirty.

Instead of having to set up Service Broker to know when some data is available or a process is ready to be kicked off, I can do the same with a WHILE loop and a WAITFOR:

DECLARE @Quantity smallint = NULL
-- Keep checking our table data until we find the data we want
WHILE (@Quantity IS NULL)
BEGIN
    -- Delay each iteration of our loop for 10 seconds
    WAITFOR DELAY '00:00:03'
    -- Check to see if someone has bought a dozen donuts yet
    SELECT @Quantity = Quantity FROM dbo.Orders WHERE Quantity = 12
END
-- Run some other query now that our dozen donut purchase condition has been met
EXEC dbo.GenerateCoupon

Fancy? No.  Practical? Yes.

No longer do I need to keep checking a table for results before I run a query - I can have WAITFOR do that for me.

If you know there is a specific time you want to wait for until you start pinging some process, you can incorporate WAITFOR TIME to make your checking even more intelligent:

DECLARE @Quantity smallint = NULL
-- Let's not start checking until 6am...that's when the donut shop opens
WAITFOR TIME '06:00:00'
-- Keep checking our table data until we find the data we want
WHILE (@Quantity IS NULL)
BEGIN
    -- Delay each iteration of our loop for 10 seconds
    WAITFOR DELAY '00:00:03'
    -- Check to see if someone has bought a dozen donuts yet
    SELECT @Quantity = Quantity FROM dbo.Orders WHERE Quantity = 12
END
-- Run some other query now that our dozen donut purchase condition has been met
EXEC dbo.GenerateCoupon