08 January 2007 13:43
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: SQL Server