Sql-server – Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

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.