SSIS Self Contention
Overview
Is this package slow because it is slow, or because of what runs beside it?
Every run in catalog.executions has a start and an end, so for any run it is arithmetic to say how many others were live when it started. Bucket a package’s runs by that number, compare the median duration in each bucket, and a package that suffers from company shows as a row that fans out to the right.
“LoadOrders takes ninety five seconds alone and six minutes when five others are running” is an argument for moving two jobs by ten minutes, and it costs nothing to act on. Without the numbers it is an opinion, which is why nobody ever does it.
Where to find it
Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Self Contention.
Two honesties the page carries in its caveat line
This is correlation
The busiest hour is also the hour with the most data, so a package that is slower at two in the morning may simply have more to do.
Where the catalog logged at Verbose, the rows moved are shown beside the duration and the ambiguity disappears. Where it did not, the page says the ambiguity is there.
One bucket is not a comparison
A package that has only ever run alone has one bucket, and the page says so rather than drawing a flat line that looks like an answer.
The memory the catalog already captured
available_physical_memory_kb is recorded at the start of every run.
So a package that slows down under concurrency and started with a gigabyte free is not contending for CPU, it is spilling buffers, and that is a different fix.
Reading the grid
| Column | What it is |
|---|---|
| Package | The package. |
| Runs | Executions in the window. |
| Quietest | Median duration in its least busy bucket. |
| Quietest band | Which bucket that is: alone, 1 to 2 others, 3 to 5 others, or 6 or more. |
| Busiest | Median duration in its busiest bucket. |
| Busiest band | Which bucket that is. |
| Factor | Busiest divided by quietest. This is the number to sort by. |
| What it means | Contending, spilling, or simply doing more work when it is busy. |
| Rows | The rows moved in each bucket, where Verbose logging recorded them. This is what removes the ambiguity. |
| Least free memory | The lowest available_physical_memory_kb at the start of any run. |
| Project | The folder and project the package lives in. |
Layout. The numeric columns are narrow and What it means comes straight after Factor, so the verdict is on screen at 1280 x 900 without scrolling sideways. Every text column is capped, Project takes all the width that is left (never less than 170 px), and a value that is still cut off shows in full in the tooltip when the mouse rests on its row.
Runs, Quietest, Busiest, Factor and Rows sort by value, so the slowest package sorts to the top whichever unit its time is shown in. The band sits in a column of its own beside each duration, so the duration column holds only the time.
- Click a bar to select that package’s row in the grid.
- Double click a bar to select that row and open SSIS Schedule, which shows what runs at the same time.
- Right click a bar for the same menu as that package’s grid row: What runs at the same time, Its whole duration spread, What its data flow moved (when it slows down or moved more rows) and Copy package path.
- Right click an empty part of the chart to copy the chart to the clipboard.
The toolbar
| Control | Options | Default |
|---|---|---|
| Window | 7 days, 30 days, 90 days | 30 days |
| When it all runs | Opens SSIS Schedule. | |
| Critical path | Opens SSIS Critical Path. |
Where the data comes from
| Source | What it gives |
|---|---|
catalog.executions |
Start, end, status, machine_name, available_physical_memory_kb per run. The overlap is computed from the starts and ends. |
catalog.execution_data_statistics |
The rows per bucket, at Verbose only. |
Related reports
| Report | Why you would go there |
|---|---|
| SSIS Schedule | Where the concurrency comes from, and what to move. |
| SSIS Package Speed | The spread this page is splitting by concurrency. |
| SSIS Critical Path | Whether moving this package would change when the night ends. |
| SSIS Data Flow | The rows behind the ambiguity. |
| SSIS Packages Run | The timeline the buckets were derived from. |
Frequently asked questions
A factor of four. Do I move the schedule? Look at the Rows column first. If the busy bucket also moved four times the rows, the package is doing more work rather than suffering. If the rows are flat and the duration is not, moving the schedule is the cheap fix.
The rows column is empty. Verbose logging was off for those runs. The page says the ambiguity is there rather than pretending the correlation is causation.
Least free memory is very low. Then the package is probably spilling buffers to disk rather than competing for CPU. Adding memory to the SSIS host, or reducing how many packages start at once, are the two levers.
A package shows one bucket. It has only ever run alone, so there is nothing to compare. That is stated rather than drawn.