Friday, June 7, 2013

SQL Server backup compression

Some notes about SQL Server backup compression, introduced in SQL Server 2008.
1.  Compressed backups of noncompressed, row compressed, and page compressed tables and indexes can all be expected to be smaller than noncompressed backups of the same data.
2.  Without trace flag 3042, mentioned in this article, compressed backups use a final size file estimate for the initial file size.  This is done to avoid wait time during the backup for file expansion. Since this may be larger than the needed backup file size on completion, the in-process file size may be larger than the final file size.  This may be an important consideration when sizing backup resources.  With trace flag 3042 in place, compressed backups may take longer, but the files only grow as needed.

3.  Io is greatly reduced for compressed backups, leading to higher CPU utilization during a shorter execution time just from compacting the activity time window.  The compression itself also adds cpu overhead.  Not a reason NOT to employ backup compression: if CPU becomes a concern, it can be regulated with resource governor.

No comments:

Post a Comment