If the only times you use SQL Server Backup and restores are via the GUI or just doing the standard

BACKUP DATABASE [MyDB] TO DISK ='G:\Backup\MyDB_20160209.bak'

You are missing out on a treat, doing a few tweaks on your backup commands can speed up your backup times dramatically (I’m seeing 40-50% reduction in time with my current tuning). What I’ve listed below is some quick steps you can take to start seeing much quicker backups. For a more detailed breakdown and I really suggest watching them all, is Sean McCown videos over at Best Backup Class Evar:1-6 as he goes into much greater detail and looks at the costs and breaks things down a lot more.

Define the tuning process

I will be using Ola Hallengren Backup DatabaseBackup command (There isn’t much difference so if you just use the standard T-SQL you should be fine).

The process is simple, we will make a change to the backup, monitor and repeat till we are happy.  changing the number of files, buffercount and max transfersize.

To get better monitoring than just the duration (we can get that from msdb or the ola CommandLog, we turn on a few traceflags:

DBCC TRACEON (3604, 3213)

Traceflag 3604 writes outputs to the SSMS window instead of the log and 3213 output internal information on backups. So if you run a backup you get an output like:

Backuptuning01

NOTE – Depending on your settings for Ola’s script your output to window will be much longer as it repeats the steps for the verify restore etc. Make sure you only track the actual backup not the others.

A standard backup would look something like:

DBCC TRACEON (3604, 3213)
EXECUTE [InstanceState].[dbo].[DatabaseBackup] @Databases = 'MyDB',
    @Directory = '\\BackupStorage\SQLBackups\',
    @BackupType = 'FULL',
    @Verify = @verify,
    @CleanupTime = @clean,
    @CheckSum = @checksum,
    @LogToTable = @log

From the output the main thing we want to care about is that final line showing you

BACKUP DATABASE successfully processed 11784801 pages in 135.940 seconds (677.274 MB/sec).

this gives the duration in seconds and the throughput in MB/Secs

Step 1: Max Transfer Size

For this I kept it to trying either default, @MaxTransferSize = 2097152  or @MaxTransferSize = 4194304

DBCC TRACEON (3604, 3213)
EXECUTE [InstanceState].[dbo].[DatabaseBackup] @Databases = 'MyDB',
    @Directory = '\\BackupStorage\SQLBackups\',
    @BackupType = 'FULL',
    @Verify = @verify,
    @CleanupTime = @clean,
    @CheckSum = @checksum,
    @LogToTable = @log,
    @MaxTransferSize = 4194304

I saw a very hefty gain here going from

BACKUP DATABASE successfully processed 11784802 pages in 284.125 seconds (324.043 MB/sec).

to

BACKUP DATABASE successfully processed 11784801 pages in 172.946 seconds (532.355 MB/sec).

Step 2: Add more files

When your happy with which MaxTransferSize to use, keep that setting and start stripping your backups to multiple files.

With Ola’s stored proc is as easy as adding @NumberOfFiles = 5 to the end of your DatabaseBackup command.

DBCC TRACEON (3604, 3213)
EXECUTE [InstanceState].[dbo].[DatabaseBackup] @Databases = 'MyDB',
    @Directory = '\\BackupStorage\SQLBackups\',
    @BackupType = 'FULL',
    @Verify = @verify,
    @CleanupTime = @clean,
    @CheckSum = @checksum,
    @LogToTable = @log,
    @MaxTransferSize = 4194304,
    @NumberOfFiles = 5

Start with 1 then go to 2, 5, 10, xx I saw a nice bite around 5, but it’s going to be different depending on hardware so always try different numbers, I saw an increase  starting with 1 file going to 5.

BACKUP DATABASE successfully processed 11784801 pages in 172.946 seconds (532.355 MB/sec).

to

BACKUP DATABASE successfully processed 11784801 pages in 143.104 seconds (643.369 MB/sec).

 

Step 3: Buffer Count

Now we build on steps to 1-2 with Buffer count. the numbers here are really wild to play with, on my work machine i saw gains around 500 but if you have a powerfull machine you could see benifits going up to 2-3k

For buffercount we add @BufferCount = 500

DBCC TRACEON (3604, 3213)
EXECUTE [InstanceState].[dbo].[DatabaseBackup] @Databases = 'MyDB',
    @Directory = '\\BackupStorage\SQLBackups\',
    @BackupType = 'FULL',
    @Verify = @verify,
    @CleanupTime = @clean,
    @CheckSum = @checksum,
    @LogToTable = @log,
    @MaxTransferSize = 4194304,
    @NumberOfFiles = 5,
    @BufferCount = 500

For me I saw gains at 500 and was slower on 1000.

@BufferCount = 100

BACKUP DATABASE successfully processed 11784801 pages in 136.803 seconds (673.002 MB/sec).

@BufferCount = 500

BACKUP DATABASE successfully processed 11784801 pages in 134.516 seconds (684.444 MB/sec).

@BufferCount = 1000

BACKUP DATABASE successfully processed 11784801 pages in 135.940 seconds (677.274 MB/sec).

Summary

So we have gone from

BACKUP DATABASE successfully processed 11784802 pages in 284.125 seconds (324.043 MB/sec).

to

BACKUP DATABASE successfully processed 11784801 pages in 134.516 seconds (684.444 MB/sec).

Not to bad id say. just remember this is 1 database on server. If your using Ola’s stored procedure and using “USER_DATABASES” then you should play with the largest and smallest databases and see if the same settings work for both, or if you need to go somewhere inbetween.

Advertisements