I have a SQL query I need to run every second for about 12 hours a day.
It's a pretty simple query that logs some data to an isolated table.
Right now I've been leaving it running in my local SSMS client inside a WHILE loop and I use WAITFOR DELAY '00:00:01' to pause between each iteration for 1 second.
If I want to move this into an agent job, is it better to schedule the job to run every 1 second over those 12 hours, or keep the query as is and have the job fire once in the morning, so that the query manages how often it runs.
(One thing to keep in mind, is usually the query is fast enough such that it's runtime is sub-second, but rarely it can take a few seconds to finish executing.)
I guess the root question is, is it better to have a job that is continuously running for 12 hours, or a job that runs about 43,000 times (once per second for 12 hours)?
Best Answer
There is a certain amount of overhead each time a job starts--reads and writes on msdb to track the state and schedule calculation, create a session, etc. So from an efficiency standpoint, it would be better to run it in a loop. This will also make it run more closely to every second, because when a job starts, it takes some time for the job to actually get to the point of executing code. So if anything results in a random .25 second delay in the job actually getting to the point of executing the code, then you may have a significant gap between executions of the code. I think (someone please correct me if I'm wrong) that it has to go through the entire login process, so a delay could be caused in the authentication process due to network issues.
The downside of running it in a loop is that you'll have to handle any errors in the code. If the job runs every second, you can configure a failure notification on the job, and if there are problems you'll get an email. If it runs in a loop, you'll need handle errors and notification in the code, and also monitor the job to ensure that it is running.