I would assume that what I am about to ask would typically fall within the realm of "bad practice" but I want to ask regardless because this functionality is useful for diagnosing problems and bench marking long running scripts.
In short, is there a function that is built into SQL Server that I could call that would allow me to 'pause' for a short period of time?
I have a nightly SSIS process that after exporting data generates various subsets of statistical data. Whenever I have trouble with execution times of the SSIS package, I have a series of diagnostic stored procedures that I can call that help diagnose at which export/bulk insert is taking exceptionally long to execute.
During the second half of my SSIS process, where I generate statistical data, it would be really beneficial if I could analyze where the process may be bogging down or analyze how fast it can modify the data in my data tables. Most of the statical procedure begin by inserting the data into a table that is cycled through via WHILE loops and updated when necessary conditions are met.
I can easily query my data to capture a specific metric while my statistical stored procedures are executing. What I would like to do is grab that metric, wait exactly 1 second (or some specific amount of time) and grab the metric again so that I can compare them.
By being able to implement a 'pause', I can create more robust diagnostic scripts that out put how many inserts, updates or calculations are begin executed per sec and can thus estimate which portions of my bigger procedures are taking the longest.
I would assume that there may be some diagnostic tools that I could use from within SQL Server Management Studio. I admit, I haven't tinkered with any of that. However, usually if there is a tool within SSMS much of the back-end functionality can be mimicked by some form of fancy scripting. Another possible limitation to using any built-in diagnostics is that I'd prefer to run these metrics while the SSIS package is running not just while I am testing the stored procedures that execute within the package.
Thanks guys for any solutions or tips! This request isn't 100% necessary but my nightly SSIS packages are becoming massive and I'm having to start fine tuning the "squeeky wheels" that I've been able to ignore in the past.
Perhaps you are looking for WAITFOR DELAY