Row Compression vs. Page compression

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

  • 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

  • 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.

  • 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
  • Jerry,

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

    Mark

  • 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

  • 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.

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

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