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.