Database backup is one of the prime responsibility of a DBA.

We are expected to make sure that there are no missed backups for production databases . In a typical production environment we would configure Full, Differential and Transactional Log Backups depending on the recovery model set for the database. Usually i configure full backups and transactional log backups for databases which have size upto 300 – 400GB. If the DB sizes are more than 500GB then i would include Differential in my backup strategy.

I was working with a client who has database sizes in more than 1TB. Full backups are configured to run every weekend between Friday night and Saturday Night. All the servers are configured to run full backups on either of the 2 days. As part of our server optimization efforts we wanted to improve the backup speed. Since the full backups are configured during off-business hours there is comparatively less load on server resources.

There are Microsoft suggested practices of improving the backup speed like using multiple devices, Instant File Initialization, Data / Backup Compression.

Since those are not an option for me in my environment, I tried to use different backup knobs MS gives in the backup command.

Particularly BufferCount and MaxtransferSize are the parameter which helped me achieve a speed improvement of around 25%.

BUFFERCOUNT –  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.

MAXTRANSFERSIZE – Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

The total space used by the buffers is determined by: buffercount * maxtransfersize.

SQL Server uses the majority of the memory for the buffer pool.  The remaining virtual address space has been referred to as memory outside buffer pool and memory to leave (MemToLeave).

Below is the setting that worked for me in my environment for database size between 500GB to 1TB

BufferCount: 40

MaxTransferSize: 2MB

Experiment with different sizes of database and try to get an optimum values best suited for your environment.

To configure the above setting, full backup should be configured as T-SQL script. I use Ola Hallengren’s maintenance scripts for our backups and his scripts allow the option of setting these parameters. Below is Ola Hallengren’s script configured in SQL Job with the additional backup parameters.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE dbo.DatabaseBackup @Databases = ‘USER_DATABASES’, @Directory = ‘C:\Backup’, @BackupType = ‘FULL’, @Compress = ‘Y’, @BufferCount = 40, @MaxTransferSize = 4194304” -b

references:

https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-18-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-2/

https://blogs.msdn.microsoft.com/psssql/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus/

https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition/

Leave your feedback as a comment.

– Hari Mindi