Post reply

SQL Agent Schedule being missed

  • April 11, 2012 at 9:16 pm

    #253900

    Hi All,

    Just wondering if anyone else has experinced this behaviour.

    I have a SQL job to run every 10secs doing a trivial getdate(), occasionally it will skip intervals or go off track a couple of seconds but then gets back.

    I assume thats due to resource contention the server is being hammered, either it had to wait on processor or memory?

    or is this often the case with the agent being unreliable when using very small intervals?

    Thanks

  • April 12, 2012 at 2:05 pm

    #1472934

    It's the case with a lot of schedulers, not just SQL Agent.

    One word of caution, make sure you delete your job history for this particular job quite often as it can pile up in msdb in a hurry. Also, watch your ad hoc plan cache...the SQL Agent team apparently did not get the memo about the value in using stored procedures. For a job that runs that often it will bloat your plan cache in a hurry.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 12, 2012 at 5:34 pm

    #1473001

    A case of what?

    Schedulers misbehaving by nature at small intervals or always induced by high workload?

    roughly within 12hours or 5000 rows of agent run data, there is on average 50 occurrences of interval skipping upto 1 minute

    I would hope that its due to high workload and not unreliable scheduling.

  • April 12, 2012 at 5:40 pm

    #1473002

    glugingboulder (4/12/2012)

    A case of what?

    Schedulers misbehaving by nature at small intervals or always induced by high workload?

    Both. Under duress especially. Even when systems are mostly idle you'll see jobs that only run once a day start a second off sometimes. This is the case with Windows Task Scheduler and other non-Microsoft schedulers too. A lot depends on when threads can get CPU time.

    roughly within 12hours or 5000 rows of agent run data, there is on average 50 occurrences of interval skipping upto 1 minute

    I would hope that its due to high workload and not unreliable scheduling.

    You may have a bottleneck on your system, my guess is CPU.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 17, 2012 at 1:47 am

    #1474555

    Also keep track of the time the job is taking to complete execution.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply