7

In order to improve performance of our SQL Server backups we backup to multiple backup files.

  • 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.

Improve this question

2 Answers 2

Reset to default
4

Lets address your questions first ...

is there a point where you can have too many files that instead of improving performance you get a worst performance due to the big amount of threads waiting?

The number of backup threads depends upon the number of logical volumes used for the databases files, and the number of backup devices. SQL Server supports a maximum of 64 backup devices for a single backup operation.

So when you are striping your backups, you are increasing the throughput. This is a good move.

Is there any general recommendation based on, for example, number of cores in the server?

An example of balanced files and disks for good backup performance from the backup optimization whitepaper (caution : its a word doc).

enter image description here

Things to consider :

  • Enable Instant file initialization.
  • Use backup compression (sql server 2008 R2 and up has backup compression in standard edition). Compression will increase CPU usage, so make sure you do not have CPU saturation.
  • Change / adjust your backup strategy. Instead of doing daily full backup of a multi Terabyte database, think of doing differential along with log backups.
  • In a test environment, play with BUFFERCOUNT (total number of I/O buffers) and MAXTRANSFERSIZE (largest unit of transfer in bytes between sql server & backup media) backup parameters along with trace flags 3605 & 3213.
  • Refer to my answer SQL Backup tuning large databases

Friendly advise

I noticed server has only 4 cores and 32 GBs of RAM.

Since you are running a big DB (several TBs), 4 cores and 32 GB RAM sounds very low. Bump up your hardware.

Refer :

Improve this answer
9
  • 1
    This table's data looks obsolete. 2 CPU cores per data file is certainly not balanced on todays systems. In general that statement cannot be true, either, because it depends on disk and CPU speed. The numbers in the table feel so wrong that I think it's misleading.
    – usr
    Jan 3, 2016 at 11:10
  • Also depends on backup compression. Without it a single core can probably back up 2GB/sec. These numbers vary, therefore, by an order of magnitude.
    – usr
    Jan 3, 2016 at 11:11
  • @usr the table data is just a general recommendation as per the whitepaper (referencing sql server 2008). They are not plain wrong and should be used as a good starting point. The table says that a good starting point should be e.g. for a 2 core system, there should be 2 data files and 2 backup files... what is wrong with that ? Backup can be made faster tickling with buffercount and maxtransfersize and striping them.
    – Kin Shah
    Jan 3, 2016 at 14:29
  • As a general recommendation these numbers are terrible because the true numbers can deviate by an order of magnitude. For what's wrong with that I'll refer to my answer which explains why such general numbers cannot exist.
    – usr
    Jan 3, 2016 at 16:01
  • I think it's a much more productive approach to understand the underlying rules and why certain numbers are good. That way you can often predict just by back of the envelope calculations what the right settings are going to be. Alternatively, just try some numbers and measure. But copying numbers from some other completely different system without understanding the fundamentals is quite unreliable.
    – usr
    Jan 3, 2016 at 16:05
4

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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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