Question: We’ve just upgraded to SQL Server 2008 R2 Standard Edition and we’re looking forward to saving backup space using the native backup compression feature. I’ve heard that I should never enable it at the instance level. Can you explain why?
Answer: The SQL community was really pleased when Microsoft listened to everyone’s feedback and made backup compression available in Standard Edition of SQL Server 2008 R2 (it was only available in Enterprise Edition in SQL Server 2008).
I like backup compression not just for the space savings, but for the time savings during backup and restore operations. During a restore, the more compressed the backup is, the shorter the restore time (generally) and so the more precious downtime is reduced.
The catch is in the phrase ‘the more compressed the backup is’. Just because the backup compression algorithm ran, it doesn’t guarantee that any compression was able to be done of the backup data. The compression algorithm used is proprietary to Microsoft, but like most common compression algorithms, the amount of compression achieved depends on what is being compressed.
For instance, mostly a database containing nearly empty data file pages might compress well, but a database that has transparent data encryption enabled (and so is a stream of small, random integers) will not compress at all. There’s no telling how much compression will be achieved without trying backup compression for each database.
The downside of trying to compress uncompressible data is that the CPU cycles are still used to run the compression algorithm, even when the compression achieved is zero.
For this reason, it can be detrimental to enable backup compression by default at the instance level without first checking which backups are not worth trying to compress. What the threshold is for ‘not worth it’ is going to vary based on achievable space savings, how desirable it is to save space in a company, and how CPU-bound the SQL Server instance is that will be performing the backups.
If you find that the majority of the backups on the instance compress enough for your needs, it can be easier to enable backup compression at the instance level and then specify WITH NO_COMPRESSION for those few backups you do not want to waste CPU cycles trying to compress. And of course the opposite is true – you can leave backup compression disabled at the instance level and specify WITH COMPRESSION for those backups you do want to compress.
To summarize, what you’ve been told is incorrect. It’s perfectly feasible to enable backup compression at the instance level – you just need to make sure that you take account of each database’s compressibility appropriately.
Learn more from Michael K. Campbell's "Don't Forget about Backup Compression."