I'm using the NUL output so backups are not written to disk.
1st test : Default Compression settings >
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION
Processed 1222736 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 506 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223242 pages in 160.234 seconds (59.641 MB/sec).
2nd test : Up Buffercount to 50 >
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 50
Processed 1222768 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 259 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223027 pages in 107.928 seconds (88.530 MB/sec).
3rd test : Up Buffercount to 150
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 150
Processed 1222800 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 167 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1222967 pages in 98.587 seconds (96.913 MB/sec).
4th test : Up Buffercount to 250 >
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 250
Processed 1222832 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 263 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223095 pages in 96.788 seconds (98.725 MB/sec).
Miniscule Improvement (2s) on my system between BufferCount of 150 and 250
Links :
http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx
http://blogs.msdn.com/sqlcat/archive/2008/03/02/backup-more-than-1gb-per-second-using-sql2008-backup-compression.aspx
No comments:
Post a Comment