SQL Agent Hot Spot

What this check looks for

Job starts in msdb.dbo.sysjobactivity over the last seven days, grouped by start_execution_date. Where several jobs share exactly the same start time, the check reports the time and how many jobs began at it.

It reads what actually ran, not what is scheduled. That distinction matters: a schedule can look well spread and still collide, because of jobs that were added later, jobs that run on multiple schedules, and jobs triggered by something other than their own schedule.

Why it matters

Nothing here is broken, and that is why it survives. It is simply a self inflicted load spike, repeated on a timer.

Jobs collide at a handful of times because those times are what people choose. Midnight, the top of the hour, and 2am are where schedules cluster, and every job added over the years picks one of them independently. Nobody sets out to start eleven jobs at once.

The consequences are ordinary and cumulative:

• They compete for the same resources. Several backups writing to one destination, or several index rebuilds against the same storage, take far longer together than they would one after another. The total work is the same and the elapsed time is worse.
• The window overruns. Maintenance that should have finished by 4am is still running at 7, which puts index rebuilds and CHECKDB into the working day.
• They interfere with each other. An index rebuild during a backup means a much larger backup and a great deal more log. A CHECKDB during a rebuild means both are slow and tempdb takes the strain.
• Failures become correlated. A resource shortage at the spike takes out several jobs at once, so the failure list is long and the cause is not in any of them.

The fix costs nothing, which is the appeal. Moving jobs a few minutes apart uses the same window better with no new hardware and no change to what the jobs do.

How to confirm it yourself

What actually happened:

SELECT a.[start_execution_date],
       COUNT(*)                                   AS [jobs_started],
       STRING_AGG(j.[name], ', ')                 AS [job_names]
  FROM msdb.dbo.sysjobactivity AS a WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK)
         ON j.[job_id] = a.[job_id]
 WHERE a.[start_execution_date] > DATEADD(DAY, -7, GETDATE())
 GROUP BY a.[start_execution_date]
HAVING COUNT(*) > 1
 ORDER BY [jobs_started] DESC, a.[start_execution_date] DESC;

STRING_AGG needs SQL Server 2017 or later. On an older version, drop that column and list the jobs separately.

What is scheduled, which is where you will make the change:

SELECT j.[name]            AS [job_name],
       s.[name]            AS [schedule_name],
       s.[enabled],
       s.[freq_type],
       s.[active_start_time],
       s.[freq_subday_type],
       s.[freq_subday_interval]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)
         ON js.[job_id] = j.[job_id]
 INNER JOIN msdb.dbo.sysschedules AS s WITH (NOLOCK)
         ON s.[schedule_id] = js.[schedule_id]
 WHERE j.[enabled] = 1
 ORDER BY s.[active_start_time], j.[name];

And how long each one takes, which decides how far apart they need to be:

SELECT j.[name],
       AVG(h.[run_duration]) AS [avg_duration_hhmmss],
       MAX(h.[run_duration]) AS [max_duration_hhmmss],
       COUNT(*)              AS [runs]
  FROM msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK)
         ON j.[job_id] = h.[job_id]
 WHERE h.[step_id] = 0
 GROUP BY j.[name]
 ORDER BY [max_duration_hhmmss] DESC;

run_duration is an integer formatted as HHMMSS, so 130 is one minute thirty seconds.

How to fix it

Stagger them, using the durations rather than guessing.

1. Rank the jobs at the hot spot by how long they take. The long ones decide the shape of the window.
2. Order them by dependency, not by convenience. Backups first, so a backup exists before anything else touches the database. CHECKDB before index maintenance, so corruption is found before it is rewritten. Log backups continue throughout.
3. Leave real gaps. Five minutes apart is enough to break the collision for short jobs; long jobs need to be sequenced rather than spaced.
4. Make dependent jobs into steps of one job, where one genuinely has to follow another. A gap is a guess about duration; a step is a guarantee.

EXEC msdb.dbo.sp_update_schedule
     @name              = N'Nightly Maintenance',
     @active_start_time = 013000;    -- 01:30:00

Then check the window is big enough at all. If the jobs simply do not fit between the end of the evening and the start of the morning, staggering rearranges the overrun rather than removing it, and the real answer is to make the jobs cheaper. The Maintenance Window Finder report shows when the instance is actually quiet, which is often not when people assume.

How long it takes

About an hour, most of it reading durations and deciding the order.

Related reports

Related checks

Frequently asked questions

Our schedules look well spread. The check reads actual start times, not schedules. Jobs on multiple schedules, jobs started by another job, and jobs started by hand all land in the same data.

Is starting two small jobs together a problem? Barely. The finding is worth acting on when the jobs are long or heavy, and the count in the message tells you which case you have.

Can I move jobs without stopping anything? Yes. Changing a schedule takes effect at the next run and does not affect a job that is currently running.

Seven days seems short. It is long enough to include a weekly schedule and recent enough to reflect how the instance is used now. The schedule query above gives you the full picture whenever you want it.