A nightly package that used to take 40 minutes now takes three hours. Nobody changed it. The usual cause is data volume, a changed source query, or a lookup table that outgrew its cache. Tuning guesses make it worse. Measure first.
This routine applies whether the package runs on-premises or on the Azure-SSIS integration runtime. We use it during SSIS Run and Maintain work and before any decision to rewrite.
Step 1: Find the slow component, not the slow package
Run the package from the SSISDB catalog at the Performance logging level, or Verbose for a single diagnostic run. Verbose adds overhead, so do not leave it on.
Then query catalog.execution_component_phases. It reports active time per component and phase for each execution. Sort by total active time and look at the top three rows.
SELECT TOP (10)
package_name, task_name, subcomponent_name, phase,
SUM(DATEDIFF(ms, start_time, end_time)) AS active_ms
FROM SSISDB.catalog.execution_component_phases
WHERE execution_id = @execution_id
GROUP BY package_name, task_name, subcomponent_name, phase
ORDER BY active_ms DESC;
If the top row is a source component, the problem is probably the source query, not SSIS. Run that query in Management Studio first. If the top row is a destination, look at commit size and indexes on the target. If it is a transformation, continue below.
Step 2: Remove blocking and semi-blocking transformations
Synchronous transformations such as Derived Column and Conditional Split reuse the input buffer. Asynchronous ones copy data into new buffers.
- Sort and Aggregate are fully blocking. They hold every row before emitting any. On large inputs they consume memory and spill to disk.
- Merge Join requires sorted input. Merge and Union All are semi-blocking.
Where the source is SQL Server, push the work back: add an ORDER BY to the source query, then set IsSorted = True and SortKeyPosition on the output columns in the Advanced Editor. SSIS then skips the Sort component. Declaring the flag when the data is not sorted gives wrong joins and no error, so verify the query.
Often an aggregate belongs in the source query anyway.
Step 3: Check buffer sizing
Two data flow task properties matter: DefaultBufferMaxRows (default 10,000) and DefaultBufferSize (default 10 MB, maximum 100 MB). SSIS sizes buffers from the estimated row width. Wide rows mean few rows per buffer.
Two checks:
- Trim columns. Select only the columns the flow uses. A
SELECT *from a table with avarchar(8000)column inflates every buffer. - Turn on
BufferSizeTuninglogging for one run. It tells you when SSIS reduced rows per buffer because the buffer size limit was hit. If it did, raiseDefaultBufferSizeandDefaultBufferMaxRowstogether, and re-measure. Stop when throughput stops improving.
Large buffers that exceed available memory cause paging, which is slower than the default. Change one setting per run.
Step 4: Fix Lookup cache modes
The Lookup transformation has three cache modes.
- Full cache loads the reference table before the data flow starts. Fastest per row, but the pre-load is paid every run and uses memory. Use a query that returns only the needed columns.
- Partial cache queries the database on a miss and caches the result. It suits large reference tables where only a small share of keys is hit.
- No cache queries the database for every row. It is rarely right.
A common regression: a dimension table grew from 50,000 to 20 million rows, and a full-cache lookup now spends most of the run loading it. Either filter the reference query to the keys in use, or switch to partial cache with an index on the join column.
Also check the no-match output. Rows sent there and then inserted by a second path often indicate a slowly changing dimension handled row by row.
Step 5: Set destination commit sizes
For OLE DB Destination with fast load, Maximum insert commit size defaults to 2147483647, which means one commit for the whole load. That is fine for small loads. For tens of millions of rows it grows the transaction log and makes a failure roll back everything. A value in the range of 100,000 to 1,000,000 is a reasonable first test, but the right number depends on your log configuration. Measure it.
Also check for a clustered index on the target and for triggers or non-clustered indexes that slow the insert. Disabling and rebuilding indexes around a very large load is sometimes worth it. For a small load it is not.
Step 6: Replace the row-by-row patterns
The OLE DB Command transformation runs its statement once per row. For updates, load changed rows into a staging table and run one set-based UPDATE or MERGE in an Execute SQL Task. This is the largest single gain in most estates we open.
Script Components that call a web service or a database per row are the same pattern in different clothes.
When to change nothing
If the package finishes inside its window with headroom and the growth rate is known, leave it. Tuning a job that is not a problem adds risk and a diff someone has to review. Tuning and a rewrite are also separate questions: a package that is slow for a bad lookup is a one-hour fix, not a rewrite candidate. The retire, lift-and-shift, or rewrite checklist covers the decision.
On the Azure-SSIS integration runtime, node size and parallel execution count also affect run time, and they affect cost. See controlling Azure-SSIS integration runtime cost before you scale up.
Record the result
For each tuned package, write down the execution ID before, the execution ID after, the changed property and the measured difference. This is part of the written handover, and it lets the next engineer tell whether a later slowdown is new.
If you have a package estate where nobody knows which runs are slow or why, send us the number of packages, your SQL Server version and what is prompting the question. A senior engineer replies within a day, and an estate assessment is usually where we begin.