Compression Backup with BUFFERCOUNT parameter

If you want to make you backup faster, you can try BUFFERCOUNT parameter with compression backup.

From Book Online description:

BUFFERCOUNT = { buffercount | @buffercount_variable }

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.
The total space used by the buffers is determined by: buffercount * maxtransfersize.

Below is my testing result, all backups use compression parameter. the data file is 195GB with 11GB used. by using compression, the backup file is 8.4GB

1. No BUFFERCOUNT parameter

BACKUP DATABASE abc TO DISK = 'c:\temp\abc.bak' WITH COMPRESSION

it completed with 88MB/sec throughput, avg cpu% is 45

2. Add BUFFERCOUNT parameter

BACKUP DATABASE abc TO DISK = 'c:\temp\abc.bak' WITH COMPRESSION, BUFFERCOUNT = 50

it completed with 105MB/sec throughput, avg cpu% is 50

Next I tried BUFFERCOUNT = 100, BUFFERCOUNT = 150 and BUFFERCOUNT = 200

here is the result:

based on the diagram upper, when BUFFERCOUNT = 100, Throughput reach the ceiling, comparing with the first command without BUFFERCOUNT parameter(default), we made the backup process 30% faster.

you can also use multiple backup files to reach the same goal. 

3. Test multiple backup files

my sql server has 2 CPU, so I use 2 backup files:

BACKUP DATABASE abc TO 

DISK = 'c:\temp\abc1.bak',

DISK = 'c:\temp\abc2.bak'

WITH COMPRESSION

it completed with 115MB/sec throughput. avg cpu 60%.

4. Test multiple backup files with BUFFERCOUNT parameter

BACKUP DATABASE abc TO 

DISK = 'c:\temp\abc1.bak',

DISK = 'c:\temp\abc2.bak'

WITH COMPRESSION, BUFFERCOUNT = 50

based on the diagram upper, there is only 5% performance difference between using BUFFERCOUNT and no BUFFERCOUNT.

Although BUFFERCOUNT makes higher CPU %, the backup always runs during off business, so if you can afford the higher CPU% penalty, it is worth to try BUFFERCOUNT. Please test the appropriate BUFFERCOUNT value based on your environment. 

Reference:

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx