CTP6 - Backup compression

The Enterprise Edition of SQL 2008 now offers the ability to compress database backups. If you use the GUI for the backup, the compression option is the last choice on the Options page

Once there you'll have 3 choices; Use the default server setting (set to 0 after installation, use sp_configure to change it), Compress backup, and Do not compress backup. If you use a script, just add the COMPRESSION keyword:

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\ADWorks.bak'
WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

I tested backing up the AdventureWorks sample database (always a good idea if you're going to play with it). The uncompressed file size is roughly 175 MB; the compressed is about 40.25 MB. There's not much free space in the database, less that 1 MB, and the size of the database matches the uncompressed size, which is what I expected.

What I didn't expect is that the compressed backup appears to have run faster than the uncompressed backup. When testing I ran each backup 3 times and I only used the default settings (other than the compression option). Both backed up to the same disk drive and nothing else was running at the time that would have affected the backup.

Here's a sample output from the uncompressed backup:

BACKUP DATABASE [AdventureWorks] TO DISK = N'c:\ADWorks_Uncompressed.bak'
WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Processed 21864 pages for database 'AdventureWorks', file 'AdventureWorks_Data'
on file 1.
100 percent processed.
Processed 1 pages for database 'AdventureWorks', file 'AdventureWorks_Log'
on file 1.
BACKUP DATABASE successfully processed 21865 pages in 20.349 seconds (8.394 MB/sec).

And here's a sample from the compressed version:

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\ADWorks.bak'
WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Processed 21864 pages for database 'AdventureWorks', file 'AdventureWorks_Data'
on file 1.
100 percent processed.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks_Log'
on file 1.

BACKUP DATABASE successfully processed 21866 pages in 13.003 seconds (13.137MB/sec).

But then I remembered Idera's SQLSafe program. I ran the evaluation version a while back. I thought the speed increase there was due to some trick of Idera's. That's not really the case. Read this from the CTP's BOL:

Performance Impact of Compressing Backups
Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

That makes sense. It takes less time to write a smaller file than a larger file. So I was looking in the wrong area. I should have been checking the CPU counters.