Day 5 – Performance Tuning Backups
Why does backup performance matter?
As your database gets bigger, backups take longer to complete. This makes sense, more data has to be written to disk which is going to take longer.
The reason it becomes a problem is that your organisation will have a Recovery Point Objective (RPO), which is the most data loss they’re willing to accept in the event of a disaster. Now, it might not be officially announced but I’m sure management has a number that’s lower than you think.
To hit your RPO you need to have less time than that between your backups. So let’s say your RPO is 15 minutes. This means you need a backup at LEAST every 15 minuted to hit this target. If your transaction log backups are taking 20 minutes, you’ll never make it.
Obviously we can’t reduce the amount of data being created, your boss won’t thank you for rate limiting customers!
So all we can do is to make things flow out faster. Let’s see how we do that:
Testing your choices
when you’re evaluating performance options for your backups you want some metrics to measure them against. Does changing BlockSize make that much of a difference?
Backup-DbaDatabase produces a lot of output, but the one you’re really going to be interested in is Duration. You’re job is going to be to minimise this as much as possible without impacting database performance.
To test changes in isolation you’ll want to remove the biggest variables in backup performance, network and storage speed. To do this, there’s a magical backup device that’s got near infinite speed and storage you can use. This is the black hole known as the bit bucket, /dev/null or NUL:, everything thrown into this pit disappears as fast as it’s put in.
So if you’re like to see how long it takes to backup your database without the overhead of storage to compare the benefits of different blocksizes you could run:
$outNoBuffer = Backup-DbaDatabase -SqlInstance server1\instance -Database Prod -FilePath NUL -CopyOnly
$outBuffer = Backup-DbaDatabase -SqlInstance server1\instance -Database Prod -FilePath NUL -CopyOnly -BufferCount 32
$outNoBuffer.Duration
$outBuffer.Duration
$outNoBuffer.Duration.TotalMilliSeconds - $outBuffer.Duration.TotalMilliSeconds
And you’ll be able to see the difference that Changing the BufferCount will make. Notice that you need to use a specific value from Duration to avoid an error message
By monitoring your SQL Instance during the backups you’d also be able to compare the overhead on the database.
Be careful if running against production. You could cause performance issues, or if you don’t specify CopyOnly you could break restore chain. If you can, do it on a test system. Be careful
Backup Compression
The first one to look at is reducing the amount of data that has to be written to a backup. Wait, didn’t I just ay we couldn’t do that? No, we’re not reducing the data being written into the database, just the amount that gets written out as backups.
The simple trick here is to compress the data being backed up. You get a two fold performance improvement with compression. You are writing less data to disk, and sending less data across the network.
If you’re running Enterprise Edition SQL Sever you will have compression available. If you’re running Standard Edition SQL Server on version 2008R2 or newer you’ll also have compression available.
The only downside is an increase in CPU usage while the backup is running. Unless your database is CPU bound you probably won’t notice the extra load during the backups.
Enabling the backup compression with Backup-DbaDatabase couldn’t be easier, it’s just a switch
$output = Backup-DbaDatabase -SqlInstance server1\instance -Database Master -FilePath \\backups\sql\EndOfYear2020_master.bak -CompressBackup
To see the amount of compression you’re getting in the above example you’d just need to look at the value of $output.CompressionRatio, the bigger the number the bigger benefit you’re seeing.
Backup Striping
The second option we can look at is Striping. This makes SQL Server stripe the backup across multiple files, up to a maximum of 64. If each of these files is on a different disk this can massively increase the throughput as you’ll reduce the I/O on each disk. You can even see improvements on a single disk with a lot of current hardware
This option isn’t without issues though. 2 common problems are:
– SQL Server will only write at the speed of the slowest device, so if one device is running slow they’ll all work at that speed
– SQL Sever cannot restore the database without all the backups. If one file is missing, game over
So if we take a backup like this:
$results = Backup-DbaDatabase -SqlInstance server1\instance -Database prod -Path \\backups\sql$\instance\ -Type Log -FileCount 4
$results | Select-Object -ExpandProperty FullName
We’ll end up with the following backup files.
\\backups\sql$\instance\master_202004291058-1-of-4.trn
\\backups\sql$\instance\master_202004291058-2-of-4.trn
\\backups\sql$\instance\master_202004291058-3-of-4.trn
\\backups\sql$\instance\master_202004291058-4-of-4.trn
But that’s backing up to a single location. So lets imagine that you have 3 backup locations on different servers, you can backup to them like this:
Backup-DbaDatabase -SqlInstance server1\instance -Database prod -Path \\backups1\sql$\instance\, \\backups2\sql$\instance\, \\backups3\sql$\instance\ -Type Log | select -ExpandProperty FullName
Notice that specifying multipe paths means we don’t need to proved FileCount parameter. This will create the following backup folders
\\backups1\sql$\instance\master_202004291058-1-of-3.trn
\\backups2\sql$\instance\master_202004291058-2-of-3.trn
\\backups3\sql$\instance\master_202004291058-3-of-3.trn
Now you’re hitting 3 diffent storage devices, each of which will be through it’s own network connection so you’ve a lot more overhead for fast writes.
Backup MaxTransferSize
As Backup-DbaDatabase only works with DISK backup targets we’ll just consider them for this section. By default SQL Server sends out backup database for DISK targets in 1MB chunks. Depending on your storage increasing this value may yield better performance. You can set this to any value up to 4MB in 64 KB steps (Backup-DbaDatabase will check for allowed values). Think of increasing this as making the ‘lumps’ of data that SQL Server sends bigger.
These chunks of data are built up in in-memory buffers in the SQL Server instance’s allocated memory. So if you instance is starved of memory then you may see some performance issues
You can set this value on a per backup basis like:
Backup-DbaDatabase -SqlInstance server1\instance -Database prod -Path \\backups\SQL1 -MaxTransferSize 1.5mb
Backup-DbaDatabase makes use of the normal .NET unit markers, so you can just specify the size in mb chunks, rather than converting into kb.
Backup BufferCount
This option works in conjunction with MaxTransferSize. If MaxTransferSize increases the size of the chunks of data sent out by SQL Server, this value controls the number of chunks SQL Server sends. All data is read and written via Buffers, so if you increase the number of buffers more data can be read and then written out in a short period of time.
However, if you specify too high a BufferCount and a high MaxTransferSize then you can run out of memory as the total memory taken up will be BufferCount * MaxTransferSize. So if you have MaxTransferSize set to 4mb and Buffercount set to 128, you’ll be asking 512mb of Buffers to be allocated for the backup.
Having these values to high can cause an Out Of Memory error, Microsoft have an article on this here. If you are not testing on a test system I highly recommend you read the article to work out how best to set these values as trying random values isn’t a good idea.
To set this value you set it like this:
Backup-DbaDatabase -SqlInstance server1\instance -Database prod -Path \\backups\SQL1 -BufferCount 16
Conclusion
Tuning backups to peak performance is a bit of an art, but with a good test strategy you can get some great performance benefits.
All posts in this series can be found at 31 Days of dbatools Backup and Restores
1 Pingback