Mini-Lab - Part 1 - Backup Compression

Published Mar 23 2019 11:46 AM 225 Views
Microsoft
First published on TECHNET on Dec 14, 2010

[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


Version history
Last update:
‎Mar 23 2019 11:46 AM
Updated by: