And we wait!!

Since I am at the airport waiting for a very delayed flight to take off, I thought it would be appropriate to do a post on…WAITFOR!!

As expected, this statement can be used to pause the execution of a batch.  It can also be used to have a batch wait until a specific time to continue the execution.

There are really two options, DELAY and TIME.

DELAY

The DELAY argument is used to pause the execution of the batch.  You can force a batch to wait for up to 24 hours.  This argument uses the “Time To Pass” value to determine how long to wait.  This really means, how much time has to pass before execution of the batch can continue. You cannot pass a date into this argument, it must be a time in the following format: hh:mm:ss.msss.  The time passed must not exceed 24 hours.  If the time does exceed 24 hours, you will get the following error.

If you put WAITFOR DELAY ’23:59:59′, this will run fine, however, if you put WAITFOR DELAY ’24:00:00′, it will fail with the same error as above.

TIME

The TIME argument is used to have a batch pause and continue executing at a specific time.  You will hear this referred to as “time_to_execute”. This argument has the same 24 hour limitation as the DELAY argument.  If you attempt to run this, WAITFOR TIME ’25:48:00′, you will receive the same error as above.

In the code below, the batch will execute the first SELECT and then wait until 1:48 AM to execute the second SELECT.

SELECT *
FROM [Person].[Person]

WAITFOR TIME ‘1:48:00’

SELECT *
FROM [Person].[Person]

GO

When specifying a time, you can use 24 hour time, WAITFOR TIME ’13:48:00′ or you can use AM/PM, WAITFOR TIME ‘1:48:00 PM’.

A point of caution if using WAITFOR in production code, make sure that by using it you are not increasing the timeouts in the application.  You may need to adjust the timeout setting of the application.  Also use a great deal of caution if using this in a transaction..it can make for a bad day!!

Keep in mind, the time specified starts when the thread is scheduled.  If there is a delay in scheduling, it may actually wait longer than expected. This can happen on really busy servers.

The statement below is also important to remember if you decide to use WAITFOR a lot.

“SQL Server monitors the number of threads associated with WAITFOR statements, and randomly selects some of these threads to exit if the server starts to experience thread starvation.” – Microsoft

Per Microsoft, you need to be careful using this because you can potentially increase the number of deadlocks.

Here is a link to Microsoft’s page on this topic.

Here is a very good video by Burt Wagner. I like that he has a stored procedure named:  BuyDonuts!!!

Thanks for visiting my blog.