SQL Server – Recommended Number of Backup Devices for Performance

backupsql server

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.

Best Answer

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 :

Related Question