[Next Post in Series]

In previous blog posts we have touched briefly on some of the new features that were introduced in SQL Server 2008 but if you're like me you need to get your hands dirty and work with them before you really understand and see the value.

BACKUP COMPRESSION

Backup Compression which was introduced in SQL Server 2008 can significantly reduce the size of you database backups as well as the total duration to complete the backup.  Some obvious questions:

  • How much space will I save
  • How much time will I save

Both have an answer that I"m sure most of you are suspecting... It Depends.  The size and type of data that you're storing, whether or not you're compressing your tables, and if you're database is encrypted all play a factor in determining how much space you can save .  Here is a query that will look at your full backups over the previous week and show you compression ratio between the Backup Size (in MB)  and the Compressed Backup Size (in MB)

SELECT Database_name,
  CONVERT(int,Backup_Size/1048576) as 'BackupSizeMB',
  CONVERT(int, Compressed_Backup_Size/1048576) as 'CompressedBackupSizeMB',
  (Backup_Size/1048576)/(Compressed_Backup_Size/1048576) as 'CompressionRatio'
FROM msdb.dbo.backupset
WHERE type = 'D' AND
  backup_finish_date > GETDATE() - 7
ORDER BY Database_name, backup_finish_date DESC 

OK let's see Backup Compression in action.  We're going to create a database called 2008FeaturesDB.

USE [master]
GO
CREATE DATABASE [2008FeaturesDB] ON  PRIMARY
( NAME = N'2008FeaturesDB',
 FILENAME = N'<<DataDrive>>\2008FeaturesDB.mdf' ,
 SIZE = 1GB ,
 MAXSIZE = 500GB,
 FILEGROWTH = 1GB )
 LOG ON
( NAME = N'2008FeaturesDB_log',
 FILENAME = N'<<LogDrive>>\2008FeaturesDB_log.ldf' ,
 SIZE = 1GB ,
 MAXSIZE = 500GB ,
 FILEGROWTH = 1GB );
GO

Now we're going to create a table called Customers and load it with 2 million rows of dummy data.  Please be advised this may take a few minutes depending on the test server you are running this on.

-- Create a test table to dump data into

CREATE TABLE Customers(
 CustomerID INT IDENTITY,
 FirstName VARCHAR(100),
 LastName VARCHAR(100),
 Birthdate DATE,
 Comments VARCHAR(MAX));

ALTER TABLE dbo.Customers ADD CONSTRAINT
 PK_Customers PRIMARY KEY CLUSTERED
 (
 CustomerID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

 
-- Load our test table
SET NOCOUNT ON

INSERT INTO Customers VALUES (
REPLICATE(NEWID(),2), REPLICATE(NEWID(),2), CONVERT(DATE, GETDATE() - ((18 * 365) + RAND()*(62*365))), REPLICATE(NEWID(), RAND()* 100))
INSERT INTO Customers VALUES (
REPLICATE(NEWID(),1), REPLICATE(NEWID(),1), CONVERT(DATE, GETDATE() - ((18 * 365) + RAND()*(62*365))), REPLICATE(NEWID(), RAND()* 10))
GO 1000000

Now we're going to compare backing up the database without compression vs. with compression

-- BACKUP DATABASE WITHOUT COMPRESSION
BACKUP DATABASE [2008FeaturesDB]
TO  DISK = N'<<BackupPath>>\2008FeaturesDB.bak'
WITH NOFORMAT, INIT,  NAME = N'2008FeaturesDB-Full Database Backup w/o Compression',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- BACKUP DATABASE WITH COMPRESSION
BACKUP DATABASE [2008FeaturesDB]
TO  DISK = N'<<BackupPath>>\2008FeaturesDB_Compressed.bak'
WITH NOFORMAT, INIT, COMPRESSION,  NAME = N'2008FeaturesDB-Full Database Backup w/Compression',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

COMPARISON CHART

  DATABASE NAME DATABASE SIZE BACKUP SIZE DURATION
WITHOUT COMPRESSION 2008FeaturesDB 12.28 GB 2.54 GB 76 seconds
WITH COMPRESSION 2008FeaturesDB 12.28 GB 0.22 GB 34 seconds

So we have a much smaller total backup size AND it took close to half the time to complete so it seems like a no-brainer as to whether or not you should use this all the time right?

 WRONG... Once again an answer of "It Depends" applies.

There is a significant CPU hit when using Backup Compression.  (In order to highlight this point I set my affinity mask to 1)

DATABASE BACKUP WITHOUT COMPRESSION

DATABASE BACKUP WITH COMPRESSION

As with most features there is a time and place to use Backup Compression.  Using the query provided in this blog post you can assess the benefit of using backup compression on your databases and determine from there whether or not the space (and  time) savings are worth the additional CPU load.

In Part 2 of this Mini-Lab (Backup Compression with Resource Governor) we will be looking at utilizing the Resource Govenor to minimize the CPU hit of using Backup Compression.

For More Information on Backup Compression please read the SQL Server 2008 Data and Backup Compression Whitepaper

_____________________________________________________________________________________________

 Follow Tier1OnSQL on Twitter