Mini-Lab - Part 1 - Backup Compression
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
_____________________________________________________________________________________________