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.

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.

share|improve this question
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 Oct 19 '11 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 Oct 19 '11 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 Oct 19 '11 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 Oct 19 '11 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 Oct 19 '11 at 13:44
add comment

migrated from stackoverflow.com Oct 19 '11 at 22:50

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
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 Oct 20 '11 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. –  Mark Storey-Smith Oct 20 '11 at 15:02
    
Brilliant, thanks Mark. –  LeedsDBA Oct 20 '11 at 16:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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