Row Compression vs. Page compression

Question

Row Compression vs. Page compression

SuperDBA-207096

SSCrazy Eights

Points: 8176
More actions
September 2, 2008 at 7:39 am

#192139

Ok - trying to get a better understanding of when it's best to use Row compression vs. Page compression?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

richard_Lees SSC Enthusiast Points: 142 More actions · Answer 1

Compression has the advantage of reducing storage requirements at the cost of increasing processor consumption. By using both row and page compression, you will reduce the storage required for tables, with the main benefit of reducing IO demand (and increasing density of data in cache).

Whether you should use row, page or no compression depends on how IO and processor bound your system is. If you are extremely IO bound, you will probably find that maximum compression (page) will suit you. If you are extremely processor bound, you will probably find that no compression will suit you. Somewhere inbetween is row compression.

Having said that, I am finding that both row and page compression are very effective in reducing my IO demands with an almost neglegible processor cost. This is particularly appropriate for data warehousing environments, or tables that are not getting continually updated.

What you need to do is run a test determining how much compression you get from row and page compression. Look at the additional cost of insert and update and determine if it is worthwhile.

Hope that helps,

Richard

http://RichardLees.com.au

jjarupan Ten Centuries Points: 1247 More actions · Answer 2

Free ebook from RedGate : Brads Sure Guide to SQL server 2008 has detail and some examples about this topic. It may be good if you take a look.

Note: It show link on the upper part of email from SQLservercentral.com. It has only ~113 pages, so you can read and test all topics within 1 day.

Jerry Hung SSChampion Points: 12968 More actions · Answer 3

Note it's ONLY AVAILABLE in the Enterprise edition

so I mostly don't even worry about it as it's too pricey

But in short

ROW: replace fixed-length column data types with variable-length (so char into varchar)

PAGE: does ROW first

then look for similar patterns to replace (prefix/dictionary), similar to Zip/text compression taught in school (you look for common prefixes, replace prefix with 1-suffix to save space)

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 4

Jerry,

Thanks! thats pretty much what I was looking for..

Mark

Kevin Farlee Old Hand Points: 304 More actions · Answer 5

The other part of the story is that it also depends on the datatypes you're dealing with.

Row compression takes all numeric types and makes the physical storage variable length.

So, storing the value 1 takes less space than storing the value 39482084. For large fact tables that are mostly/all numeric, this is a huge win. For purely text tables, you'd get nothing.

Page compression does a dictionary lookup within the scope of a page to find common prefixes to strings on the page. This doesn't do much to help numeric data, but can be a huge win for text data.

Page compression actually is a superset, in that it automatically includes row compression.

Kevin Farlee
SQL Server Storage Engine PM

sky_night SSC Enthusiast Points: 145 More actions · Answer 6

I know this is an old thread but I found it useful and just wanted to add a quick note. One other thing to keep in mind is the following:

1.) Page compression takes twice as long to perform

2.) Page compression takes up more disk space (so you need more available disk space to perform this operation). After running the operation you will be able to shrink the data files (as the data is now compressed) but you will need that additional space to perform the operation.

-Paul N.