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.

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.