SSIS Package Speed

Overview

Is this run slow, or is this package always like that?

A package that took eleven minutes last night is fine if it always takes eleven minutes and is an incident if it usually takes ninety seconds. The built in reports show the number without the context that decides which.

Where to find it

Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Package Speed.

Why the chart is a spread

Each package gets:

  • the band from its median to its ninety fifth percentile
  • a whisker out to its worst run
  • a mark where its most recent run landed

A wide band says the package’s duration is not a property of the package, and the number beside it should not be read as one.

A mark past the end of the band is last night’s run being genuinely unusual, which is the only thing on this page worth being woken up for.

Two decisions about the arithmetic

Percentiles are over succeeded runs only

Failures are counted separately. A package that fails after nine seconds drags an average down and makes a degrading package look like an improving one.

Canceled runs are excluded for the mirror image of the same reason: they are usually short because somebody killed them for running long.

Rows are the widest single path, not the sum

Where the catalog logged at Verbose, the rows that moved are shown beside the duration. A package that loads yesterday’s deltas takes as long as yesterday was busy, and twice as long, four times the rows is a healthy package while twice as long on its own sends somebody hunting.

Rows are taken as the widest single data flow path rather than as the sum of all of them, because a source feeding a transform feeding a destination writes the same rows three times and adding them up triples every package that has a transform in it.

Reading the grid

Column What it is
Package / Project The package.
Runs Executions in the window.
Failed How many of those failed, counted apart from the percentiles.
Typical The median duration of the succeeded runs.
95th The ninety fifth percentile. The top of the band.
Worst The slowest succeeded run in the window.
Latest The most recent run’s duration. Compare this against 95th.
Trend Whether the recent runs sit above or below the older ones.
Rows The widest data flow path’s row count, where Verbose logging recorded it.
Last run When the latest run was.

The toolbar

Control Options Default
Window 24 hours, 7 days, 30 days 7 days
Recent runs Opens SSIS Packages Run.
Data flow Opens SSIS Data Flow.

Where the data comes from

Source What it gives
catalog.executions Start, end and status per run.
catalog.execution_data_statistics Rows per data flow path, written at Verbose only.
catalog.catalog_properties The retention window, which bounds every window on the toolbar.
Report Why you would go there
SSIS Inside the Package Which task inside a slow package burned the time.
SSIS Data Flow How many rows actually moved, path by path.
SSIS Self Contention Whether the package is slow because of what runs beside it.
SSIS Critical Path Whether making it faster would move the end of the night.
SSIS Packages Run The timeline these durations came from.

Frequently asked questions

The band is wide on every package. Then duration is not a stable property of this workload, which is itself the finding. Look at SSIS Self Contention and at whether the row counts move with the durations.

The rows column is empty. The catalog was not logging at Verbose for those runs. execution_data_statistics is written at Verbose only, and nothing else records row counts.

The latest run is slower than the worst run in the band. That is a genuine outlier and the single most actionable thing on this page.

Why exclude canceled runs? Because they are short for the wrong reason. A run somebody killed at three minutes because it was misbehaving would pull the median down and hide the problem that caused the kill.