Is SQL Server Backup single or multi-threaded?

Question

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.

USE MASTER
GO
SELECT *
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.

• bdares: 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.
• LeedsDBA: 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: 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
• bdares: 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.
• LeedsDBA: 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.

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.

BACKUP DATABASE [MyDatabase] 
TO 
 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'
WITH 
 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.

• LeedsDBA: 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.
• Mark Storey-Smith: 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.