Subsystem Load

Overview

Every job step belongs to a subsystem, and every subsystem has its own pool of worker threads with a ceiling that Agent keeps in msdb.dbo.syssubsystems.

A step that arrives when the pool is full does not fail and does not warn. It waits, and the time it spends waiting is counted in the job’s duration.

That is why a package that takes four minutes on its own takes forty when six of them start together, and why the job history shows a slow run with nothing wrong in it.

The Subsystem Load page: how close each subsystem came to its worker ceiling
The whole page over seven days. Eleven subsystems, four of them in use, and 581 step runs walked to find the overlaps.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Subsystem Load.

Shown only on an instance that can have SQL Server Agent.

How the load is worked out

The ceiling is visible and the load is only derivable.

syssubsystems holds max_worker_threads per subsystem, so the ceiling is one column. msdb records only when each step ran and for how long, so the overlap has to be worked out by walking the runs and counting how many were in flight at each moment.

This page does that, per subsystem, and shows the busiest moment against the ceiling.

T-SQL is the exception and is shown as one

A T-SQL step runs inside the engine rather than through a subsystem worker in the same way, so its ceiling is not the thing that limits it.

Pretending otherwise would put a scary bar on every instance in the world, so the T-SQL row is shown with its counts and without a ceiling comparison.

Reading the chart

The bars: peak overlap against the worker ceiling for each subsystem
Peak overlap against the ceiling for each subsystem. The steps configured and the runs in the window sit under each name, so a subsystem with steps and no runs is visible.

One bar per subsystem. The pale bar is the ceiling from syssubsystems and the dark part is the highest overlap the walk found, so the comparison is peak against ceiling rather than a count standing on its own.

The label on the right says the same thing in numbers. Seven of eighty is room to spare. A dark bar that fills its pale one is the state where steps are waiting for a worker, and that wait is counted inside the job’s duration.

Reading the grid

The grid: ceiling, steps, runs, peak overlap and the busiest moment
Most at once against Worker ceiling is the comparison, with Busiest moment beside it so the peak can be lined up against the schedule. T-SQL is shown with its counts and no ceiling comparison.
Column What it is
Subsystem T-SQL, CmdExec, PowerShell, SSIS, ActiveX, replication, analysis services.
Worker ceiling max_worker_threads from syssubsystems. Blank for T-SQL.
Steps How many job steps on this instance use this subsystem.
Runs How many times they ran in the window.
Most at once The highest overlap the walk found. This is the number to compare against the ceiling.
Busiest moment When that peak happened, so you can line it up against the schedule.
Longest run The longest single step of this subsystem in the window.
Verdict Whether this subsystem hit its ceiling, came close, or has room.

The toolbar

Control Options Default
Window 24 hours, 7 days, 30 days 7 days
Agent activity Opens Agent Activity.
What the steps run Opens Job Commands.

Where the data comes from

Source What it gives
dbo.syssubsystems The subsystem names and max_worker_threads.
dbo.sysjobsteps Which subsystem each step uses.
dbo.sysjobhistory When each step ran and for how long, which is what the overlap walk reads.

The Agent time encoding. run_date, run_time and run_duration are integers holding yyyymmdd, hhmmss and hhmmss. Converting them correctly is what makes the overlap walk possible at all; treating run_duration as seconds puts every step’s end in the wrong place.

One thing about the window

The overlap is only as good as the history behind it. Agent trims sysjobhistory to a row count, so a busy instance at the default limit may hold far less than the window you chose. The caveat line says so, and Agent Settings is where the limit is.

Messages you may see

Agent has no subsystems registered. syssubsystems is empty, which is not a state a working Agent reaches. On an Express instance there is no Agent at all and this page is not offered.

Report Why you would go there
Agent Activity Which jobs were running at the busiest moment.
Job Commands What those steps actually run.
Job Step Failures Whether any of them failed rather than just waited.
Job Schedules The schedules that put six packages on the same minute.
Agent Settings The history row limit, which bounds this page’s window.

Frequently asked questions

A package takes four minutes alone and forty at 02:00. That is this page. Look at the SSIS row: if Most at once meets the ceiling at 02:00, the extra thirty six minutes were spent queueing rather than working.

Can I raise the ceiling? max_worker_threads in syssubsystems is updatable, and raising it moves the contention somewhere else rather than removing it. Staggering the schedule is usually the cheaper answer.

Why does T-SQL have no ceiling? Because a T-SQL step runs in the engine rather than through a subsystem worker pool in the same way. Showing it with a ceiling would flag every instance for something that is not limiting it.

Most at once is lower than I know it was. The history has been trimmed. The overlap walk can only count runs it can see.