Using WAITFOR DELAY

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Using WAITFOR DELAY

15 posts, Page 1 of 2

1 2 »»

Using WAITFOR DELAY

Rate Topic

Display Mode

Topic Options

Author

Message

Karthika Raman

Posted Wednesday, January 16, 2008 12:33 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:39 PM
Points: 216, Visits: 166

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

Post #443932

Adam Haines

Posted Wednesday, January 16, 2008 9:28 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:18 PM
Points: 2,278, Visits: 2,999

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

Post #444117

Karthika Raman

Posted Thursday, January 17, 2008 8:55 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:39 PM
Points: 216, Visits: 166

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

Post #444368

Lowell

Posted Thursday, January 17, 2008 9:09 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 11,648, Visits: 27,762

Karthika Raman (1/17/2008)

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #444377

Steve Jones - SSC Editor

Posted Thursday, January 17, 2008 9:24 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #444395

Karthika Raman

Posted Thursday, January 17, 2008 9:41 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:39 PM
Points: 216, Visits: 166

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

Post #444413

Adam Haines

Posted Thursday, January 17, 2008 9:43 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:18 PM
Points: 2,278, Visits: 2,999

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

Post #444415

Karthika Raman

Posted Thursday, January 17, 2008 10:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:39 PM
Points: 216, Visits: 166

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

Post #444431

Lowell

Posted Thursday, January 17, 2008 10:17 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 11,648, Visits: 27,762

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #444445

Adam Haines

Posted Thursday, January 17, 2008 10:34 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:18 PM
Points: 2,278, Visits: 2,999

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

Post #444461

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions