Using WAITFOR DELAY

Question

Using WAITFOR DELAY

Karthika Raman

SSCrazy

Points: 2520
More actions
January 16, 2008 at 12:33 pm

#185740

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

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

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

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

My blog: http://jahaines.blogspot.com

Karthika Raman SSCrazy Points: 2520 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

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.

Karthika Raman SSCrazy Points: 2520 More actions · Answer 5

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

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.

My blog: http://jahaines.blogspot.com

Karthika Raman SSCrazy Points: 2520 More actions · Answer 7

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

Lowell SSC Guru Points: 323518 More actions · Answer 8

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!

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

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".

My blog: http://jahaines.blogspot.com

Karthika Raman SSCrazy Points: 2520 More actions · Answer 10

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

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)