patternsqlModerate
Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command
Viewed 0 times
buffercountmaxtransfersizeblocksizesettingforandcommandbackup
Problem
I am looking for practical guidance for setting the values for the
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:
What I have gathe
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 gathe
Solution
You've addressed a boatload of items in your question. Thanks for being so thorough!
Just a couple of things I notice off hand:
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™".
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.
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.
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"
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"
Context
StackExchange Database Administrators Q#128437, answer score: 14
Revisions (0)
No revisions yet.