0

Possible Duplicate:
Is SQL Server Backup single or multi-threaded?

Does SQL Server uses a separate writer thread for each backup device (backup file) or separate thread is used only when you stripe your backup across multiple backup volume (drive letter, LUN or mount point)?

for e.g. do we get three write thread here or

backup database dbName to DISK = 'D:\file1.bak',
                      DISK = 'D:\file2.bak',
                      DISK = 'D:\file3.bak'

OR the following speeds up the backup because you are writing to different volume

backup database dbName to DISK = 'D:\file1.bak',
                      DISK = 'E:\file2.bak',
                      DISK = 'F:\file3.bak'

In above each drive letter is a separate IO path. I understand IO throughput is also very important for backup speed.

Improve this question
0
0

Yes, I tested this with a 2TB database. The backup time dropped from 5 hours to roughly 4 hours 15 minutes after breaking it up into 4 files and putting them in the same drive. Another advantage is you could back them up to separate drives if your drives IOPS is your bottleneck.

Also, I believe Paul Randall goes through this in his "Microsoft Certified Master Backup and Restore internals" modules.

/edit: Furthermore, I've been testing something similar with RoboCopy's /MT command (Multi-Threading) and we get similar performance gains doing multi threaded copies of backup files. During our next backup, I'll test to see if I see multiple SPIDs as well.

Improve this answer
2
  • Does SQL Server uses a separate writer thread for each backup file (assuming you are backing up to multiple files) even you only have one drive to backup. – SQL Learner Nov 1 '12 at 19:44
  • Yes, it will do so when you write out to multiple files. The CPU thread scheduler doesn't care if the files are on the same drive or not, but it does care that you are writing mulitple files thus it'll use a thread for each. – Ali Razeghi Nov 1 '12 at 21:46

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