33

I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of testing, and I am fully aware that any truly valuable answer will begin with "Well, it depends...". My concerns about the testing that I have done and the testing shown in any of the resources I have found (see way below) is that testing is done in a vacuum, most likely on a system with no other load.

I am curious about proper guidance / best-practices regarding these three options that are based upon long-term experience: many data points over weeks or months. And I am not looking for specific values since that is mostly a function of the available hardware, but I would like to know:

  • How various hardware / load factors influence what should be done.
  • Are there circumstances in which none of these values should be overridden?
  • Are there pitfalls for overriding any of these that are not immediately obvious? Using up too much memory and/or disk I/O? Complicating restore operations?
  • If I have a server with multiple Instances of SQL Server running (a Default Instance and two Named Instances), and if I run the backups of all 3 Instances concurrently, does that affect how I set these values beyond making sure that the collective (BUFFERCOUNT * MAXTRANSFERSIZE) does not exceed available RAM? Possible I/O contention?
  • In that same scenario of having the three Instances on one server, and again running the backups across all three concurrently, how would also running the backups for multiple Databases concurrently within each Instance affect the setting of these values? Meaning, if each of the three Instances has 100 Databases each, running 2 or 3 backups per each Instance concurrently such that there are between 6 and 9 backups running concurrently. (In this situation, I have many small to medium databases rather than a few large ones.)

What I have gathered so far:

  • BLOCKSIZE:

    • The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. [1]
    • The default is 65536 for tape devices and 512 otherwise [1]
    • If you are taking a backup that you plan to copy onto and restore from a CD-ROM, specify BLOCKSIZE=2048 [1]
    • When you write to single disks, the default of 512 is just fine; if you use RAID arrays or SAN, you must test to see whether the default or 65536 is better. [13 (page 18)]
    • If setting manually, the value needs to be >= the Block Size used to create the data file(s), else you will get the following error:

      Msg 3272, Level 16, State 0, Line 3
      The 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\BackupTest.bak' device has a hardware sector size of 4096, but the block size parameter specifies an incompatible override value of 512. Reissue the statement using a compatible block size.

  • BUFFERCOUNT:

    • Default [2], [8]:

      SQL Server 2005 and later versions:
      (NumberofBackupDevices * [mystery_multiplier]) + NumberofBackupDevices + (2 * NumberofVolumesInvolved)

    • [mystery_multiplier]: There is some inconsistency regarding this value. I have seen it expressed in 3 forms:

      • 3 [2]
      • GetSuggestedIoDepth [8]
      • GetSuggestedIoDepth + 1 [8]


      Testing that shows the multiplier to be 3 was done on SQL Server 2005 SP2 [9].

      My testing on SQL Server 2008 R2 and 2012, and a user comment regarding SQL Server 2014 [8], show the multiplier to be 4. Meaning, given the reported value for GetSuggestedIoDepth (directly below), either:

      • GetSuggestedIoDepth is now 4, or
      • the multiplier is now GetSuggestedIoDepth + 1
    • GetSuggestedIoDepth returns 3 for DISK devices [9]
    • No hard-set max value, but given that memory required = (BUFFERCOUNT * MAXTRANSFERSIZE), it would seem that a practical max value would be: BUFFERCOUNT <= (available_memory / MAXTRANSFERSIZE)
  • MAXTRANSFERSIZE:
    • The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB). [1]
    • Default values: If device is in read mode(restore) or this is a Desktop or Express Edition use 64K, else use 1 MB. [9]
  • General / Miscellaneous:
    • The max size that can be used is the (Buffer Pool’s To Physical Memory / 16). As returned from the GlobalMemoryStatusEx (ullTotalPhys) API call. [9]
    • Trace Flag 3213 outputs backup/restore configuration parameters while performing backup/restore operations, and 3605 dumps output to the ERRORLOG file: DBCC TRACEON (3213, 3605, -1);
    • You can use DISK = N'NUL:' (DOS / Windows equivalent of /dev/null in UNIX) for easier testing of some metrics (but won't get a good sense of total process time since it is skipping the write I/O)

Resources

  1. MSDN page for T-SQL BACKUP command
  2. KB904804: You experience slow performance when you back up the database in SQL Server 2000
  3. Options to Improve SQL Server Backup Performance
  4. Backup and Restore
  5. Optimizing SQL Server Backup and Restore
  6. Optimizing Backup Performance
  7. How to increase SQL Database Full Backup speed using compression and Solid State Disks
  8. Incorrect BufferCount data transfer option can lead to OOM condition
  9. How It Works: How does SQL Server Backup and Restore select transfer sizes
  10. How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)
  11. SQL Backup tuning large databases
  12. SQL Server Memory for Backup Buffer
  13. A Case Study: Fast and Reliable Backup and Restore of a VLDB over the Network (.docx file)
  14. How many backup devices is recommended to improve backup performance?

I tested with:

--DBCC TRACEON (3213, 3605, -1);

BACKUP DATABASE [Test] TO
      DISK =  'NUL:'
     --,DISK = 'NUL:'
     -- DISK =  'BackupTest1.bak'
     -- ,DISK =  'BackupTest2.bak'
WITH
    STATS = 5,
    FORMAT,
    CHECKSUM,
    NO_COMPRESSION,
    COPY_ONLY
    --,BUFFERCOUNT = 40
    --,MAXTRANSFERSIZE = 4194304--2097152,
    --,BLOCKSIZE = 16384 

--DBCC TRACEOFF (3213, 3605, -1);

UPDATE

It seems that I sometimes forget to add some of the info that I am always asking others to provide when I am answering a Question ;-). I did give some info above regarding my current situation, but I can provide more detail:

I am working for a client that provides a 24/7/365.25 SaaS application. So there is potential for users to be on at any point, but realistically, the users are all U.S.A.-based (for now) and tend to work mostly "standard" hours: 7 AM Pacific (i.e. 10 AM Eastern) to 7 PM Pacific (i.e. 10 PM Eastern), but 7 days a week, not just Monday - Friday, though weekend load is a bit lighter.

They are set up such that each client has their own DB. It's a niche industry so there are not tens of thousands (or more) of potential clients. The number of client DBs varies per Instance, with the largest Instance holding 206 clients. The largest DB is approx. 8 GBs, but only about 30 DBs are over 1 GB. Hence, I am not specifically trying to maximize performance of a VLDB.

When I started with this client, their backups were always FULL, once per day, and no LOG backups. They had also set MAXTRANSFERSIZE to 4 MB and BUFFERCOUNT to 50. I replaced that setup with a slightly customized version of Ola Hallengren's database backup script. The slightly customized part is that it gets run from a multi-threading tool (that I wrote and will hopefully start selling soon) that dynamically discovers DBs as it connects to each Instance, and allows for throttling per Instance (hence I am currently running the three Instances concurrently, but the DBs per each instance sequentially since I wasn't sure of the ramifications of running them concurrently).

The setup is to now do a FULL backup one day per week and DIFF backups on the other days; LOG backups are taken every 10 minutes. I am using the default values for the 3 options I am inquiring about here. But, knowing how they had been set, I wanted to make sure that I was not undoing an optimization (just because there were some major flaws in the old system doesn't mean that everything was wrong). Currently, for the 206 databases, it takes about 62 minutes for FULL backups (once a week) and between 7 and 20 minutes for DIFF backups on the remaining days (7 on the first day after the FULL, and 20 on the last day before the next FULL). And that is running them sequentially (single thread). The LOG backup process, in total (all DBs on all 3 Instances), takes anywhere from 50 to 90 seconds each time (again, every 10 minutes).

I realize that I can run multiple files per DB, but a) I am not sure how much better that will be given the multithreading and the small to medium size of the DBs, and b) I am not wanting to complicate the restore process (there are various reasons why dealing with a single file is preferred).

I also realize that I could enable compression (my test query has it intentionally disabled), and I had recommended that to the team, but it was brought to my attention that the built-in compression is kinda sucky. Part of the old process was to compress each file into a RAR, and I did my own testing and found that yes, the RAR version is at least 50% smaller than the natively compressed version. I did try using native compression first to speed things up and then RAR the files, but those files, while smaller than those merely natively compressed, were still a bit larger than the RAR-only compressed version, and by enough of a difference to justify not using native compression. The process to compress the backups is asynchronous and runs every X minutes. If it finds a .bak or .trn file, it compresses it. This way, the backup process is not slowed down by the time it takes to compress each file.

  • 1
    Just curious, are you trying to address a slow backup problem ? Normally, the defaults just work fine in most of the environments. Also, is power option set to high performance - since taking backup uses CPU cycles. – Kin Shah Feb 5 '16 at 20:16
  • 2
    @Kin No, the backups are not specifically slow. But, if making a minor change would/could make them 20% (or more) faster, then I would certainly take it. For 206 databases, it takes about 62 minutes for FULL backups (once a week) and between 7 and 20 minutes for DIFF backups on the remaining days. And that is running them sequentially (single thread). When I started with this client, the prior setup was to use 4 MB for MaxTransfer and 50 for BufferCount. At present I am just using the defaults, so unsure if I undid a performance gain, so wanted to learn more before making any changes. – Solomon Rutzky Feb 5 '16 at 20:33
  • @srutzky just a quick point from your last comment, I saved considerable time breaking up my backups into multiple files going to the same volume. I just wanted to share that with you just in case it wasn't something you tried yet. If your 206 DBs run a backup in parallel across multiple DBs though you might not get the multi-threading benefits. – Ali Razeghi Feb 5 '16 at 20:42
  • 2
    @MaxVernon "The virtual device interface (VDI) backups allow 3rd party back-up solutions to integrate with the SQL Server. " that was taken from Resource #10 in my Question :). I was not wanting to go through that much effort ;-) – Solomon Rutzky Feb 5 '16 at 21:50
  • 1
    @srutzky in case you wanna have some fun: read MSSQL Backups - check HBA maximum transfer size - the guy is brilliant and really thorough in his tests. And something that probably matches your tests: SirSQL's Automated Backup Tuning. – Marian Feb 6 '16 at 23:27
12

You've addressed a boatload of items in your question. Thanks for being so thorough!

Just a couple of things I notice off hand:

  • How various hardware / load factors influence what should be done.

Are you running a 24x7 instance? What is the load around the clock? I notice you have backup compression disabled; is that by design for the test, or is it desirable for some reason to have it turned off when you put this into production? If you have tons of hardware headroom (CPU/RAM), and completing the backup in the shortest amount of time is of paramount importance, then you'd want to tune these parameters for the particular hardware you have with that goal in mind. If you want to ensure OLTP workloads are serviced around the clock, and don't want backup to impact that, you'll likely need to tune these parameters the other way around. You haven't identified your design goals since you are asking for general guidance however as you so wisely state "it depends™".

  • Are there circumstances in which none of these values should be overridden?

You'd want to retain the default settings if you were concerned about supportability down the road after you no longer maintain the instance, and are uncertain about the abilities of your replacement. You'd likely want to leave the defaults in place unless you have a specific need to tune them. Let sleeping dogs lie, as they say.

  • Are there pitfalls for overriding any of these that are not immediately obvious? Using up too much memory and/or disk I/O? Complicating restore operations?

As the documents you reference clearly state, upping these parameters too much can certainly have negative impacts on uptime. As with all things production-based, you need to test this thoroughly before deploying it, and leave the settings alone unless absolutely necessary.

  • If I have a server with multiple instances of SQL Server running (a Default Instance and two Named Instances), and if I run the backups of all 3 Instancs concurrently, does that affect how I set these values beyond making sure that the collective (BUFFERCOUNT * MAXTRANSFERSIZE) does not exceed available RAM? Possible I/O contention?

You'll want to ensure you leave plenty of RAM for unforeseen circumstances. I would certainly be concerned about using more than 60% or 70% of available ram for backup operations unless I knew with 100% certainty that nothing else was ever going to be happening during the backup window.

I've written a blog-post with some code that shows how I do backup performance testing, at SQLServerScience.com


this may not be the best answer I've ever written, but as The Great One™ once said, "you miss 100% of the shots you don't take"

  • 2
    Thanks for those pointers, Max. +1 for that :). I did just add an UPDATE section to my already not-short Question to address a few comments on the Question and your question here about why I am not using compression. I believe I also answered your question about how I am running the backups :-). – Solomon Rutzky Feb 5 '16 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.