Using WAITFOR DELAY

  • I would like to solicit expert opinion about using WAITFOR DELAY and its impact on performance, lock issues etc. If you have a better alternative solution, please feel free to share it.

    Following is an overview of the requirement and where WAITFOR DELAY is to be used.

    Currently we have a tool that migrates some data into a sql staging table from a different system.

    We have several sql tasks ( rolled into a scheduled job) that are meant to happen after the above migration.

    Currently there is no link between the two and the sql scheduled job starts at the specified time regardless of the success/failure/completion of the migration task

    All processes run during off hours

    So

    I am in the process of designing a solution to integrate the two processes.

    The migration tool would update a database with the status of its completion/failure/success.

    A SQL job scheduled to start at a certain time runs a stored procedure which does the following:

    Checks the database for completion status - by polling the database at a set interval until a certain time.

    IF it finds a successful completion, then it does further checking for each table and makes sure source and destination matches. If that match is successful, then it executes the next set of SQL tasks that are to happen after migration

    IF it does not find a successful completion within the specified time, it stops and sends a notification.

    The stored procedure code goes something like this

    SET status = 0

    WHILE curenttime < 'SpecifiedTime'

    BEGIN

    Look for migration status

    IF status is successful

    BEGIN

    Do a comparison between source and destination

    IF match is successful

    Execute Job(SQL tasks)

    SET status = 1

    ELSE

    Send notification of match failure

    SET status = 1

    END

    ELSE

    WAITFOR DELAY 'timeinterval'

    END

    If Status = 0

    BEGIN

    Send notification for migration failure

    END

    Thanks in advance for your thoughts on this.

    KR

  • While this is a feasible solution there are potential pitfalls including networking congestion, timeouts, bad timing (BOL has a great example of how a wait delay can actually run longer than expected due to networking complications), locking etc..

    To be honest the best and most robust solution would be to create an SSIS package to control the flow of the entire process. You can even save the package in SQL server and schedule it.

    The benifits of a SSIS package:

    1.This allows for error checking because you know exactly where the process failed and can even write the errors to a table and/or send a notification.

    2.You never have to worry about timing, as you can control percisely when tasks fire.

    3.You do not have to change your scripts because you can add tsql tasks to an SSIS package and still control the flow.

    SSIS is a more scaleable solution and adds more data flexiblity.

    -Adam

  • I will not be able to control the entire process through SSIS. The migration tool cannot be controlled through SSIS. So given that I have to have a way of detecting when the process is complete and wait for it to happen and then fire off the next set of events. So there is waiting requirement whether I do it through SSIS or through Stored procedures. IS there a tool in SSIS that can replace the WAITFOR function and do it more efficiently?

    Also this from BOL: You can create a deadlock by running a query with WAITFOR within a transaction that also holds locks preventing changes to the rowset that the WAITFOR statement is trying to access. SQL Server identifies these scenarios and returns an empty result set if the chance of such a deadlock exists.

    -- However it is not clear to me in what situation the deadlock would actually be created. Obviously and I have tested my code this does not necessarily happen. In my WaitFor statement it looks for certain values in certain columns of a table that is being updated by the third party tool as it completes its migration process, and those updates seem to go fine while this stored procedure is still active.

    Thanks

    KR

  • Karthika Raman (1/17/2008)


    I will not be able to control the entire process through SSIS. The migration tool cannot be controlled through SSIS. So given that I have to have a way of detecting when the process is complete and wait for it to happen and then fire off the next set of events. So there is waiting requirement whether I do it through SSIS or through Stored procedures. IS there a tool in SSIS that can replace the WAITFOR function and do it more efficiently?

    Thanks

    KR

    It looks like you can have the migration tool at least set a flag, right? If that migration tool can write to a log table or something, then you can have a schedule job check for that status, and either run (if status is ready) or halt and await the next scheduled time (if status is zero?);that would avoid the waitfor altogether.

    then you can just have the job scheduled to run every 2 minutes from midnight to 4 or whatever is the time window for execution.

    at the end, the job can set the status to status=zero, so that if it runs at 12:14, all subsequent jobs just halt due to the flag...

    is that a possibility?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The issue I have with a continuous process is that when it fails, and it will, how do you detect that? I vote for a scheduled job that checks for the completion (based on your logic) and then starts the rest of your process.

  • Both the above comments are very good points.

    The reason for the continuous process design was that after detecting the flag(s) the migration tools can set, there is another level 'check' that I run at a table level - a successful match that should trigger the next steps in a job.

    I still need help from both/either of you as to how I would do this in a scheduled job - If I am understanding you correctly - A scheduled job that runs every two minutes has a job step that runs a stored procedure that looks for the status of the flag (let us say value of the flag should be 0). The next step is set to run on the success of this step. How can I translate the value of the flag to the success/failure of a job step?

    Thanks

    KR

  • I would agree with the process running table. You should segregate the process into separate jobs like Steve and Lowell said. This way the second process will never run, unless the processed flag returns true.

    I would personally avoid using waitfor because there are too many uncertainties.

  • I would love to do it in the proposed design of doing it in separate job steps or through separate jobs. What I am having trouble is how I would cascade the steps/jobs based on the success or failure of the first step/job which is the migration. I am not sure how I would take the result of a query and translate it to job step logic. Also, I am trying to figure out how a failure notification would fit into this. I do not want a failure notification everytime the job runs looking for the flag value indicating success, but I do want to get the notification when the window for the job to have run is complete.

    I hope I am making it clear at what I am looking for.

    Any help towards this effort is greatly appreciated

    Thanks

    KR

  • Without more details, I'd build a prototype job that looked something like this:

    IF EXISTS(SELECT STATUS FROM SOMETABLE WHERE STATUS='READY')

    BEGIN

    SET XACT_ABORT ON

    BEGIN TRAN

    EXEC PR_STEP1

    EXEC PR_STEP2

    EXEC PR_STEP3

    UPDATE SOMETABLE SET STATUS='DONE'

    COMMIT TRAN

    END

    ELSE

    BEGIN

    INSERT INTO TBLOG(MSG,MSGDATE) VALUES ('Status was not ready at this time',getdate())

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • To addon to Lowell post you could also check for the existance of the flag and then start the job; otherwise, just finish up

    IF EXISTS(SELECT STATUS FROM SOMETABLE WHERE STATUS='READY')

    BEGIN

    EXEC msdb.dbo.sp_start_job N'MyJob';

    END

    ELSE

    BEGIN

    INSERT INTO TBLOG(MSG,MSGDATE) VALUES ('Status was not ready at this time',getdate())

    END

    You would have to reset the value at the end of the second job, "MyJob".

  • Thanks to all for you valuable input. I will incorporate the above code (s) in my new design and will write back when I have been able to test.

    KR

  • If the load process is serial in nature, I would take Adam's suggestion a step further and simply start the post-load job using the start-job sp call as the final step in the load process.

    Answering part of your initial post, waitfor usage can bring an entire application to a grinding halt in an instant:

    begin tran

    do something with theReallyImportantTable ...

    waitfor delay ...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • waitfor usage can bring an entire application to a grinding halt in an instant:

    Not sure what you mean... how?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If you grab some locks on a key table in the application (Invoice, primary-key-generator, etc) and then issue a waitfor delay those locks will remain held until the waitfor is complete. This could effectively shut down the app. Of course this type of waiting isn't usually EXPLICITLY done, but rather implicitly. Such as starting a tran and doing some work, then issuing a dialog box to a user or going off and trying to gather up additional information for continuing the current work thread.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Yeah... mistakes like that would certainly do it... I thought you were talking about something symptomatic with using WAITFOR even though you'd used it correctly...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply