Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using WAITFOR DELAY Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2008 12:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 1:32 PM
Points: 216, Visits: 169
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
Posted Wednesday, January 16, 2008 9:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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
Posted Thursday, January 17, 2008 8:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 1:32 PM
Points: 216, Visits: 169
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
Posted Thursday, January 17, 2008 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
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

--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
Posted Thursday, January 17, 2008 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:25 AM
Points: 33,267, Visits: 15,433
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
Posted Thursday, January 17, 2008 9:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 1:32 PM
Points: 216, Visits: 169
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
Posted Thursday, January 17, 2008 9:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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
Posted Thursday, January 17, 2008 10:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 1:32 PM
Points: 216, Visits: 169
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
Posted Thursday, January 17, 2008 10:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
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
Posted Thursday, January 17, 2008 10:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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 »

Add to briefcase 12»»

Permissions Expand / Collapse