Can many concurrent SELECTs slow down DB BACKUP on same DB?

Question

Hello, can many concurrent SELECTS on the same DB that's running a BACKUP job slow down this job?

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

Welcome to Microsoft Q&A!
Yes. In addition, there are many things you can do to improve the performance of your backups.

• The SQL 2008 Database backup compression feature (introduced as Enterprise Edition only feature in SQL 2008) proves to save quite some amount of disk space and improves backup performance. This technique also helps in speeding up the restore process that can be extremely helpful for reducing downtime in a disaster recovery situation. However, the catch here is more CPU utilization and maximum available IO bandwidth use.
• Take care, not to use the same drive for storing the database files. Reading from and writing to the same drive takes significantly more time than 2 separate drives. In addition to contention issues, any damage to the sole drive could mean you lose the database and the backup.
• Since extra CPU and IO bandwidth are needed for the process, and you’ll preferably need other drives for storing the backup, using Solid State Drive units is advisable. It will also further give the performance a boost.
• Using advanced parameters like BLOCKSIZE, MAXTRANSFERSIZE, BUFFERCOUNT etc also adds massive gains.
• Instruct the BACKUP command to keep deleting old backup files of the same database and of the same type as part of the whole operation. Using ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE options with the BACKUP command can achieve this.
• To identify the files to delete, the headers of all the files in the backup folder are read. The larger the number of files stored in the folder, the longer it takes to read all of the file headers. Hence, to reduce the number of file headers to be read each time, store backups in folders according to database name and backup type. Including the <DATABASE> and <TYPE> tags in folder paths can achieve this.
• Another helpful step towards saving backup time is selecting to stop deleting backup and restore history that’s older than <n Days | Hours> (available from Tools > Server Options > File Management). Usually this option is selected and causes the stored procedure msdb..sp_delete_backuphistory to delete history from the msdb database that contains a lot of history thus adding to the overall backup time. But if you clear this option and perform the delete at some other convenient time manually, you can save quite a bit.
  For more information, please refer to this article: https://www.stellarinfo.com/blog/speedup-sql-server-backup-performance/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

What for a question.
Sure, every opertion requires some CPU and I/O workload and can of cause slow down other opertions, also a backup.

Answer 3

Thanks for all of the replies.