Monday, January 8, 2007 1:43 PM tonyrogerson

BACKUP DATABASE 5x quicker using BLOCKSIZE option

Got a very strange one on HP kick and its common across the 3 servers I have here.; might be useful for other kit - worth checking!

Windows Server 2003 x64 R2 running SQL 2005 Standard x64.

On building and progressing through a migration project for my client I noticed that the backup database was only reading and writing at just 10MBytes per second, this on a server that is more than capable of 50-180MBytes per second as measured through IOMeter.

Anyway, the fix - BLOCKSIZE=65536; the backup now runs at 50MBytes per second instead of just 10MBytes per second - not sure why; BOL mentions that if you don't use a BLOCKSIZE then it doesn't use buffered IO - when I've time I'll research it, but I thought a heads up on something that might make your backups go FIVE times quicker...

Perhaps its just me, but I've never come across the performance differential on the backup / restore before.

One of the big problems I'm going to need to overcome now is how do I incorporate this into log shipping and maintanence plans - there is no way for you to specify a BLOCKSIZE, perhaps I'll do a request for change on it.

From BOL...

BLOCKSIZE = { blocksize | @blocksize_variable }

Specifies the physical block size, in bytes. Generally, this option is not required, as BACKUP automatically selects a block size that is appropriate to the disk or tape device. Explicitly stating a block size overrides the automatic selection of block size. 65,536 (64 KB) is the maximum block size SQL Server supports.

This option can be helpful if you are taking a backup that you plan to store on CD-ROM. To transfer the resulting backup set to a CD-ROM, set BLOCKSIZE to 2048.

Note:
If you do not specify the physical block size, restore uses buffered I/O, which can be less efficient, especially for large backups.

 

 

Filed under:

Comments

# re: BACKUP DATABASE 5x quicker using BLOCKSIZE option

Wednesday, January 10, 2007 3:19 PM by Colin Leversuch-Roberts

I'll have a check on this or do you think this is only 64 bit related? I have specified blocksize before ( some years ago ), I'd try litespeed - far quicker and better!! I never use maint plans or ms log shipping - always roll my own, makes it very easy to handle this type of change.

# re: BACKUP DATABASE 5x quicker using BLOCKSIZE option

Monday, January 22, 2007 8:20 AM by StevenWhite

I got a slightly better time using this on sql2000 and 2005, but no improvment on a x64 server.

I wonder if it is more to do with aligning the blocksize to the disk blocksize. (After the standard diskpart commands have been run etc.)

Steven

# Entendendo e Melhorando a performance de seus backups | Vladimir M. B. Magalh??es – SQL Server DBA

Pingback from  Entendendo e Melhorando a performance de seus backups | Vladimir M. B. Magalh??es – SQL Server DBA