Data Compression

Melissa Connors discusses compression options and gives examples of data which will compress and that which will not:

Page Compression is what I like to refer to as “compression for real this time” as it goes well beyond the smart storage method of row and uses patterns/repeating values to condense the stored data.

First, to gain a better understanding of this method, check out a simple representation of a page of data. This is illustrated below in Figure 1. You’ll notice that there are some repeating values (e.g. SQLR) and some repeated strings of characters (e.g. SSSLL).

I really appreciate getting an idea of what kind of data does not compress well.  You’d think auto-incrementing numbers would be another scenario, but Melissa explains how that’s not necessarily the case.

Related Posts

Looking At Compressed Pages

Jess Pomfret shows us what compressed data looks like in SQL Server: We first need to switch on trace flag 3604: this will write the output of our DBCC PAGE command to the messages tab instead of the event log. There are 4 parameters for DBCC PAGE: we will need to pass in the database name (or id), the […]

Read More

What Happens With Data Compression + Backup Compression

Jess Pomfret tests what happens when you enable backup compression for databases with already-compressed tables in SQL Server: What happens if I use data compression and backup compression, do I get double compression? This is a great question, and without diving too deeply into how backup compression works I’m going to do a simple experiment […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031