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 / Project | The package. |
| Runs | Executions in the window. |
| Quietest | Median duration in its least busy bucket. |
| Busiest | Median duration in its busiest bucket. |
| Factor | Busiest divided by quietest. This is the number to sort by. |
| 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. |
| What it means | Contending, spilling, or simply doing more work when it is busy. |
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.