Documentation forSQL Sentry

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.

Settings Pane opened to SQL Server Agent Job settings.

Behavior When Queuing Other Jobs

Inherit From Parent

Select False if you want to configure the queuing behavior differently than the Inherited settings.

Settings Pane opened to SQL Server Agent Job settings with Inherit From Parent highlighted and set to False.

Queue Type

Under Queue Type there are three options:

Settings Pane opened to SQL Server Agent Job with Queue Type highlighted and set to Never Queue.

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.

Settings Pane opened to SQL Server Agent Job settings with Queue Other For Up To highlighted and set to 30 minutes.

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.

Settings Pane opened to SQL Server Agent Job settings with AutoStart Threshold highlighted and set to 4 hours.

Note:  The maximum number of jobs that can be queued on a server is specified by the Maximum Queue Length setting under SQL Server Instance settings.

Behavior When This Job is Queued

Inherit From Parent

Select False to configure the queuing behavior differently than the Inherited settings.

Settings Pane opened to SQL Server Agent Job settings with Inherit From Parent highlighted and set to False.

Queue Type

Under Queue Type there are three options:

Settings Pane opened to SQL Server Agent Job settings with Queue Type highlighted and set to Queue for Specified Time.

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.

Settings Pane opened to SQL Server Agent Job settings with Queue For Up To highlighted and set to 30 minutes.

Auto-Start Type

Controls the auto-start behavior for the job when it leaves the queue.

Settings Pane opened to SQL Server Agent Job settings with Auto-Start Type highlighted and set to Use default setting.

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.
Note:  Jobs that use shared schedules on SQL Server 2005 and above will not be queued.

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.

SQL Sentry Queue Log

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.