Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

marked as duplicate by Jon Seigel, Paul White, Martin Smith, Mark Storey-Smith, ypercubeᵀᴹ Nov 6 '12 at 18:57

This question was marked as an exact duplicate of an existing question.

up vote 0 down vote accepted

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.

share|improve this answer
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.