SSIS Large-Volume ETL and Performance Engineering
Why this topic matters
Section titled “Why this topic matters”SSIS performance work goes wrong in a predictable way: someone reads that DefaultBufferMaxRows can be increased, changes it, sees no improvement, changes it again, and concludes SSIS is slow. Meanwhile the actual problem is a Sort transformation, a SELECT *, or a destination that never had fast load enabled.
Tuning is an ordered discipline. Measure, then remove structural problems, then adjust settings — in that order. Buffer settings are the last thing to touch, not the first, and they are the only part of this article most people have read.
Fundamentals: what actually costs time
Section titled “Fundamentals: what actually costs time”Four questions decide throughput, in roughly this order of impact:
| Question | Governs |
|---|---|
| How much data is moving? | Everything. Filtering at the source is the cheapest optimisation available |
| How wide is each row? | Buffer capacity, memory use, copy cost. Driven by declared types, not content |
| How many times is it copied? | Asynchronous components allocate new buffers; blocking ones hold the whole set |
| Can the destination absorb it? | Fast load, commit size, indexes, constraints, triggers, lock contention |
Everything else — thread counts, buffer sizes, engine settings — is a second-order effect on top of those four.
The mental model: find the constraint, then relieve it
Section titled “The mental model: find the constraint, then relieve it”A data flow is a chain, and its rate is set by its slowest link. There are only four candidates:
SOURCE PIPELINE DESTINATION MACHINE ────── ──────── ─────────── ─────── Slow query Blocking transform No fast load RAM (spooling) Missing index Excess conversions Small commit size CPU (transforms) Wide rows Full-cache lookup Too many indexes Disk (source/dest) Network Row-by-row command Lock contention Contention with Locking LOB columns Triggers the DB engineGuessing which one is binding is unreliable. Isolate instead — the procedure is below, and it takes minutes.
Measure before you tune
Section titled “Measure before you tune”Isolate the bottleneck
Section titled “Isolate the bottleneck”1. Isolate the destination. Replace the destination with a Row Count transformation and re-run.
- Suddenly fast → the bottleneck is the write path.
- Still slow → the bottleneck is upstream.
2. Isolate the source. Run the source query alone in SSMS with Discard results after execution enabled.
- Slow there → it is a database problem: plan, indexes, locking, or volume.
- Fast there → the problem is in the pipeline or the destination.
3. Bisect the pipeline. Remove transformations one at a time between source and Row Count until the time changes materially. That component is your cost.
4. Watch the machine. Sustained high CPU points at transformations or conversions; high disk wait at source or destination; steadily growing memory at a blocking component; disk activity in BufferTempStoragePath means spooling.
Use the catalog’s own numbers
Section titled “Use the catalog’s own numbers”Run one execution with Performance or Verbose logging, then query the catalog. This is far more reliable than intuition.
-- Time spent per component phase — the single most useful tuning querySELECT subcomponent_name, phase, SUM(DATEDIFF(MILLISECOND, start_time, end_time)) AS elapsed_msFROM catalog.execution_component_phasesWHERE execution_id = @execution_idGROUP BY subcomponent_name, phaseORDER BY elapsed_ms DESC;-- Rows moved between components (requires Verbose)SELECT source_component_name, destination_component_name, SUM(rows_sent) AS rows_sentFROM catalog.execution_data_statisticsWHERE execution_id = @execution_idGROUP BY source_component_name, destination_component_nameORDER BY rows_sent DESC;-- Which control-flow tasks dominate the package?SELECT e.executable_name, es.execution_result, DATEDIFF(SECOND, es.start_time, es.end_time) AS secondsFROM catalog.executable_statistics AS esJOIN catalog.executables AS e ON e.executable_id = es.executable_idWHERE es.execution_id = @execution_idORDER BY seconds DESC;Reading the phase output: a large PreExecute time on a Lookup means cache warm-up; large ProcessInput on a destination means the write path; large PrimeOutput on a source means extraction.
Interpret with care — Verbose logging itself adds overhead, so use these to find the relative hot spot, not to quote absolute timings.
Minimal working example: before and after
Section titled “Minimal working example: before and after”A deliberately poor load, and the same job restructured. Both do the same work.
BEFORE (a common shape, and slow)
OLE DB Source SELECT * FROM dbo.SalesLine -- 60 columns, all rows ▼ Sort (by CustomerId) -- FULLY BLOCKING ▼ Merge Join with OLE DB Source (SELECT * FROM dbo.Customer, also Sorted) ▼ Aggregate (SUM by Region) -- FULLY BLOCKING ▼ OLE DB Command "UPDATE dbo.RegionSummary SET … WHERE Region = ?" -- ONE ROUND TRIP PER ROWFour structural problems: everything read, two blocking transformations holding the full set in memory, a join between two tables that live in the same database, and a row-by-row update.
AFTER
OLE DB Source SELECT c.Region, SUM(s.NetAmount) AS RegionTotal, COUNT(*) AS SaleCount FROM dbo.SalesLine AS s JOIN dbo.Customer AS c ON c.CustomerId = s.CustomerId WHERE s.SaleDate >= ? GROUP BY c.Region; -- join + aggregate in SQL ▼ OLE DB Destination (fast load) → stg.RegionSummary
Control flow, next task: [Execute SQL Task] one set-based MERGE from stg.RegionSummary into dbo.RegionSummaryNothing blocks, the row count reaching the pipeline is tiny, and the update is one statement. No buffer setting was changed.
Practical implementation
Section titled “Practical implementation”Large flat-file ingestion
Section titled “Large flat-file ingestion”Flat files are where SSIS is genuinely excellent, if configured properly.
- Declare narrow, correct column types. The default is
DT_STR(50)for everything. A 30-column file at defaults carries far more bytes per row than it needs, and buffer capacity is computed from declared widths. - Uncheck columns you do not need in the source — they then occupy no buffer space at all.
- Use
FastParseon integer, date, and time columns when the file uses invariant formats. It bypasses locale-aware parsing. - Prefer fixed-width or ragged-right layouts when you control the format; parsing is cheaper than delimiter scanning with quote handling.
- Split very large files and process them with parallel Data Flow Tasks — but only if the destination can absorb concurrent writes.
- Consider the Bulk Insert Task when the file needs no transformation. It is the thinnest path from file to table, at the cost of per-row error handling.
Staging tables
Section titled “Staging tables”Staging is the single most effective structural decision in large-volume SSIS work.
Why it wins:
- The pipeline does only what a pipeline is good at — reading and streaming.
- Joins, aggregation, deduplication, and merging happen in SQL, where the optimiser and indexes exist.
- The load into staging can use bulk semantics with minimal logging and no constraint checking.
- Re-running is trivially safe: truncate and load again.
- Failures localise — a staging failure is a load problem; an apply failure is a logic problem.
Design guidance:
- Heap, no indexes, during load. A heap accepts rows fastest. Add indexes afterwards if the apply needs them.
- Match source types exactly to avoid pipeline conversions.
- Include a batch id and load timestamp on every row so a partial run is identifiable and removable.
TRUNCATE TABLE, notDELETE— minimally logged and instant.- Keep staging in its own schema or database, with a recovery model appropriate to throwaway data.
Fast load and commit sizing
Section titled “Fast load and commit sizing”Use Table or view — fast load. The plain mode issues one INSERT per row.
| Setting | Guidance |
|---|---|
Table lock (TABLOCK) |
On for a dedicated load into an exclusive table; off when other writers need it or several flows load it concurrently |
| Check constraints | Leave on unless the data is validated elsewhere |
| Rows per batch | Row-count hint to the optimiser |
| Maximum insert commit size | Rows committed per transaction — the setting with the widest blast radius |
FIRE_TRIGGERS |
Triggers do not fire under fast load unless you add this to FastLoadOptions |
ORDER |
Declares the input’s sort order; can help when it matches the clustered index |
On commit size: too large means one enormous transaction — the log cannot truncate, memory pressure rises, and a failure rolls back everything. Too small means constant commit overhead. Tens of thousands to low hundreds of thousands of rows is a reasonable starting range; then measure.
Two consequences people miss:
- A constraint violation fails the entire batch, not the offending row. Batch sizing is an error-handling decision too.
- The default differs by SSIS version and access mode — Microsoft’s documentation shows both
0and2147483647on different pages. Read the actual value in your destination.
Minimal logging
Section titled “Minimal logging”Bulk loads can be minimally logged, which dramatically reduces log writes. The conditions are set on the SQL Server side, not in SSIS: the database must be in the bulk-logged or simple recovery model, the load must use a bulk path, and table-locking and index-structure requirements must be met.
When a load is unexpectedly slow and the log file is growing fast, minimal logging is usually not in effect. Check the recovery model first.
Indexes and constraints on the destination
Section titled “Indexes and constraints on the destination”Every nonclustered index is maintained per inserted row. For a large load into a table you control:
- Load into a heap staging table.
- Apply to the target, or switch the staged data in.
- Build or rebuild indexes afterwards.
Dropping and recreating indexes on a live target is not a free win — the rebuild costs time and the table is degraded for readers meanwhile. Measure both approaches at your volume.
See SQL Server Indexes for the indexing fundamentals underneath this.
Reducing what travels
Section titled “Reducing what travels”- Select only needed columns, in the query or the component’s column list.
- Filter at the source. An indexed
WHEREbeats reading everything and discarding in a Conditional Split. - Convert types at the source. A Data Conversion component adds a second copy of each converted column to the buffer.
- Beware LOB columns.
VARCHAR(MAX),NVARCHAR(MAX),VARBINARY(MAX), and XML are handled outside the normal buffer and spill to disk. Isolate them in a separate data flow if they are unavoidable. - Declare realistic string lengths.
VARCHAR(4000)holding 20 characters still reserves 4000 bytes per row.
Source query optimisation
Section titled “Source query optimisation”The source query is production code and deserves the same attention as any other:
- Use SQL command, not Table or view.
- Make predicates SARGable — compare a column to a value; do not wrap the column in a function.
- Check the plan in SSMS. A missing index on the watermark column is a very common cause of slow extracts.
WITH (NOLOCK)/READ UNCOMMITTEDis a correctness trade-off, not a free speed-up. Use only where dirty reads are genuinely acceptable.- Push joins and aggregation into the query when both sides share a database.
- For large extracts against a busy OLTP source, consider reading from a replica or snapshot rather than optimising the extract further.
Lookup cache strategy
Section titled “Lookup cache strategy”| Mode | Cost profile | Choose when |
|---|---|---|
| Full Cache | Loads the whole reference set during pre-execute, matches in memory | The set fits comfortably in memory — the usual case |
| Partial Cache | Queries on demand, caches up to a limit | Large reference set, pipeline touches a small repeated subset |
| No Cache | One query per row | Reference data changes mid-load, or row counts are tiny |
- Always restrict the reference query to the key and the columns you need.
- Full Cache pre-execute time is real — against a very large dimension it can dominate the run, before any row moves.
- Cache Connection Manager lets several lookups (or packages) share one populated cache instead of reloading it.
- A no-match output is not an error — redirect and treat unmatched rows as inserts.
- When the reference set is huge and both sides share a database, do the join in SQL and drop the Lookup.
Blocking transformations
Section titled “Blocking transformations”| Component | Behaviour | Replace with |
|---|---|---|
| Sort | Fully blocking; buffers everything | ORDER BY + IsSorted/SortKeyPosition on the source output |
| Aggregate | Fully blocking | GROUP BY in SQL, on staged data if necessary |
| Fuzzy Lookup / Grouping | Fully blocking and expensive | Deduplication in SQL where the rules allow |
| Merge Join | Partially blocking; needs sorted inputs | A JOIN in SQL when both sides share a database |
| Pivot / Unpivot | Partially blocking | T-SQL PIVOT/UNPIVOT |
The presort pattern, which removes the most common blocking component:
- Add
ORDER BY JoinKeyto the source query. - Open the source component’s Advanced Editor.
- Set the output’s
IsSortedtoTrue. - Set
SortKeyPosition=1(then2,3… for compound keys) on the sort columns.
The engine now trusts the data is sorted. If it is not, results are silently wrong — only claim it when the query really orders the rows.
A realistic end-to-end example
Section titled “A realistic end-to-end example”A fictional nightly retail load: roughly 40 million sales lines arriving as regional CSV files, into a partitioned fact table. This is the shape that holds up at volume.
STAGE 1 — Ingest (parallel, one Data Flow Task per region file)
Flat File Source • 12 columns declared with real widths; 4 unneeded columns unchecked • FastParse on Quantity, SaleDate • error output: Redirect Row → reject table ▼ Derived Column (replace-in-place where possible; 3 added columns only) ▼ Lookup "Store" Full Cache, SELECT StoreCode, StoreKey FROM dbo.DimStore (~5,000 rows — trivially cacheable) ▼ Lookup "Product" Partial Cache, SELECT ProductCode, ProductKey FROM dbo.DimProduct (~4 million rows; each file touches a few thousand) ▼ OLE DB Destination (fast load) → stg.SalesLine_<Region> -- separate heap per region Table lock: ON (each flow owns its own table — no contention) Check constraints: OFF (data already validated by the Lookups above) Maximum insert commit size: 100000
No Sort. No Aggregate. No OLE DB Command. Nothing blocks.
STAGE 2 — Consolidate and index (SQL, one Execute SQL Task)
INSERT INTO stg.SalesLine WITH (TABLOCK) SELECT … FROM stg.SalesLine_North UNION ALL SELECT … FROM stg.SalesLine_South …;
CREATE CLUSTERED INDEX CIX_stgSalesLine ON stg.SalesLine (SaleDate, StoreKey);
STAGE 3 — Apply by partition switch (SQL)
For each affected SaleDate partition: • build stg.SalesLine_Switch with identical schema, filegroup, and indexes • add a CHECK constraint proving the date range • ALTER TABLE stg.SalesLine_Switch SWITCH TO dbo.FactSales PARTITION n;
The visible change to consumers is a metadata operation.Design decisions worth naming:
- Separate staging table per region removes destination lock contention, which is what makes the parallel ingest actually parallel.
- Two different Lookup cache modes, chosen from reference-set size and access pattern rather than by habit.
Check constraints: OFFis justified here because the Lookups already guarantee valid keys — and that justification is the point. Turning it off without one is how bad data arrives.- Indexing happens once, after consolidation, not 40 million times during the load.
- The expensive work happens away from the live table, and the swap is metadata.
Internal behavior: buffers, execution trees, and memory
Section titled “Internal behavior: buffers, execution trees, and memory”Execution trees
Section titled “Execution trees”The engine divides a data flow into execution trees. A new tree begins at each source and at each asynchronous component’s output, because that is where new buffers are allocated.
Source ─> Derived Column ─> Conditional Split ─> Lookup ─> Destination└──────────────── ONE execution tree, one buffer set ───────────────┘
Source ─> Derived Column ─> Sort ─> Destination└──── tree 1 ────────────┘ └──── tree 2, NEW buffers ────┘More asynchronous components means more memory and more thread coordination. This is the concrete reason “just add a Sort” is expensive.
Buffer sizing
Section titled “Buffer sizing”| Property | Meaning | Default |
|---|---|---|
DefaultBufferMaxRows |
Maximum rows per buffer | 10,000 |
DefaultBufferSize |
Target buffer size in bytes | 10 MB |
AutoAdjustBufferSize |
Let the engine size buffers from row width | False on older versions |
The engine estimates row width from declared types and allocates whichever limit binds first. Wide rows hit the byte limit long before the row limit — which is exactly why narrowing rows helps more than raising the limits.
When AutoAdjustBufferSize = True, the engine sizes buffers from the row width and DefaultBufferMaxRows, removing most of the guesswork. Prefer it over manual tuning where available.
If you do tune manually:
- Raise both settings together, and only with measurements on both sides.
- Larger buffers mean fewer, bigger allocations — better throughput, worse memory pressure, and higher latency to first row.
- Watch for spooling. When SSIS runs short of memory it writes buffers to
BufferTempStoragePathand throughput collapses. Sustained spooling means excessive memory demand (usually a blocking transformation) or a machine short of RAM.
EngineThreads hints at how many threads the pipeline may use across execution trees. Revisit it only for data flows with many parallel paths; it will not rescue a badly shaped pipeline.
Where the memory actually goes
Section titled “Where the memory actually goes”SSIS and the Database Engine compete for RAM on a shared machine. SQL Server’s buffer pool will happily consume everything max server memory allows, leaving SSIS to spool.
Options, in order of preference:
- Run SSIS on a separate machine from the target instance.
- Cap SQL Server’s
max server memoryto leave headroom for the SSIS process. - Reduce SSIS demand: narrower rows, no blocking components, restricted lookup caches.
Parallelism
Section titled “Parallelism”Three levers, at different levels:
| Lever | Level | Effect |
|---|---|---|
MaxConcurrentExecutables |
Package | Concurrent control flow executables. Default -1 = logical processors + 2 |
EngineThreads |
Data Flow Task | Threads across execution trees. A hint |
| Package/job design | Architecture | Several packages run by the scheduler concurrently |
Parallelism is a design decision, and it can easily make things slower:
| Risk | What happens | Mitigation |
|---|---|---|
| Same destination table | Lock contention, deadlocks, especially with TABLOCK |
Separate staging tables; consolidate afterwards |
| Same busy source | Saturating the source slows everything | Stagger, or read from a replica |
| Memory | Several data flows allocating buffers at once | Cap concurrency; narrow rows |
| Disk | Concurrent large reads/writes on one spindle set | Separate storage, or serialise |
The clean shape: parallel independent extracts into separate staging → synchronisation point → sequential dependent work.
SQL push-down vs pipeline transformation
Section titled “SQL push-down vs pipeline transformation”A decision rule that resolves most design arguments.
Do it in SQL when:
- Both sides of a join live in the same database.
- The operation is set-based — join, aggregate, deduplicate, window function.
- An index can serve the predicate or the ordering.
- The result is much smaller than the input (filtering, aggregation).
Do it in the pipeline when:
- Sources are heterogeneous — a file joined to a table, or two different servers.
- The work is genuinely row-by-row: parsing, calling out to code, format conversion.
- The database is a shared OLTP system you must not load with analytical work.
- Pushing down would require a linked server, whose distributed plans are frequently worse than moving the data.
The hybrid that usually wins: extract with a lean query, transform minimally in the pipeline, bulk-load to staging, do the heavy set-based work in SQL.
Partition-aware loading and partition switching
Section titled “Partition-aware loading and partition switching”For very large fact tables, partitioning changes the shape of the load. The ETL-relevant technique is partition switching: ALTER TABLE … SWITCH moves an entire partition between tables as a metadata operation, without moving rows.
1. Create a staging table with the SAME schema, SAME filegroup, and matching indexes and constraints as the target partition.2. Bulk-load new data into it — fast, unindexed, minimally logged if configured.3. Build the required indexes on staging.4. Add a CHECK constraint proving every row falls inside the partition's boundaries.5. ALTER TABLE stg.X SWITCH TO dbo.Fact PARTITION n; -- metadata onlyWhy it matters for SSIS:
- The expensive part happens away from the live table.
- The visible change to consumers is near-instantaneous.
- Reloading one day or month becomes cheap — switch the old partition out, load a fresh one, switch it in.
The requirements are strict, and getting them wrong makes the switch fail rather than corrupt anything: identical column definitions, same filegroup, aligned indexes, and constraints that guarantee the data range. The wider partitioning design — partition functions, schemes, and sliding-window maintenance — is a SQL Server topic sitting underneath the ETL rather than part of the package.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”Balanced Data Distributor fans rows across several identical downstream branches to parallelise CPU-bound transformation work. It helps when the bottleneck is transformation CPU, not I/O — and it multiplies memory use.
Multiple destinations from one source via Multicast is cheap in itself (no copy) but the branches run concurrently and compete for memory and target locks.
Network as the bottleneck is under-diagnosed. A pipeline pulling across a WAN can be latency-bound rather than throughput-bound, and no SSIS setting fixes that. Compressing at the source and moving files, then loading locally, often beats streaming rows across the link.
Destination triggers do not fire under fast load unless FIRE_TRIGGERS is specified — which is usually a performance benefit, and occasionally a correctness surprise if the trigger maintained something the load now skips.
Columnstore destinations behave differently from rowstore: batch size interacts with rowgroup quality, and small batches produce many small rowgroups that degrade query performance later. Load size is a query performance decision there, not only a load decision.
Identity and sequence contention on a hot destination can serialise otherwise-parallel loads. Staging tables without identity columns avoid it.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: tuning buffers first
Section titled “Anti-pattern: tuning buffers first”BAD: "The load is slow." → raise DefaultBufferMaxRows to 100000 → raise DefaultBufferSize to 100 MB → package now spools to disk and is slower → conclude SSIS is slow
Meanwhile: a Sort transformation is holding 40 million rows in memory.Structure first, settings last. Always.
Anti-pattern: row-by-row updates
Section titled “Anti-pattern: row-by-row updates”BAD: Source → Lookup → OLE DB Command (UPDATE … WHERE Id = ?) 500,000 changed rows = 500,000 round trips.
BETTER: Source → Lookup → OLE DB Destination (fast load) → staging then one set-based UPDATE … FROM staging in the control flow.Anti-pattern: parallel loads into one table with TABLOCK
Section titled “Anti-pattern: parallel loads into one table with TABLOCK”BAD: four Data Flow Tasks, all fast-loading dbo.FactSales with TABLOCK → they block each other; effective parallelism is one, plus deadlock risk
BETTER: four staging heaps, one per branch, then a single consolidating INSERT.Other frequent mistakes
Section titled “Other frequent mistakes”SELECT *in sources, then complaining about memory.- Not using fast load, or using it with a commit size nobody chose.
- Running packages on the database server and letting SSIS and the engine fight for RAM.
- Assuming a load is minimally logged without checking the recovery model.
- Full-cache Lookups on huge dimensions, so the package appears hung during pre-execute.
- Dropping indexes on a live target without measuring whether the rebuild costs more than it saves.
- Quoting benchmark numbers from a blog. Throughput depends on row width, hardware, and contention; the only numbers that matter are the ones you measured on your data.
Failure modes, recovery, and troubleshooting
Section titled “Failure modes, recovery, and troubleshooting”| Symptom | Likely cause | Action |
|---|---|---|
| Slow, memory climbing steadily | Fully blocking transformation | Move Sort/Aggregate into SQL |
| Slow, disk busy on the SSIS box | Buffer spooling | Check BufferTempStoragePath; reduce demand or add RAM |
| Hangs before rows move | Full Cache Lookup warm-up | Restrict the reference query; try Partial Cache |
| Destination slow, source fast | No fast load; too many indexes; triggers; lock contention | Enable fast load; index after; check FIRE_TRIGGERS; check blocking |
| Log file grows enormously | Not minimally logged, or commit size too large | Check recovery model and bulk path; reduce commit size |
| Deadlocks between packages | Concurrent TABLOCK writes |
Separate staging tables; serialise the apply |
| Fast in dev, slow in prod | Different volume, contention, or memory | Compare execution_component_phases between environments |
| Throughput collapses partway | Spooling begins, or the destination’s indexes grow | Watch memory; consider batching the apply |
| Package gets slower every week | Growing source with no incremental strategy | See Incremental Loads |
When performance regresses suddenly, compare a good and bad execution directly:
-- Phase timings for two executions side by sideSELECT p.subcomponent_name, p.phase, SUM(CASE WHEN p.execution_id = @good_id THEN DATEDIFF(MILLISECOND, p.start_time, p.end_time) END) AS good_ms, SUM(CASE WHEN p.execution_id = @bad_id THEN DATEDIFF(MILLISECOND, p.start_time, p.end_time) END) AS bad_msFROM catalog.execution_component_phases AS pWHERE p.execution_id IN (@good_id, @bad_id)GROUP BY p.subcomponent_name, p.phaseORDER BY ISNULL(bad_ms,0) - ISNULL(good_ms,0) DESC;The component at the top of that list is where the regression is.
Security and configuration considerations
Section titled “Security and configuration considerations”WITH (NOLOCK)for speed is a correctness decision, not a tuning knob. Dirty reads can produce duplicated or missing rows in the extract.- Turning off Check Constraints for throughput admits invalid data. Only do it when validation genuinely happened elsewhere, and say so in the package documentation.
BufferTempStoragePathspool files contain pipeline data. Point it at a secured location, not a world-readable temp folder.- Reading from a replica for performance can mean reading slightly stale data — acceptable for most warehouse loads, not for all.
- Higher parallelism increases blast radius. A runaway parallel load can starve the source system that other applications depend on.
Testing, validation, and operational monitoring
Section titled “Testing, validation, and operational monitoring”- Test at realistic volume. A pipeline that works on 10,000 rows tells you nothing about 40 million; blocking behaviour and memory pressure only appear at scale.
- Record a baseline. Store duration, rows loaded, and rows rejected per batch in the audit table. Without a baseline, “it’s slow” is unfalsifiable.
- Alert on duration trend, not just failure. A step that has crept from 4 to 40 minutes is a failure that has not happened yet.
- Re-measure after every structural change — including changes that “obviously” help.
- Watch source-side impact, not just ETL duration. An extract that halves its own runtime by hammering the OLTP source has not improved anything.
-- Duration trend per package over 30 daysSELECT package_name, CAST(start_time AS DATE) AS run_date, COUNT(*) AS runs, AVG(DATEDIFF(SECOND, start_time, end_time)) AS avg_seconds, MAX(DATEDIFF(SECOND, start_time, end_time)) AS max_secondsFROM catalog.executionsWHERE start_time > DATEADD(DAY, -30, SYSDATETIMEOFFSET()) AND end_time IS NOT NULLGROUP BY package_name, CAST(start_time AS DATE)ORDER BY package_name, run_date;The tuning checklist
Section titled “The tuning checklist”Work down this list. The early items account for most of the gain.
- Is the source query fast on its own, with a sensible plan?
- Are you selecting only the columns and rows you need?
- Is the destination using fast load, with a considered commit size?
- Are there blocking transformations that SQL could do instead?
- Are Lookups restricted, in the right cache mode?
- Are you doing row-by-row updates instead of staging plus a set-based apply?
- Are destination indexes and constraints appropriate for a bulk load?
- Is the load minimally logged where it could be?
- Can independent work run in parallel without contending for the same table?
- Is SSIS competing with the Database Engine for memory?
- Only now: are buffer settings limiting throughput, and is spooling happening?
Trade-offs and alternatives
Section titled “Trade-offs and alternatives”| Decision | Option A | Option B | Choose A when |
|---|---|---|---|
| Transformation location | SQL push-down | Pipeline | Both sides share a database |
| Destination locking | TABLOCK on |
off | The load owns the table exclusively |
| Commit size | Large | Small | Failure is unlikely and the log can take it |
| Indexes | Load then index | Keep indexes during load | The table is offline or staging |
| Apply method | Partition switch | MERGE/INSERT |
The table is partitioned and the scope aligns |
| Parallelism | Parallel with separate staging | Serial | Sources and destinations do not contend |
| Lookup | Full Cache | Partial Cache | The reference set fits comfortably in memory |
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”Q: A nightly load has gone from 40 minutes to four hours with no code change. How do you investigate?
Compare a good and a bad execution using catalog.execution_component_phases — the component whose phase time grew is the regression. Then reason about why: data volume growth (the extract now scans more), a plan change on the source query (statistics, parameter sniffing), destination index growth, new contention from another job, or memory pressure causing buffer spooling. The catalog comparison turns a vague complaint into one component to explain.
Q: Why is narrowing rows more effective than increasing DefaultBufferSize?
Because buffer capacity is computed from declared column widths. Wide rows hit the byte limit long before the 10,000-row limit, so fewer rows travel per buffer regardless of how large you make it. Removing an unused VARCHAR(4000) column can multiply rows-per-buffer, whereas raising the size just allocates more memory for the same inefficiency — and can push the package into spooling.
Q: When would you deliberately turn off Check Constraints on a fast-load destination? Only when validity is guaranteed elsewhere — for example the pipeline resolved every foreign key through Lookups that redirect no-match rows, and the target is a staging table you control. The trade is real: a constraint violation under fast load fails the whole batch rather than a row, so the check is also a diagnostic. Turning it off without an upstream guarantee is how invalid data reaches a warehouse.
Q: Explain execution trees and why they matter for memory. The engine splits a data flow into execution trees, starting a new one at each source and at each asynchronous component’s output, because that is where new buffers are allocated. Synchronous components reuse the buffer they were given, so a fully synchronous pipeline has flat memory use regardless of row count. Each asynchronous component adds a buffer set; each fully blocking one holds the entire input. That is the structural reason a single Sort can turn a streaming job into one that spools to disk.
Q: Four parallel data flows all load the same fact table and it is slower than running them serially. Why?
Lock contention. With TABLOCK on a fast-load destination they serialise on a bulk update lock and can deadlock; without it they contend on pages and indexes. Effective parallelism collapses to roughly one, plus overhead. The fix is to give each branch its own staging heap, then consolidate with a single set-based insert — or to serialise deliberately.
Q: How does partition switching change the economics of a large fact load?
It moves the expensive work off the live table. You bulk-load an unindexed staging table with the same schema and filegroup, index it, prove the range with a CHECK constraint, and then SWITCH — a metadata operation. Consumers see a near-instantaneous change instead of hours of contention, and reloading a single day becomes cheap. The cost is strict structural requirements: identical columns, same filegroup, aligned indexes, and a constraint that guarantees the boundary.
Q: Someone proposes running SSIS on the SQL Server to “avoid network overhead”. Argue both sides.
For: no network hop for the data, simpler infrastructure, and Windows authentication is easier. Against: SSIS buffers and the SQL Server buffer pool compete for the same RAM, and SQL Server will take everything max server memory allows — pushing SSIS into disk spooling, which is far more expensive than the network hop you saved. If you must co-locate, cap max server memory deliberately and measure. For large loads, a separate ETL machine is usually the better answer.
Q: What performance claim should you never make in a design document? A specific throughput number carried over from elsewhere. Rows-per-second depends on row width, hardware, contention, recovery model, indexing, and the destination’s state. Quote your measurements on your data, describe the conditions, and state which structural property (no blocking components, fast load, minimal logging) is responsible.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Data Flow: Sources, Destinations, Transformations, and Pipeline Internals
- SSIS Control Flow: Tasks, Containers, and Precedence Constraints
- SSIS Incremental Loads: Watermarks, CDC, Change Tracking, and Staging
- SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints
- SQL Server Indexes
- SQL Server Aggregate Functions
- Common Table Expressions
Official Microsoft references
Section titled “Official Microsoft references”- Data Flow Performance Features
- OLE DB Destination
- Lookup Transformation
- Sort Transformation
- catalog.execution_component_phases
- catalog.execution_data_statistics
- Prerequisites for Minimal Logging in Bulk Import
- Partitioned tables and indexes
- ALTER TABLE (Transact-SQL) — the
SWITCH PARTITIONclause and its restrictions