Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

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
add comment

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