Is there a way to pause or wait a few seconds from a script?

Question

I would suggest that what I am going to ask tends to fall into the realm of "bad practice", but I want to ask because this functionality is useful for diagnosing problems and writing long scripts.

In short, is there a feature built into SQL Server that I could name that allows me to “pause” for a short period of time?

I have an overnight SSIS process that, after exporting data, generates various subsets of statistics. Whenever I have problems with the SSIS package runtime, I have a series of diagnostic stored procedures that I can call to help diagnose when exporting / bulk inserting takes an extremely long time to complete.

In the second half of my SSIS process, where I generate statistics, it would be very helpful if I could analyze where the process can clog or analyze how quickly it can change the data in my data tables. Most of the static procedure begins by inserting data into the table, which cycles through the WHILE loops and is updated when the necessary conditions are met.

I can easily request my data to capture a specific metric while my statistical stored procedures are running. What I would like to do is grab this metric, wait exactly 1 second (or some specific amount of time) and grab the metric again so that I can compare them.

Being able to realize the “pause”, I can create more reliable diagnostic scripts from which you can determine how many inserts, updates or calculations are run per second, and thus, it is possible to evaluate which parts of my larger procedures take the longest.

I would suggest that there may be some diagnostic tools that I could use from SQL Server Management Studio. I admit that I did not redo it with anyone. However, as a rule, if there is a tool in SSMS, many of the functionality can be mimicked by some form of fancy scripts. Another possible limitation of the use of any built-in diagnostics is that I would prefer to perform these indicators while the SSIS package is working not only during testing of stored procedures performed inside the package.

Thanks guys for any solutions or tips! This request is not 100% necessary, but my nightly SSIS packages are becoming massive, and I need to start fine tuning the "squeeky wheels" that I could ignore in the past.

Answer 1

You may be looking for WAITFOR DELAY

Answer 2

You must use the WAITFOR DELAY statement

Answer 3

Choose Getdate () - wait 10 seconds to run another command

TASK WAITFOR '00: 00: 10 '; - valid hh: mm: ss

Choose GetDate ()