SQL Sentry Job Queuing
Introduction
Job queuing allows a resource intensive job to delay other jobs from executing until it completes. This allows the resource intensive job to utilize more of the server resources, allowing all jobs to finish faster and more efficiently. Unlike event chains, job queuing applies only to the jobs on an individual SQL Server instance.
Each SQL Agent Job has its own queuing settings. Choose the desired job, and then select the Settings tab from the Conditions and Settings pane. Configure default queuing settings at both the global and instance levels, and those settings are inherited by any associated jobs.
Behavior When Queuing Other Jobs
Inherit From Parent
Select False if you want to configure the queuing behavior differently than the Inherited settings.
Queue Type
Under Queue Type there are three options:
| Queue Type | Description |
|---|---|
| Never queue | This job doesn't queue any other jobs. |
| Queue for specified time | This job queues other jobs for the specified time. |
| Queue indefinitely | This job queues other jobs until this job has completed. |
Queue Others For Up To
The maximum amount of time this job queues other jobs. This setting isn't available globally, because it could be disastrous if it were accidentally enabled for all jobs on all SQL Servers.
Auto-Start Threshold
Any queued job whose next scheduled run time is beyond the specified threshold starts automatically upon leaving the queue. If a job's next scheduled run time is before the threshold it doesn't auto-start but resumes with its next scheduled run. This setting is extremely valuable for avoiding having to configure the auto-start type for every possible job that can be queued. It effectively provides a safeguard so that jobs that don't run very frequently auto-start and thus don't miss a scheduled run, and those that do run frequently resume their schedules since it usually doesn't matter if they miss a few runs. It also helps to automatically even the load when a queuing job finishes, so that all queued jobs don't auto-start at the same time and cause resource contention issues.
Behavior When This Job is Queued
Inherit From Parent
Select False to configure the queuing behavior differently than the Inherited settings.
Queue Type
Under Queue Type there are three options:
| Queue Type | Description |
|---|---|
| Never queue | This job can't be queued by another job. |
| Queue for specified time | This job only queues for the specified time. |
| Queue indefinitely | This job queues indefinitely. |
Queue For Up To
The maximum amount of time this job can be queued.
Auto-Start Type
Controls the auto-start behavior for the job when it leaves the queue.
| Auto-Start Type | Description |
|---|---|
| Don't Auto-Start, resume schedule | Skip the job until its next scheduled run. |
| Auto-start immediately | If this option is checked, the job executes as soon as it leaves the queue. However, sometimes you may not want this behavior for jobs that should only run at a specific time, or for recurring jobs (jobs that run multiple times per day) where a single missed run isn't critical. Note: If this option isn't selected and the job is always queued by another job, such as two jobs with the exact same schedule, it may never run. |
| Use default setting | The job uses the queuing job's Auto-Start Threshold setting. This is the default setting, and it's recommended for most cases. |
Queue Log
The Queue Log can be viewed by selecting Show Queue Log from the context menu of the Jobs node of a SQL Server or an individual job node. The Queue Log provides details about all recent queuing activity that has taken place on the server or for the job.
Example Scenario
The primary reason for queuing jobs is to ensure that a high priority, resource intensive job has full access to the server's resources to complete its task without competing with other jobs that can be delayed until the high priority job is done.
Consider a full backup job that runs once a week, every Sunday at 1:00 AM. If this is the only job running, having full use of the server's resources, it can complete in about 45 minutes. However, there are typically other recurring jobs on the same server that can cause contention for resources, resulting in the backup taking almost two hours to complete. This in turn causes delays in many other recurring jobs being able to successfully complete. While it's not plausible to reschedule a five minute recurring job to recur once an hour for the sake of this one weekly backup, it may be acceptable to temporarily queue that job for one hour once a week until the backup is complete.