1

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)?

5

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.

| improve this answer | |
  • Sounds good. Is there a way to schedule a job to stop itself?...if not I can just have my loop conditions check the current datetime I guess. – J.D. Feb 28 at 14:25
  • 1
    You can have some code check the time and if it's time to stop just throw an error. Or, you could schedule another job to run at the stop time and use sp_stop_job to stop it. docs.microsoft.com/en-us/sql/ssms/agent/… – Tony Hinkle Feb 28 at 14:28
  • 2
    I would go for one job with a loop option, and just do BREAK in the loop conditionally. However, if something goes wrong, then you need to determine for how long you can love without this job running. The simplest way, I guess, is to have the job execute, say, every minute. If the job is already running, then Agent won't start a second instance. – Tibor Karaszi Feb 28 at 15:24
  • @TiborKaraszi Worthy of being an actual Answer. – Michael Green Feb 28 at 18:57
1

I would go for one job with a loop, and just do BREAK in the loop based on whatever condition suits you.

However, if something goes wrong and the jobs terminates, then you need to determine for how long you can live without this job running. The simplest way IMO is to schedule the job to be executed, say, every minute. If the job is already running, then Agent won't start a second instance.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.