Compressing Data during Backup

SQL Server 2008 introduced backup compression feature - a big relief for those environments that never seem to have enough space.

As you can imagine, compressing the data results in smaller backup file resulting in less device I/O, which increases the backup speed significantly. However, the compression process will increase the CPU usage many fold, which may impact other resources running on the server. You can limit CPU usage by using the resource governor. We will discuss how to limit CPU usage in future post.

You can also determine the I/O performance of your backup by using Windows performance counters. SQL Server Objects provide two I/O performance counters that you can use

1. The Device Throughout Bytes/sec - This counter is part of the SQLServerBackup Device Object.
2. Backup / Restore Throughput / sec - This counter is part of the SQLServerDatabases object.

We recently upgraded from SQL 2005 to 2008 and the first thing I enabled was backup compression. After enabling the compression, I conducted a dry-run on a 350 GB database. When the backup process started, SQL Server created a backup file of about 124 GB, but once the backup completed, the file was shrunk to 69 GB. Based on my understanding, I knew the compression happens during the backup process and not afterwards - in other words the data is compressed while it is being backed up and not compressed after the file has been written on the file system. After further research and finally getting in touch with a SQL MVP, we found the real cause.

When SQL Server starts a compressed backup process, it has no idea about how much data will be compressed (compression ratio depends on the type of the data stored in the database), so it makes an educated guess (roughly1/3rd of the size of the data being backed up) and pre-allocates space on the storage media to write the compressed file. Almost always the compressed file will be smaller than the allocated space (if it is larger, the allocated size will obviously extend), hence the actual file size is generally smaller. Imagine SQL Server allocating small space and then having to re-extend during the backup process.

I learned another tidbit about this pre-allocation process. Apparently you can disable this process by turning on Trace Flag 3042. Be sure to turn it off after the backup has completed. 

      

         DBCC TRACECON(3042, -1)  -- turn on

         DBCC TRACEOFF (3042, -1) -- turn off

Obviously turning off pre-allocation comes at a performance cost because now the space must be dynamically allocated during the backup operation, but it comes in handy when you don't have enough space on the storage drive.

See Microsoft KB article for more information - http://support.microsoft.com/kb/2001026

Backup compression is off by default when you install SQL Server. You can turn it on either by using sp_configure stored procedure or via management studio.

Via Management Studio

Right click on your SQL instance > Properties > Database Settings

Alternatively, you can run sp_configure

USE master;
GO
EXEC sp_configure ‘backup compression default’, '1';
RECONFIGURE WITH OVERRIDE;

To override default for just one database or just one backup, you can use WITH COMPRESSION in your backup statement.

USE MASTER
GO
BACKUP DATABASE AdventureWorks
TO DISK='E:\AdventureWorks2008R2.bak'
WITH COMPRESSION;
GO

To determine the compression ratio of a backup, you can check the backup history in Master DB.

USE MASTER
GO
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
GO

Thank you.