How many backup devices is recommended to improve backup performance?
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).
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).
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) andMAXTRANSFERSIZE(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 :
- Optimizing Backup and Restore Performance in SQL Server
- How does SQL Server Backup and Restore select transfer sizes ?
- How to increase SQL Database Full Backup speed using compression and Solid State Disks ?