patternsqlMinor
How many backup devices is recommended to improve backup performance?
Viewed 0 times
howrecommendedimprovedevicesperformancemanybackup
Problem
In order to improve performance of our SQL Server backups we backup to multiple backup files.
files but, is there a point where you can have too many files that
instead of improving performance you get a worse performance due to
the big amount of threads waiting?
cores in the server? To be more specific, one of our server shows
We're backing up a big DB (several TBs) using 10 backup files, but I noticed server has only 4 cores and 32 GBs of RAM. I have changed the number of backup files to use 4 backup files instead. I'll see how it goes next week in the next backup cycle, but meanwhile I was trying to find any recommendations on how many backup files to use depending on the server specifications.
- On most blog posts about this topic they say to use multiple backup
files but, is there a point where you can have too many files that
instead of improving performance you get a worse performance due to
the big amount of threads waiting?
- Is there any general recommendation based on, for example, number of
cores in the server? To be more specific, one of our server shows
BACKUPIO and BACKUPBUFFER as top 2 offenders wait stats.We're backing up a big DB (several TBs) using 10 backup files, but I noticed server has only 4 cores and 32 GBs of RAM. I have changed the number of backup files to use 4 backup files instead. I'll see how it goes next week in the next backup cycle, but meanwhile I was trying to find any recommendations on how many backup files to use depending on the server specifications.
Solution
In order to improve performance of our SQL Server backups we backup to multiple backup files
Not generally, no. That is a special purpose technique that can help.
big amount of threads waiting
Why would that cause a loss of throughput? Threads are normally an ample resource. Problems arise in certain configurations where you burn through hundreds and into the thousands of threads.
based on [the] number of cores in the server
That would make sense if the backup operation was CPU bound. That could be the case. As long as it is IO bound it cannot help to target the number of cores with any setting.
one of our server shows BACKUPIO and BACKUPBUFFER as top 2 offenders wait stats
That points to an IO bound backup process. According to my experience that is far more common than CPU boundedness.
a big DB (several TBs)
The size of the backup does not play a role in throughput considerations. Throughput is performance per unit of time.
There mere presence of waiting threads does not normally affect other threads and operations on the server. Waiting threads do nothing.
so I have changed the number of backup files to use 4 backup files instead
That was unfounded because the backup is IO bound. Targeting the number of cores is not a good target.
If you added 128 CPU cores, your backup would not become one second faster.
You might have increased performance but only accidentally.
How many backup files to use is influenced by many things. An easy way to find a good number is to simply test different values, including 1 (the most likely value for common systems).
Not generally, no. That is a special purpose technique that can help.
big amount of threads waiting
Why would that cause a loss of throughput? Threads are normally an ample resource. Problems arise in certain configurations where you burn through hundreds and into the thousands of threads.
based on [the] number of cores in the server
That would make sense if the backup operation was CPU bound. That could be the case. As long as it is IO bound it cannot help to target the number of cores with any setting.
one of our server shows BACKUPIO and BACKUPBUFFER as top 2 offenders wait stats
That points to an IO bound backup process. According to my experience that is far more common than CPU boundedness.
a big DB (several TBs)
The size of the backup does not play a role in throughput considerations. Throughput is performance per unit of time.
There mere presence of waiting threads does not normally affect other threads and operations on the server. Waiting threads do nothing.
so I have changed the number of backup files to use 4 backup files instead
That was unfounded because the backup is IO bound. Targeting the number of cores is not a good target.
If you added 128 CPU cores, your backup would not become one second faster.
You might have increased performance but only accidentally.
How many backup files to use is influenced by many things. An easy way to find a good number is to simply test different values, including 1 (the most likely value for common systems).
Context
StackExchange Database Administrators Q#125038, answer score: 4
Revisions (0)
No revisions yet.