In SQL Server, we can adjust high level settings to improve server performance. Similarly, we can
adjust settings in individual backup statements to improve the performance of backups themselves. A
backup tuning primer is well beyond the scope of this document; to learn about backup tuning,
please see the recording of our Backup Tuning class at http://bit.ly/1O6Rsh3 (download demo code at
http://bit.ly/1Os6yzz).
adjust settings in individual backup statements to improve the performance of backups themselves. A
backup tuning primer is well beyond the scope of this document; to learn about backup tuning,
please see the recording of our Backup Tuning class at http://bit.ly/1O6Rsh3 (download demo code at
http://bit.ly/1Os6yzz).
Introduction
Once you are familiar with the backup tuning process, you can perform an analysis, and then set up
specific thresholds in the Minion.BackupTuningThresholds table. It is a “Thresholds” table, because
you cannot tune a backup once and disregard database growth; backup tuning settings must change as
a database grows. So, Minion Backup allows you to configure a different collection of backup tuning
settings for different sized databases (thereby, defining backup tuning thresholds). As your
database grows and shrinks, Minion Backup will use the settings you’ve defined for those sizes, so
that backups always stay at peak performance.
Note: You can get more specific information about the Minion.BackupTuningThresholds table in the
“Minion.BackupTuningThresholds” section.
As a small example, here is a limited rowset for Minion.BackupTuningThresholds, which shows
different backup tuning settings for a single database at various sizes, and for two different
backup types:
DBName BackupType SpaceType ThresholdMeasure ThresholdValue NumberOfFiles Buffercount
MaxTransferSize
DB1 Full DataAndIndex GB 0 2 30 1048576
DB1 Full DataAndIndex GB 50 5 50 2097152
DB1 Diff DataAndIndex GB 0 2 30 1048576
DB1 Log Log GB 0 1 15 1048576
This sample data shows two threshold levels for DB1 full backups: one for databases larger than
50GB, and one for databases above 0GB. Note that the threshold value is a “floor” threshold: so, if
DB1 is 25GB, it will use the 0GB threshold settings; if it is 60GB, it will use the 0GB threshold
settings. The sample data also shows just one threshold level each for DB1 log backups and DB1
differential backups.
Of course, we could add additional rows for each type, for different size thresholds. This is what
puts the “dynamic” in “dynamic backup tuning”; Minion Backup will automatically change to the new
group of settings when your database passes the defined threshold.
specific thresholds in the Minion.BackupTuningThresholds table. It is a “Thresholds” table, because
you cannot tune a backup once and disregard database growth; backup tuning settings must change as
a database grows. So, Minion Backup allows you to configure a different collection of backup tuning
settings for different sized databases (thereby, defining backup tuning thresholds). As your
database grows and shrinks, Minion Backup will use the settings you’ve defined for those sizes, so
that backups always stay at peak performance.
Note: You can get more specific information about the Minion.BackupTuningThresholds table in the
“Minion.BackupTuningThresholds” section.
As a small example, here is a limited rowset for Minion.BackupTuningThresholds, which shows
different backup tuning settings for a single database at various sizes, and for two different
backup types:
DBName BackupType SpaceType ThresholdMeasure ThresholdValue NumberOfFiles Buffercount
MaxTransferSize
DB1 Full DataAndIndex GB 0 2 30 1048576
DB1 Full DataAndIndex GB 50 5 50 2097152
DB1 Diff DataAndIndex GB 0 2 30 1048576
DB1 Log Log GB 0 1 15 1048576
This sample data shows two threshold levels for DB1 full backups: one for databases larger than
50GB, and one for databases above 0GB. Note that the threshold value is a “floor” threshold: so, if
DB1 is 25GB, it will use the 0GB threshold settings; if it is 60GB, it will use the 0GB threshold
settings. The sample data also shows just one threshold level each for DB1 log backups and DB1
differential backups.
Of course, we could add additional rows for each type, for different size thresholds. This is what
puts the “dynamic” in “dynamic backup tuning”; Minion Backup will automatically change to the new
group of settings when your database passes the defined threshold.
Enabled by Default
Default backup tuning settings are in effect the moment that Minion Backup is installed: the system
comes installed with a default “MinionDefault” row in Minion.BackupTuningThresholds. These backup
tuning settings are used for any database which does not have a specific set of thresholds defined
for it; as well as for any database that has dynamic tuning disabled in Minion.BackupSettings.
While this last point may seem inconsistent – after all, why should a database refer to the
“MinionDefault” row in this table if dynamic tuning is disabled? – in fact, it makes perfect
sense:
comes installed with a default “MinionDefault” row in Minion.BackupTuningThresholds. These backup
tuning settings are used for any database which does not have a specific set of thresholds defined
for it; as well as for any database that has dynamic tuning disabled in Minion.BackupSettings.
While this last point may seem inconsistent – after all, why should a database refer to the
“MinionDefault” row in this table if dynamic tuning is disabled? – in fact, it makes perfect
sense:
- First, the default backup tuning settings cannot truly be said to be “dynamic”, as the dynamic aspect of backup tuning comes from having different settings for a database come into effect automatically as the database grows. The MinionDefault row in this table has a threshold size of 0GB, and so applies to databases of all sizes.
- Second, most of the settings in the MinionDefault row are “passive”: NumberOfFiles is 1, which is the case for any backup where number of files is not specified. And Buffercount, MaxTransferSize, and BlockSize are zero, meaning SQL Server is free to choose the appropriate value for these settings at the time the backup runs.
Essential Guidelines
There are three essential guidelines for setting dynamic backup tuning thresholds in Minion Backup:
- Any group of tuning thresholds – whether it is the MinionDefault group of settings, or a database-specific group of settings – must have one row with a “floor” setting of zero.
- Once you have defined a single database-specific row, all backup types for that database must be represented in one or more rows. (Note that each backup type must also, therefore, have a “floor” threshold of zero represented.) For more information about this rule, see “The Configuration Settings Hierarchy Rule” in the “Architecture Overview” section.
- However, if there is a hole in your backup tuning threshold settings, the MinionDefault row acts as a failsafe. It is best to define your backup tuning settings thoughtfully and with foresight; but the failsafe is there, just in case of oversights. (This failsafe is the exception to The Configuration Settings Hierarchy Rule; no other table can rely on the MinionDefault row in this way.)
Important Backup Tuning Concepts
Here is a quick review of important backup tuning threshold concepts in Minion Backup:
- Tune your own: The settings we use for these examples are just that: examples. They are not recommendations, and have no bearing on your particular environment. We DO NOT recommend using the example number in this document, without proper analysis of your particular system.
- Default Settings: Minion Backup is installed with a default backup tuning threshold setting, defined by the row DBName=’MinionDefault’, BackupType=’All’, and ThresholdValue=0. These settings are in effect for any database with DynamicTuning enabled in the Minion.BackupSettings.
- Space Types: You have the option of basing our tuning thresholds on data size only, on data and index size, or on file size. File size includes any unused space in the file; “data and index” does not.
- Available Data: Minion Backup is a huge help to your analysis, because it gathers and records the backup settings for EVERY backup (including Buffercount, MaxTransferSize, etc.) in Minion.BackupLogDetails, whether or not it was a tuned backup.
- Floor Thresholds: The thresholds in Minion.BackupTuningThresholds represent the LOWER threshold the “floor”). Therefore, you must be sure to enter a threshold for file size 0.
- Settings Precedence: Minion Backup has a hierarchy of settings, where the most specific setting takes precedence. See the “Backup Tuning Threshold Precedence” section below.
Backup Tuning Threshold Precedence
Minion Backup has a hierarchy of settings, where the most specific setting takes precedence. The
precedence for backup tuning threshold settings is as follows:
Precedence Level DBName Backuptype
Highest DB1 Full, or Diff, or Log
High DB1 All
Low MinionDefault Full, or Diff, or Log
Lowest MinionDefault All
Note: If you define a database-specific row, we highly recommend that you provide tuning settings
for all backup types, for that database. For example, if you insert one row for YourDatabase with
backup type Full, you should also insert a row for YourDatabase and backup type All (or two
additional rows, one each for differential and log).
Let’s look at an example set of backup tuning threshold settings:
ID DBName BackupType isActive
1 MinionDefault All 1
2 MinionDefault Full 1
3 MinionDefault Log 1
4 DB1 All 1
5 DB1 Full 1
Using these settings, let’s look at which settings will be used when:
precedence for backup tuning threshold settings is as follows:
Precedence Level DBName Backuptype
Highest DB1 Full, or Diff, or Log
High DB1 All
Low MinionDefault Full, or Diff, or Log
Lowest MinionDefault All
Note: If you define a database-specific row, we highly recommend that you provide tuning settings
for all backup types, for that database. For example, if you insert one row for YourDatabase with
backup type Full, you should also insert a row for YourDatabase and backup type All (or two
additional rows, one each for differential and log).
Let’s look at an example set of backup tuning threshold settings:
ID DBName BackupType isActive
1 MinionDefault All 1
2 MinionDefault Full 1
3 MinionDefault Log 1
4 DB1 All 1
5 DB1 Full 1
Using these settings, let’s look at which settings will be used when:
- For a DB1 full backup, Minion Backup will use row 5: DBName=DB1, BackupType=Full.
- For a DB1 differential or log backup, Minion Backup will use row 4: DBName=DB1, BackupType=All.
- For a DB2 full backup, Minion Backup will use row 2 (DBName=MinionDefault, BackupType=Full).
- For a DB2 differential backup, Minion Backup will use row 1 (DBName=MinionDefault, BackupType=All).
Note: If you are unsure of what backup tuning settings will be used, you can double check; use the
Minion.BackupStmtGet stored procedure, which will build (but not run) the backup statement for you.
For more information, see “Minion.BackupStmtGet”.
Business Aware Dynamic Backup Tuning
What’s more, Minion Backup’s dynamic backup tuning can be made “business aware”, in a sense. For
example, configure one set of tuning thresholds for weekday business hours, and another set for
after hours and weekends. Or, perhaps you need a different set of configurations for Monday,
because that’s the busiest day.
Here is a high-level overview of one way to set up “business aware” backup tuning scenarios:
1. Perform your backup tuning analysis, and determine the settings for two scenarios:
a. one low-resource scenario for times when the server is busy (say, weekdays); and
b. one high-resource scenario for when the server is largely unused (e.g., on the weekend).
2. Insert rows to Minion.BackupTuningThresholds for the low-resource scenario, and set
IsActive=1.
3. Insert additional rows to Minion.BackupTuningThresholds for the high-resource scenario, and
set IsActive=0.
4. Set up your backup routine with precode that checks the day of the week;
a. If the day is Saturday or Sunday, the precode sets isActive=1 in
Minion.BackupTuningThresholds for the high-resource scenario, and isActive=0 for the low-resource
scenario.
b. Otherwise, the precode enables the low-resource scenario, and disables the high-resource
scenario.
example, configure one set of tuning thresholds for weekday business hours, and another set for
after hours and weekends. Or, perhaps you need a different set of configurations for Monday,
because that’s the busiest day.
Here is a high-level overview of one way to set up “business aware” backup tuning scenarios:
1. Perform your backup tuning analysis, and determine the settings for two scenarios:
a. one low-resource scenario for times when the server is busy (say, weekdays); and
b. one high-resource scenario for when the server is largely unused (e.g., on the weekend).
2. Insert rows to Minion.BackupTuningThresholds for the low-resource scenario, and set
IsActive=1.
3. Insert additional rows to Minion.BackupTuningThresholds for the high-resource scenario, and
set IsActive=0.
4. Set up your backup routine with precode that checks the day of the week;
a. If the day is Saturday or Sunday, the precode sets isActive=1 in
Minion.BackupTuningThresholds for the high-resource scenario, and isActive=0 for the low-resource
scenario.
b. Otherwise, the precode enables the low-resource scenario, and disables the high-resource
scenario.
Tuning Log Backups
Log backups are interesting, because the size of the database doesn’t matter for a log file backup.
If your database is small, but a process has blown the log up to a huge size, the size of the data
file has no impact whatsoever on the log backup. You need to perform a backup tuning analysis for
log file backups, just like for any other backup type. After all, you wouldn’t want to back up a
5MB log file to 10 files!
Any time you have a row in Minion.BackupTuningThresholds with BackupType = 'Log', Minion Backup
will automatically use the space used in the log as the measure for “SpaceType”. So for example,
if you have a 100GB log file that is 10% used, the space used in the log file is 10GB; Minion
Backup uses this measure – the 10GB – to determine when the threshold should change.
Though the value of SpaceType does not change anything in regards to log backups, we still
recommend you set SpaceType equal to “Log” whenever the BackupType = 'Log', because it is a visual
reminder of how the threshold is calculated.
This feature is meant to keep a huge log from taking hours to process, while other logs are filling
up (because they can''t back up yet because of the big one). So, keep a safety net for yourself,
and put in a couple tuning options for your logs. If they grow really big, the payoff of tuned log
backups is considerable; well-tuned log backups take a fraction of the time they ordinarily would.
Note: The backup tuning thresholds feature does not shrink the log file. To shrink the log file,
see the three “ShrinkLog%” columns in the Minion.BackupSettings table. These two features –
Dynamic Backup Tuning and Shrink Log on Log Backup – work very well together to keep your system
running without intervention from you. (You’re welcome!) For more information on shrinking the log,
see “How to: Shrink log files after log backup”.
If your database is small, but a process has blown the log up to a huge size, the size of the data
file has no impact whatsoever on the log backup. You need to perform a backup tuning analysis for
log file backups, just like for any other backup type. After all, you wouldn’t want to back up a
5MB log file to 10 files!
Any time you have a row in Minion.BackupTuningThresholds with BackupType = 'Log', Minion Backup
will automatically use the space used in the log as the measure for “SpaceType”. So for example,
if you have a 100GB log file that is 10% used, the space used in the log file is 10GB; Minion
Backup uses this measure – the 10GB – to determine when the threshold should change.
Though the value of SpaceType does not change anything in regards to log backups, we still
recommend you set SpaceType equal to “Log” whenever the BackupType = 'Log', because it is a visual
reminder of how the threshold is calculated.
This feature is meant to keep a huge log from taking hours to process, while other logs are filling
up (because they can''t back up yet because of the big one). So, keep a safety net for yourself,
and put in a couple tuning options for your logs. If they grow really big, the payoff of tuned log
backups is considerable; well-tuned log backups take a fraction of the time they ordinarily would.
Note: The backup tuning thresholds feature does not shrink the log file. To shrink the log file,
see the three “ShrinkLog%” columns in the Minion.BackupSettings table. These two features –
Dynamic Backup Tuning and Shrink Log on Log Backup – work very well together to keep your system
running without intervention from you. (You’re welcome!) For more information on shrinking the log,
see “How to: Shrink log files after log backup”.