SSIS Data Flow up to 3× faster: tuning the buffer and Fast Load

There's a scenario that repeats in almost every ETL project on SQL Server: an SSIS package that "always worked" starts taking too long as the volume grows. The first reaction is usually to blame the hardware or rewrite the Data Flow. Most of the time, the problem is far cheaper to solve — it's in the buffer sizing, a setting almost everyone leaves at its default.

In this guide I show the three tweaks with the highest return per minute invested: AutoAdjustBufferSize, DefaultBufferMaxRows and Fast Load at the destination. On a 10-million-row load, this combination can bring execution time down from around 2 minutes to 44 seconds — without changing a single line of logic.

How the SSIS Data Flow moves data

The Data Flow doesn't process row by row: it works with buffers — blocks of memory that carry a set of rows at a time, from source to destination. The more efficiently these buffers are filled, the fewer "trips" the engine makes and the higher the throughput.

The size of each buffer is defined by two Data Flow Task properties:

• DefaultBufferMaxRows — maximum number of rows per buffer. Default: 10,000.
• DefaultBufferSize — memory limit per buffer. Default: 10 MB.

SSIS puts as many rows as possible into the buffer without exceeding either limit. The important detail: these defaults come from an era when server memory was scarce. On modern machines they're small and hold performance back without giving any obvious sign.

Step 1 — Turn on AutoAdjustBufferSize

Because the two properties above limit each other, it's easy to adjust one and have the other "cut off" the effect. That's why SQL Server 2016 introduced the AutoAdjustBufferSize property.

When you set it to True (the default is False), SSIS starts calculating the buffer size from DefaultBufferMaxRows and simply ignores DefaultBufferSize. In other words: you say how many rows you want per buffer and the engine adjusts the memory on its own.

-- Data Flow Task properties
AutoAdjustBufferSize = True
DefaultBufferMaxRows = 50000

This eliminates the tug-of-war between the two properties and makes tuning predictable: you reason in rows, not in bytes.

Step 2 — Use Fast Load and control the commit

Filling larger buffers only helps if the destination also accepts data in batches. On the OLE DB Destination, make sure of:

• Access Mode = "Table or view - fast load" — uses the BULK INSERT interface instead of inserting row by row.
• MaxInsertCommitSize = 100000 — controls how many rows go into each transaction (commit). Very small batches create transaction overhead; huge batches hold the log and memory. A value in the hundreds of thousands is usually a good starting point.

-- OLE DB Destination
AccessMode = Table or view - fast load
MaxInsertCommitSize = 100000
Table lock = True   -- when the load window allows

Step 3 — Measure, adjust and monitor

The gain depends on the shape of your data, so measure before and after. In a classic test documented by the community, a 10-million-row load dropped from just over 2 minutes to 44 seconds simply by raising DefaultBufferMaxRows to 50,000 (with AutoAdjust on) and the commit to 100,000.

Rules of thumb for DefaultBufferMaxRows:

• Wide rows (many columns / large types): use fewer rows per buffer. Each row takes more memory, so full buffers fill up fast.
• Narrow rows: use more rows per buffer to take advantage of the memory.
• Buffers that are too large hurt too — the goal is a balance between filling the buffer and keeping a healthy commit rate.

How to know you went too far: watch the "Buffers Spooled" counter in the Data Flow log. If it climbs above zero, SSIS can't keep the buffers in memory and has started paging to disk — a clear sign to lower DefaultBufferMaxRows (or free up more memory for the service).

Quick checklist

1. Data Flow Task → AutoAdjustBufferSize = True.
2. Data Flow Task → DefaultBufferMaxRows calibrated to the row width (start at 50,000).
3. OLE DB Destination → fast load + MaxInsertCommitSize (start at 100,000).
4. Run with real data, compare the time and watch Buffers Spooled.

Conclusion

Before rewriting packages or asking for a bigger server, spend ten minutes reviewing the Data Flow buffer. AutoAdjustBufferSize, a well-calibrated DefaultBufferMaxRows and Fast Load at the destination form one of the best cost-benefit tweaks in ETL on SQL Server: the package stays the same, but the load finishes in a fraction of the time. It's sizing, not magic — and it applies to every pipeline still running on factory defaults.