If you perform a...

BACKUP DATABASE [DB_Name] TO DISK=N'c:\backups\db_name.bak' WITH STATS=10

Will that perform a single or multi-threaded write?

Our Server support company advised us that if we were to multi-thread our backups we would get better disk write performance. They advised us that SQL Backup is multi-threaded, but i think the above statement would be single-threaded. If we specify multiple DISKS to write to, then it will stripe the backup across multiple devices and therefore be multi-threaded by my understanding.

If I perform the following T-SQL while the backup is processing, i can see that the SPID performing the backup has multiple processes, but only one seems to write to disk, the others i presume are reading and doing other things. It's just the write threads i'm referring the question to.


FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_requests r ON p.spid = r.session_id
WHERE spid=(spid of backup T-SQL)

Thanks in advance.

  • 1
    I'd expect the number of physical drives to have nothing to do with how the processing burden (which is quite minor compared to the disk I/O time) is handled.
    – bdares
    Commented Oct 19, 2011 at 13:23
  • Sorry when i say multi/single-threaded... i mean the number of data streams to the devices. So BACKUP to a single DISK='' would that be just one sequential data stream to the disk (backup file)? If that's the case would using DISK='...',DISK='...' in the backup statement cause multiple streams? MSDN states "Copying the data and transaction log from the backup devices to the database and transaction log files is performed by reader/writer threads; one thread is assigned to each backup device" But because the support company said that SQL Server Backup is multi-threaded that's thrown me!
    – LeedsDBA
    Commented Oct 19, 2011 at 13:28
  • I'm pretty sure the answer to this is that it's single-threaded with just one DISK='...', but when using multiple DISK='...' it's multi-threaded as per msdn... but i wanted someone else to confirm. TIA
    – LeedsDBA
    Commented Oct 19, 2011 at 13:36
  • I'd expect that specifying more than one drive would indeed stripe the backup and make it go faster. A better solution would be to have a striped RAID (such as RAID 6) to back up to, if speed is such a concern. You wouldn't have to worry about the software supporting it, either.
    – bdares
    Commented Oct 19, 2011 at 13:38
  • Thanks for your comment bdares2. We use a 3Par - 100 disk RAID array (yes you read that right!) all SATA disks for our backup...the speed we are seeing for our backups is around 24 MB/sec... the engineer tells me that this is because we will only use one disk for a single-threaded application (which i believe is wrong, as the RAID controller would handle the striping, not the application). However, I do agree with him that we would better utilise the bandwidth by using multiple streams.
    – LeedsDBA
    Commented Oct 19, 2011 at 13:44

1 Answer 1


Backup to multiple files is perhaps what your adviser is referring too. You'll often see improvements (although usually modest) if you're backing up to multiple files on a single disk/array, up to the point that you're saturating source and destination IO paths obviously.

  DISK = N'Z:\backup\MyDatabase_File1.bak'
  , DISK = N'Z:\backup\MyDatabase_File2.bak'
  , DISK = N'Z:\backup\MyDatabase_File3.bak'
  , DISK = N'Z:\backup\MyDatabase_File4.bak'
    NAME = N'MyDatabase - Full Backup'
    , INIT
    , STATS = 10

Compression is the obvious addition if you're using 2008+. If not, there are gains to be had from experimenting with BUFFERCOUNT, BLOCKSIZE and MAXTRANSFERSIZE option. Paul Randall's article on Advanced Backup & Restore Options is a good place to start.

  • Hi Mark, thanks for your response. So just using one disk would be single-threaded then by that theory? We're using 2005 and currently use a well known third-party compression tool (without mentioning the product!) which is under-performing. Actually our backups are taking longer than they are when not using the compression! Thanks for the link, I will certainly take a look at that article.
    – LeedsDBA
    Commented Oct 20, 2011 at 14:58
  • Yes, 1 file/device = 1 writer thread. Multiple file/device (be that multiple .bak files as per my example or multiple tapes) will have a writer thread per file/device. Commented Oct 20, 2011 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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