Skip to content

SSIS Large-Volume ETL and Performance Engineering

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.

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 engine

Guessing which one is binding is unreliable. Isolate instead — the procedure is below, and it takes minutes.

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.

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 query
SELECT subcomponent_name,
phase,
SUM(DATEDIFF(MILLISECOND, start_time, end_time)) AS elapsed_ms
FROM catalog.execution_component_phases
WHERE execution_id = @execution_id
GROUP BY subcomponent_name, phase
ORDER BY elapsed_ms DESC;
-- Rows moved between components (requires Verbose)
SELECT source_component_name,
destination_component_name,
SUM(rows_sent) AS rows_sent
FROM catalog.execution_data_statistics
WHERE execution_id = @execution_id
GROUP BY source_component_name, destination_component_name
ORDER 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 seconds
FROM catalog.executable_statistics AS es
JOIN catalog.executables AS e ON e.executable_id = es.executable_id
WHERE es.execution_id = @execution_id
ORDER 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.

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 ROW

Four 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.RegionSummary

Nothing blocks, the row count reaching the pipeline is tiny, and the update is one statement. No buffer setting was changed.

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 FastParse on 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 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, not DELETE — minimally logged and instant.
  • Keep staging in its own schema or database, with a recovery model appropriate to throwaway data.

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 0 and 2147483647 on different pages. Read the actual value in your destination.

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:

  1. Load into a heap staging table.
  2. Apply to the target, or switch the staged data in.
  3. 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.

  • Select only needed columns, in the query or the component’s column list.
  • Filter at the source. An indexed WHERE beats 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.

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 UNCOMMITTED is 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.
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.
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:

  1. Add ORDER BY JoinKey to the source query.
  2. Open the source component’s Advanced Editor.
  3. Set the output’s IsSorted to True.
  4. Set SortKeyPosition = 1 (then 2, 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 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: OFF is 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”

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.

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 BufferTempStoragePath and 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.

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:

  1. Run SSIS on a separate machine from the target instance.
  2. Cap SQL Server’s max server memory to leave headroom for the SSIS process.
  3. Reduce SSIS demand: narrower rows, no blocking components, restricted lookup caches.

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.

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 only

Why 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.

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.

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.

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.
  • 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 side
SELECT 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_ms
FROM catalog.execution_component_phases AS p
WHERE p.execution_id IN (@good_id, @bad_id)
GROUP BY p.subcomponent_name, p.phase
ORDER BY ISNULL(bad_ms,0) - ISNULL(good_ms,0) DESC;

The component at the top of that list is where the regression is.

  • 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.
  • BufferTempStoragePath spool 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 days
SELECT 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_seconds
FROM catalog.executions
WHERE start_time > DATEADD(DAY, -30, SYSDATETIMEOFFSET())
AND end_time IS NOT NULL
GROUP BY package_name, CAST(start_time AS DATE)
ORDER BY package_name, run_date;

Work down this list. The early items account for most of the gain.

  1. Is the source query fast on its own, with a sensible plan?
  2. Are you selecting only the columns and rows you need?
  3. Is the destination using fast load, with a considered commit size?
  4. Are there blocking transformations that SQL could do instead?
  5. Are Lookups restricted, in the right cache mode?
  6. Are you doing row-by-row updates instead of staging plus a set-based apply?
  7. Are destination indexes and constraints appropriate for a bulk load?
  8. Is the load minimally logged where it could be?
  9. Can independent work run in parallel without contending for the same table?
  10. Is SSIS competing with the Database Engine for memory?
  11. Only now: are buffer settings limiting throughput, and is spooling happening?
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

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.