Some Notes on SQL Server 2008 Backup compression

I blogged about backup compression here, and I mentioned that the main advantages are saving space, reduced backup and restore time. After discussion with some fellow MVPs, I have to revise my statement in previous post.

Yes, backup compression saves backup and restore time only if there are enough CPU and IO resources available. This is obvious because IO is the biggest bottleneck when reading/writing backup file. Thanks to Aaron and Andrew for remind me about this fact.

However, IO bottleneck is solvable if we use fast removable storage (eSATA for example) instead of internal hard disk. With this way, we can take full advantage of compression without affecting current machine performance.

The SQL Customer Advisory team did a very comprehensive benchmark on this. Below is some screen shoot I cut and paste from their blog post:

sqlcatcompress1

sqlcatcompress2

The complete post for benchmark by SQLCAT is here

http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

Leave a Reply