Compression Presentation Resources

I’ve been fortunate enough to give a presentation on Data Compression in SQL Server several times over past and coming months. I put this post together to showcase the slide deck, answer some questions, and list some resources around the feature.

Data Compression is an Enterprise Edition only feature, which was introduced in SQL Server 2008, starting with 2008 R2 Backup Compression was made available in all editions of SQL Server.

The SQLCAT blog about compression (and specifically to answer the question of there are benefits to using backup compression with a row and/or page compressed database–the answer is yes) is here.

Hugo Kornelius answers some questions around LOBs and why SQL Server doesn’t compress them at SQL Blog here.

My blog post on Columnstore Archival Compression in SQL 2014.

The full SQLCat white paper on Data Compression is here.

To answer the question about backup compression affecting the transaction log chain–basically, you can’t mix compression modes in a given media set. So if you are not using backup compression and you turn it on, you need to start backing up to a new media set (a new backup file). For example, the below code is correct:

backup database [AdventureWorksDW2012] to disk='c:\temp\1.bak'
go
backup log [AdventureWorksDW2012] to disk='c:\temp\2.bak'
with compression

backup log [AdventureWorksDW2012] to disk=’c:\temp\3.bak’

BACKUP LOG [AdventureWorksDW2012] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012_LogBackup_2013-05-09_14-22-10.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorksDW2012_LogBackup_2013-05-09_14-22-10′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [AdventureWorksDW2012] FROM DISK = N’c:\temp\1.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AdventureWorksDW2012] FROM DISK = N’c:\temp\2.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AdventureWorksDW2012] FROM DISK = N’c:\temp\3.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AdventureWorksDW2012] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012_LogBackup_2013-05-09_14-21-30.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5

GO

Also–the following rules apply around using compression in conjunction with partitioned tables:

When you use data compression with partitioned tables and indexes, be aware of the following considerations:

  • When partitions are split by using the ALTER PARTITION statement, both partitions inherit the data compression attribute of the original partition.
  • When two partitions are merged, the resultant partition inherits the data compression attribute of the destination partition.
  • To switch a partition, the data compression property of the partition must match the compression property of the table.
  • When a clustered index is dropped, the corresponding heap partitions retain their data compression setting unless the partitioning scheme is modified. If the partitioning scheme is changed, all partitions are rebuilt to an uncompressed state.

credit Books online

About Joseph Dantoni
A DBA, cyclist, cook (who likes to play chef occasionally)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,816 other followers

%d bloggers like this: