Backup & Recovery from First Principles, Part 9: BUFFERCOUNT, MAXTRANSFERSIZE, and Friends – Benchmarked
The BACKUP statement has a set of tuning knobs most people never touch: BUFFERCOUNT, MAXTRANSFERSIZE, BLOCKSIZE, and striping across multiple files.[1] Advice about them tends to be folklore, so this post is numbers instead: nine configurations, each run twice, against the same database on the same hardware. I benchmarked backup tuning once before; this extends that work with more of the option matrix.
This is part 9 of the backup and recovery series (part 1).
What the Knobs Do
A backup is a pipeline: reader threads pull pages from the data files into a pool of buffers, and writer threads drain those buffers to the backup devices. The knobs shape that pipeline:
BUFFERCOUNT– how many buffers are in the pool. More buffers let readers run ahead of writers (and vice versa) instead of stalling on each other. Total buffer memory isBUFFERCOUNT x MAXTRANSFERSIZE, taken from outside the buffer pool – it is possible to cause memory pressure with reckless values.MAXTRANSFERSIZE– the size of each buffer, i.e., the largest unit of transfer to the device. Default 1 MB, maximum 4 MB.BLOCKSIZE– the physical block size written to the medium. A tape-era knob.- Striping –
TO DISK = f1, DISK = f2, ...writes to several files in parallel, each with its own writer. COMPRESSION– included as the baseline comparison, because everything else is a rounding error next to it.
The Benchmark
Setup: a 5.75 GB table filled with semi-random data (CRYPT_GEN_RANDOM padding, so it compresses realistically rather than trivially), backed up WITH COPY_ONLY to keep the demo chain clean (part 4). Each configuration ran twice; the table reports run 2 (warm, steady-state). Hardware: a single local NVMe SSD, SQL Server 2019 CU32. (The same matrix on SQL Server 2025 RTM on the same machine showed the same qualitative pattern – compression dominant, BUFFERCOUNT + MAXTRANSFERSIZE fastest at 5.4 s / 1,162 MB/s, BLOCKSIZE slower – though with more run-to-run variance.)
|
1 2 3 4 5 |
/* representative example - configuration 6 */ BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\bench\b6.bak' WITH COPY_ONLY, INIT, COMPRESSION, BUFFERCOUNT = 64, MAXTRANSFERSIZE = 4194304; |
# |
Configuration | Duration (s) | Throughput (MB/s) | File size (MB) |
|---|---|---|---|---|
| 1 | No compression (baseline) | 22.9 | 275 | 6,315 |
| 2 | COMPRESSION | 8.2 | 775 | 702 |
| 3 | COMPRESSION + BUFFERCOUNT = 64 | 7.3 | 869 | 702 |
| 4 | COMPRESSION + BUFFERCOUNT = 256 | 6.7 | 944 | 702 |
| 5 | COMPRESSION + MAXTRANSFERSIZE = 4MB | 8.0 | 794 | 702 |
| 6 | COMPRESSION + BC = 64 + MTS = 4MB | 6.4 | 980 | 702 |
| 7 | COMPRESSION + BLOCKSIZE = 65536 | 11.0 | 573 | 702 |
| 8 | COMPRESSION + 4-file stripe | 9.2 | 687 | 4 x ~176 |
| 9 | COMPRESSION + stripe + BC = 64 + MTS = 4MB | 8.1 | 784 | 4 x ~176 |
Reading the Results
- Compression is the whole ballgame. 2.8x faster and a 9x smaller file, in one keyword. Everything below it on the list is fighting over the remaining margin. If you tune exactly one thing, tune this (and note the file was 702 MB regardless of the other knobs – they change speed, not size).
- BUFFERCOUNT is the best of the minor knobs. 64 buffers took 11% off; 256 took 18% off. The default computed value is conservative. Watch the memory math: config 4 at the default 1 MB transfer size held 256 MB of buffers; config 6 (64 x 4 MB) also 256 MB.
- MAXTRANSFERSIZE alone did little, but compounded with BUFFERCOUNT. Config 6 (both knobs) was the overall winner at 6.4 s / 980 MB/s – 22% faster than compression alone.
- BLOCKSIZE actively hurt (34% slower than plain compression). On disk targets, leave it alone; it exists for tape and some VDI/object-storage backup targets that document a required value.
- Striping to the same disk did nothing useful, and that is the expected result: four writers contending for one NVMe device is not parallelism, it is queueing. Striping earns its keep when the files land on different spindles/paths/network targets, or when a per-file throughput ceiling (common with network destinations and some backup appliances) is the bottleneck.
Caveats, Prominently
These numbers describe my 5.75 GB database on one NVMe SSD. Your bottleneck may be the read side, the network, a backup appliance, or CPU (compression costs CPU – on a CPU-saturated server it can slow things down and steal cycles from the workload). The method transfers even if the numbers do not: build a matrix, run each configuration at least twice, discard run 1, and benchmark on the system you actually care about. For measuring, msdb.dbo.backupset stores exact start/finish times and bytes for every run (part 2).
Also worth knowing: since SQL Server 2019 CU5, striping is no longer required to get parallel backup performance improvements for TO URL backups, and TDE-encrypted databases have their own MAXTRANSFERSIZE interaction – MAXTRANSFERSIZE > 65536 enables optimized compression with TDE,[2] which segues nicely into part 10: compression, encryption, and TDE gotchas.
What did the matrix look like on your hardware? I would genuinely like to know. Comments below, or find me on Bluesky or LinkedIn.
References
- BACKUP (Transact-SQL) – Microsoft Learn. Full syntax and semantics for BUFFERCOUNT, MAXTRANSFERSIZE, BLOCKSIZE, and media sets. ↩
- Backup Compression (SQL Server) – Microsoft Learn. Compression behavior, CPU trade-offs, and the TDE + MAXTRANSFERSIZE interaction. ↩