SQL SERVER – Time Delay While Running T-SQL Query – WAITFOR Introduction

Today we will look at one very small but interesting feature of SQL Server. Please note that this is not much known feature of SQL Server. In SQL Server sometime there are requirement when T-SQL script has to wait for some time before executing next statement. It is quite common that developers depends on application to take over this delay issue. However, SQL Server itself has very strong time management function of WAITFOR. Let us see two usage of WAITFOR clause.

Official explanation of WAITFOR clause from Book Online is “Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.”

Option 1 : Waitfor Delay in executing T-SQL
T-SQL runs after particular delay is completed.

SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SELECT GETDATE() CurrentTime

Option 2 : Waitfor Time in executing T-SQL
T-SQL runs after particular time has arrived.

DECLARE @MyDateTime DATETIME
/* Add 5 seconds to current time so
system waits for 5 seconds*/
SET @MyDateTime = DATEADD(s,5,GETDATE())
SELECT GETDATE() CurrentTime
WAITFOR TIME @MyDateTime
SELECT GETDATE() CurrentTime

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

21 thoughts on “SQL SERVER – Time Delay While Running T-SQL Query – WAITFOR Introduction

  1. Have you ever used WAITFOR in a production environment?

    I’ve never really seen much of a use for it and I’d be interested to hear ideas of practical uses for it.

    For any situations where I want to delay the start of a query (e.g. DBCC commands run outside of core business hours), I’ve always used SQL Server Agent instead.

    Like

  2. I have the same questions as John.

    I understand the value of the first example to wait X seconds until doing something else, but I don’t get why you would wait until a specific time to execute.

    Why not use SQL Agent with a scheduled job?

    I assume that second feature simply exists, but that best practices would be to use an agent job?

    Is the benefit perhaps for a one time or semi-random AdHoc style situation?

    Also how does it handle waiting for a specific time to execute?

    Does it check in the background and thus consume resources the entire time it is waiting for the specified date/time or does it have a more efficient way of “sleeping” until it is needed?

    Like

  3. We have the use for a waitfor due to our developers writing a process that returns a status code. We will give it 45 seconds, check the results and continue depending on the results.

    Like

  4. hi,
    I use the WAITFOR command allot in DTS’s while an External Application (WinRar) compacts Access MDB files that have just been created from SQL Views. Once the Delay has timed out I then publish the Zipped files to our website. The WAITFOR command saved me creating lots of different DTS or Batch files can kicking then off at different times.
    Cheers Michael

    Like

  5. Some years ago I could not get a job scheduled through the server (politics) and had to launch a mission critical application that was all in T-SQL every day at the later of:
    1) 5am
    2) when a particular record was written to a table each day. On “bad’ days this could be as late as 7 or 8am

    I didn’t want to keep hitting the server in a tight loop, so I’d leave the SP running to fire at 5am to check for the record, I’d then re-check every 10 minutes.

    As an aside, if “the record” wasn’t in place by 6am I’d normally be on the phone to the person responsible for that process to see what the hold-up was. for major delays I’d cancel the job until I got the all-clear.

    Like

  6. WAITFOR ….
    waits for a specified interval or time but displays the result of all the queries after the last query is executed.

    I need the output of the first query as soon as it gets executed

    Then wait for specified interval

    execute the next query and display 2nd output

    Like

  7. if we schedule a database backup with waitfor clause
    like

    while 1=1
    begin
    waitfor time ’17:36:30′
    backup databse ………………………………..
    end

    does it requires the resources while it is waiting for the time or not.

    will this reduce performance

    Like

  8. WOW… I had no idea whatsoever about this T-SQL feature!

    It definitely made up my day! I am currently using this feature to restart programmatically a SQL Job based on certain conditions.

    Why? Well basically I am facing random hangs during nightly execution of my ETL packages. Every night we extract data from a legacy database called ADABAS. We have to use a third party software called CONNX to retrieve data from it in a relational format. The issue has been rooted to CONNX and they are asking us to upgrade to the latest version before troubleshooting anything. That is something that won’t happen anytime soon. Therefore, I have had to be creative and look for workarounds to get through with the hangs.

    So I am using WAITFOR. Because these random hangs usually get solved by restarting the jobs. These jobs run @ 12:00 AM. So I have created specific jobs to monitor the other jobs at specific times and check if they are taking longer than usual. If they do, I stop the Jobs. This is where WAITFOR comes in. If you stop and restart a job programmatically, SQL will raise and error saying the job is still running… This is because the JOB hasn’t been fully stopped yet when sp_start_job is triggered. Therefore needing WAITFOR in between those exec lines.

    Like

  9. The scenario where we use WAITFOR TIME is when we have Job A trigger Job B. Job A can run at any time with minimal user impact, and has a highly variable start time and duration. Job B tends to run quickly but has an extremely high user impact if it runs during business hours. The “normal” hand off time from A to B should fall between 3 and 5AM, but sometimes it is late. So we added a step at the beginning of Job B to check the current time, and if it is after the safe time for Job B to complete before the start of the business day, it uses WAITFOR to pause the job and process the data in the evening instead.

    Like

  10. I have used WAITFOR to allow the RAM held by SQL Server to be released overnight, without it the upper limit is reinstated before the server has time to release it and it doesn’t drop down.

    EXEC sys.sp_configure N’show advanced options’, N’1′

    RECONFIGURE WITH OVERRIDE

    GO
    EXEC sys.sp_configure N’max server memory (MB)’, N’4000′

    RECONFIGURE WITH OVERRIDE

    GO

    WAITFOR DELAY ’00:01:00′

    EXEC sys.sp_configure N’max server memory (MB)’, N’10000′

    RECONFIGURE WITH OVERRIDE

    GO

    EXEC sys.sp_configure N’show advanced options’, N’0′

    RECONFIGURE WITH OVERRIDE

    GO

    Like

  11. I want to use WAITFOR so I can open a session (actually insert a record on a table) and then wait for 3 hours, if it has no value at the EndTime field. It updates with the current time. Guess it should work, but I don’t know if i should implement it in a trigger with the WAITFOR, then the condition, else, do nothing.
    Thanks for any hint on this.
    INSERT…
    Triggers
    WAITFOR…
    Evaluates…
    Updates or Nothing.
    :D
    Regards
    Marco

    Like

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  13. Here’s why you want to use WAITFOR. If you need to bulk update a production table that’s heavily used and is HUGE, then instead of locking the entire table for the duration of the operation and grinding the production system to a halt, you would iterate through the table row by row using a cursor. But even in a tight loop where you are updating the table one row at a time, you will cause unintended consequences. WAITFOR allows you to run a tight loop and allow other operations to run concurrently.

    Like

  14. Currently I’m using this to insert rows into a log table between intensive operations in my stored procedures to track down why sometimes they fail. However, one odd thing seems to happen, my log table history will show the EXACT precise time (down to the millisecond) for multiple rows, even tho I have a waitfordelay of 3 milliseconds before every log entry. Very odd, and only occurs in production environment, maybe it has to do with parallel processes in a farm environment. next to try it with MAXDOP 1

    Like

  15. WAITFOR can be used where we want to change value of one column periodically… like in any stock trading database if we want to fluctuate current share value then we can apply this.

    Like

  16. Is it possible to update the data after 50 hours to database, when we executing the query now. means if we execute the query now. after 50 hours it should be update/insert/delete to database.. how to set here wait for delay???

    Thanks in Advance.. waiting for reply

    Like

  17. down vote There are two things:

    1> To get todays date we can write
    SET @today_date = GETTDDT();

    2> To get Current time we can us ethe following query:

    SET @today_time = (SELECT
    digits(cast(hour(current time) as decimal(2,0)))||
    digits(cast(minute(current time) as decimal(2,0)))||
    digits(cast(second(current time) as decimal(2,0)))
    FROM sysibm/sysdummy1);

    Like

  18. I need to run an update query on thousands of rows to add a unique record id which is generated based partially on a time stamp… since the query inserts 5 or 6 values in that millisecond time i get duplicate values for my record ID… can I use this feature to wait a millisecond or two between each row update (or insert)?

    Like

  19. This can also be very useful when using the database to generate out emails to many customers. You can set a ‘pause’ between mailings so that you can adhere to Amazon SES and other SMTP provider restrictions.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s