At its core, backup speed boils down to just two numbers.
Question 1: How fast can SQL Server read data from disk?
You can measure this by doing a full backup to disk, but use the NUL: option:
|
1 |
BACKUP DATABASE MyDb TO DISK='NUL:' |
This does a backup, but doesn’t actually write it anywhere, so it’s a measure of how fast the data files can be read from storage. If you run this, know that you’re taking a real backup: this backup can break your differential backups.
Question 2: How fast can SQL Server write to the backup target?
You can measure this using disk benchmark tools like CrystalDiskMark or Diskspd. If you’re using a UNC path for your backup target, you may need to temporarily map a drive to get these tools to work. If you’re the only server/user who does backups to that location, focus on the sequential write speed numbers. If multiple servers/users will be doing backups at the same time, focus on the random write speed numbers.
Your backup speed is determined by the bottleneck – either question 1’s answer, or question 2’s.
How to Read Data from Disk Faster
Make the drives faster. Tune your storage. Ah, I wish it was as easy as typing that three-word sentence.
Make it smaller. Use Enterprise Edition’s data compression to shrink the size of your database on disk. Archive tables and indexes that you’re no longer using.
Read it from somewhere else. If you’re using AlwaysOn Availability Groups, consider doing your backups from the fastest replica available.
Back up less data. If you’re doing daily full backups, you can switch to weekly full backups instead, and differentials every day. Just know that the differential by itself is useless – you need to make sure you have both the full and the differential available to do a restore to the most recent point in time.
Back up more often. If you’re only doing log backups once an hour, try nibbling off the logs in smaller increments instead.
How to Write Data to Disk Faster
Use backup compression. This is included free with modern versions of SQL Server, and it gets even better with third party backup tools that often have tweakable levels of compression. Experiment with different settings to find the right compression setting for your biggest databases. It’s a balance of how much CPU the tool uses, versus how much smaller the file output becomes. If your bottleneck is slow writes, and you have lots of idle CPU time, then the tradeoff makes sense.
Write to a faster target. While SATA RAID 5 gives you lots of capacity for backups, it may not be fast enough to get the job done.
Don’t involve third party appliances. If you’re using a dedupe appliance like a Data Domain, it may not be fast enough to keep up with SQL Server’s reads. Try doing a temporary backup to a plain ol’ file share, and compare the backup speeds. Then work with your sysadmins to see if there’s a way to tune your backup appliance.
Team multiple network cards together. If you’re using 1Gb Ethernet and backing up to a network share, try teaming multiple 1Gb cards together on both the SQL Server and on the backup target on the other side.
Use multiple backup files. Try striping the backup to multiple files. If your backup target is fast enough to keep up, writing to 4 files can often improve your backup throughput by 4x.
And when you’re all done tuning backups, it’s time to tune restores. After all, a really fast backup doesn’t matter when the system is down – the business is more concerned about how long the restore will take.
37 Comments. Leave new
Nice tip on the bench marking tools. Thanks guys !
Just gonna leave this here http://sirsql.net/content/2012/12/13/20121212automated-backup-tuning/
Nic – yeah, that’s very cool. It just doesn’t identify whether the bottleneck is reading or writing or compression, which is what I was aiming at in this post. Thanks though!
Can you use COPY_ONLY option to make it NOT break any diff chains when using NUL: ?
John – yep!
You have me thinking about the backup/restore from multiple files. I have never tried this and I am not sure how much more difficult this would be to manage but I find it intriguing none the less. I noticed you did not mention DIFF backups, we use these to shorten the window during the work week and live with the long backup over the weekend. This also conserves disk space. Maybe you are just assuming we already do this?
thanks,
Don
apologies I just re-read the paragraph where Differentials are mentioned. I don’t know how I missed it the first time.
Another option for VLDB’s are snapshots with log backups.
Backup compression is not just a feature of Enterprise. We run 2012 standard and have backup compression turned on. It made a huge difference in the backup speed.
Your email states it is Enterprise only but the article states all versions.
John – version means year, edition means Std/Dev/EE.
Thanks I see the mentions of compression in the different contexts
What about with these knobs
MaxTransferSize = 4194304
BufferCount = 50
You can see a get a big improvement in backup and restore throughput from raising buffercount and to a lesser degree maxtransfersize. Just be aware that there are potential tradeoffs against increased I/O response times and CPU usage. You can basically do a DOS attack on yourself if you aren’t careful, which might be why Brent didn’t mention these settings.
The effects will also depend on your I/O subsystem. The last time I tested these thoroughly, I got the best performance (reducing both the backup and restore duration by half) with BUFFERCOUNT=50 and MAXTRANSFERSIZE=2097152. There were some slight improvements with settings pushed higher than that, but it wasn’t worth the extra CPU and I/O latency.
That was on a spinning disk NAS (NetApp) accessed over iSCSI. I think you’d be less likely to see increasing MAXTRANSFERSIZE help on flash storage.
SQLCAT also has some papers describing their findings.
SQLCAT: Tuning the Performance of Backup Compress
https://web.archive.org/web/20080505051354/http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx
SQLCAT: Guide to the Relational Engine (see sections on compression)
http://download.microsoft.com/download/0/F/B/0FBFAA46-2BFD-478F-8E56-7BF3C672DF9D/SQLCAT's%20Guide%20to%20Relational%20Engine.pdf
SQLCAT: Fast and Reliable Backup and Restore of Multi-TB Database over the Network
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx
Hey guys, for anyone interested, I’ve got a great session I do on this topic. I discuss how to go about tuning your backups/restores as well as the considerations you need to take into account.
You can find it here if you’re interested in watching it: http://midnightdba.itbookworm.com/EventVids/SQLSAT90BackupTuning/SQLSAT90BackupTuning.wmv
I’ve also got Minion Backup. It’s specifically designed to help you get the most out of tuning your backups. You can check it out here: http://www.MinionWare.net. It’s a free tool. And there are recorded webinars and other vids that show you everything it can do.
I haven’t tested this, but if you are bottlenecked on the “reading data from disk” part, would you expect adding additional files and balancing the data across them to increase read rate? Not sure if SQL Server would just read them serially one after the other or do them in parallel (thus potentially increasing rate).
Multiple database files = multiple reader threads. Along with more memory buffers (BUFFERCOUNT) and fast write targets (backup file count), your performance increases. Depending on what you’re writing to, and how busy you want to make it, larger or smaller IO block sizes (MAXTRANSFERSIZE)
Brent,
Sorry but I need to correct you on this:
Running a backup to a NUL file breaks your recovery chain completely – not just the differentials.
I had this problem the other week and that is what was doing it. For some reason, a third-party script was running this command at the end on a full backup and was causing subsequent log backups to be useless.
Matt – uh, no. http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/
Thanks – I will check it out.
If I’m wrong, I’m wrong – it’s a simple prospect 🙂
I was coming from the angle of a problem I was looking at recently that was causing DR restoration tests to fail and this was the issue.
Sorry Matt G, that’s not how it works. If you were to take a log backup to NUL then that would break your chain. But fulls never break your log chain. Ever.
D’OH!
You are correct, of course.
The situation I saw was that it was a LOG backup to NUL at the end of the full backup (going on timestamps).
Dammit – and I thought I had one over on Brent. Maybe next time…. :/
NIC teaming may not give you the advantage you expect
A TCP session between any two servers will generally use the same physical link to avoid the issue of frame re-ordering, refer https://en.wikipedia.org/wiki/Link_aggregation#Order_of_frames. It is my understanding that there is only a single physical session between any pair of Windows servers for SMB sharing, and all the traffic is multiplexed across that physical session. Freely translated, if you are writing all the backup stripes to a share on the same server you are probably getting minimal benefit from teamed NICs
Having said all that, I have used striped backups, multiple buffers, and 4MB transfer sizes for over a decade now; it works for me
Search a handful of trace flags: 3604, 3605, 3004, 3014. I am not at my computer right now so I am doing this from memory so somebody please correct me if I have one of these numbers wrong. Anyhow these trays flags are wonderful to use when there are strange nuances to backing up or restoring data.
Great article as always Brent… I just want to add my two cents… SQL Server backups can also make use of the SMB3 feature called multipath/multichannel. Ofcourse you need win 2012 on both ends and everything setup correctly but it can drastically shorten the backup windows. I did take advantage of this in a new installation of sql 2014 AlwaysOn cluster to drastically decrease the needed time to add big databases to the AG.
Disclaimer, I work for EMC that makes and sells Data Domain.
“Don’t involve third party appliances.” That is a little harsh for a couple of reasons. One Data Domain comes in different sizes with different throughput capability. If you are undersized for the workload it is going to be slow like most IT assets. In lab tests we see really good backup speeds from a 2TB SQL 2014 database sitting mostly on flash disks using compressed backups over a 10Ge network to 2 ports on a Data Domain 4200 using 8 backup devices. We had a fairly high TPC-E workload running so the 10gig link gave out first. Since most of our enterprise accounts don’t want critical backup data sprayed all over the data center on file shares, DD offers a better alternative to either that or the old dump and sweep. So I think you can get both good backup speed and better manageability if you use the right combination of technology and pay attention to the details of balancing the data protection stream end-to-end. Kind of like FastTrack Backup and Restores.
The rest was really good reading 🙂
Phil Hummel @GotDisk
EMC
Hi Brent,
As an accidental DBA I’m just beginning to work my way through backup and recovery. So far I have set up full backups and collected the business requirements regarding RPO. Referring to RPO full backups won’t suffice in the long run. I definitely need to set up log backups and have already started to do so. But I am struggeling to weigh out the pros and cons of log backups. I definitely have databases like reporting databases which are well served with one full backup a day as data are not gonna be changed later on till the next daily load. These databases don’t need log backups and are in simple recovery mode. However I’m asking myself if log backups would do any harm. From a maintenance and management point of view it might be beneficial to have all backup and recovery work the same way. If I am just doing log backups say for about 30% of the databases I would think that I don’t really get into the management properly if it would be simple (and stupid) for all databases. What do you think about this and what is your advice?
Cheers
Martin
Martin – start by asking what the business needs:
https://www.brentozar.com/archive/2011/12/letters-that-get-dbas-fired/
Then, give yourself the simplest solution that meets those objectives. Don’t over-complicate it – you have enough other problems you need to worry about, like security, capacity planning, and performance. If you have servers that the business doesn’t need point-in-time recovery for, then just stick with fulls.
Hey Martin, I’ll be glad to discuss this topic with you offline as I think a full discussion on pros and cons of log backups is a bit off topic here. Feel free to write me and we’ll go over the whole thing.
Hi,
I do have SQL 2014 high availability setup in Prod env. Server A – Primary, B-Secondary and C -DR. Server A to B are synchronous commit and automatic failover, Server A to C are asynchronous commit and manual failover. I need to setup fullbackup and diff backup. Can I do it on Server C? I had given server C 98% priority in backup preferences – AG. Running below query on server C but it throwing 1 which means I can’t take backup on that server.
SELECT sys.fn_hadr_backup_is_preferred_replica (‘dbname’);
GO
Ashish – for questions unrelated to the contents of a blog post, your best bet is to head over to http://dba.stackexchange.com.
[…] reduce the amount of transaction log needed, you can either try to make your backup go faster, or take your backups during a time of reduced write activity, or […]
I am trying to understand how to fix a problem I have seen. If I have multiple drive letters on Windows for SQL. Eg.. E, D, F ,G. and I run a backup, it reads from one file per drive.
If I bundle all those drives into one bigger drive, the backup only chooses one file to read from into the backup.
All disk speeds are the same and backup write directory has capacity to go write faster (as proven by the different drives)
Is there a Flag or another setting to allow “X” more files to be read on a backup.
I have already done the buffer tuning and multiple BAK files on the destination.
Nothing online even mentions this and would really love a good answer as to why or how to tune that setting if possible.
Thanks
Hey Peter – that’s because SQL will generate one reader thread per logical disk, not database file. On the write side it will create one writer thread per backup device (file) though. So it is true that SQL will generate one reader thread per database file, but that’s as long as all the files live on different logical disks. I opened a Connect item for that…
Thankyou Argenis. So there is no override on that to force it to read more than one file per logical disk?
I am trying to prevent having multiple logical disks that can add more effort to managing a sql server.
Obviously on the storage array i have multiple disks so it has the capacity to go faster there. I definitely found the multiple backup files helped out a lot back a year ago
Was just hoping i wouldnt have to rebuild a huge database if i could avoid it
Peter,
Sorry for the bad news, but as of time of writing there is no knob for this behavior. My question to you is…is the single reader thread a problem for you? Typically you hit a bottleneck on the target device before the reading thread becomes one. That’s not to say that I haven’t seen this become an issue – I have, plenty of times.
Yeah – i can confirm we can handle more on the destination side as I have a different configuration on another database with the logical drives and same destination storage and see 3-4x the reads / writes with 4 drives seperated. This is very obvious when the backup times reduce by that amount. Appreciate your help on that. Shame there is no simple work around .