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