Why/when should I choose Row level compression over Page compression?

  • VoldemarG

    Hall of Fame

    Points: 3670

    In what table design/scenarios should I prefer a Row level compression and not Page level?

    Like if we were to name one major advantage of one over another, and possibly one disadvantage of each compared to another, what would it be?

    In this amazingly helpful in many ways article I see that Update% and Scan/Select estimations as well as actual estimated space gains are the decisive factors:

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd894051(v=sql.100)

    HOWEVER, if we really are not concerned about a slight difference (between Row and Page compression) in space saving after table is compressed but also want to ensure that further Read and Write operations to this table will perform at least not worse than before compression then what type should I choose ? (to minimize risk of possible issues with performance after compression).

    Also, a sub-question: 

    after I compress  a 500-million-row table,  how can I un-compress it /rollback?  Are there any pitfalls involved in rolling back?

    ( I am doing a detailed POC on applying compression to several largest tables in our environment, those tables are huge and critical, and I want to make sure I am well prepared for the unexpected).  This will be the statement that I ll use to compress: (or do you suggest to do individual indexes instead of entire table? if yes then why would I want to do that?).

    ALTER TABLE My2TBTable_1  REBUILD PARTITION = ALL  WITH (DATA_COMPRESSION = PAGE);

    THANK YOU!

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14684

    That link contains enough information to guide you on which one to chose - other than that it is TEST TEST TEST and pick the one that performs better for your workload.

    to remove compression - rebuild all again without the compression clause. do note that in both cases log file will GROW a lot and so will data file.

    compress both DATA and Indexes - the command you gave only compresses the table (and its clustered index if it has one as it is the same).

    on my shop the majority of critical databases are page compressed (exception of CRM as not supported by the vendor - but if it had it it would perform a lot better)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    Have you run the estimate procedure?

    I haven't seen many people saw they do row without page, but I'm sure some have done this. What I'd really do is not compress your table as a POC, but make a copy of it temporarily, including all indexes. Storage is relatively cheap here, and you don't want to get into trouble. Then compress the copy with row, do a live comparison with the original and the repeat with page. That might help you understand  for your data, what happens. You can then drop these quickly without uncompressing them (truncate the tables, then drop).

    As Frederico mentioned, the log will grow, so check that. Be sure you compare table+indexes.

  • VoldemarG

    Hall of Fame

    Points: 3670

    How else can I compress DATA?  You mean COMPRESS function?

    or What do you mean by this:

    <<Compess both DATA and Indexes - the command you gave only compresses the table (and its clustered index if it has one as it is the same).>>

    Voldemar likes to play CHESS (and IS good at it!)

  • coolchaitu

    SSChampion

    Points: 10276

    Row compression is a good choice for OLTP data, high updates. Page compression is good choice for data having low updates.

  • Jeff Moden

    SSC Guru

    Points: 996619

    VoldemarG wrote:

    In what table design/scenarios should I prefer a Row level compression and not Page level?

    Like if we were to name one major advantage of one over another, and possibly one disadvantage of each compared to another, what would it be?

    In this amazingly helpful in many ways article I see that Update% and Scan/Select estimations as well as actual estimated space gains are the decisive factors:

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd894051(v=sql.100)

    HOWEVER, if we really are not concerned about a slight difference (between Row and Page compression) in space saving after table is compressed but also want to ensure that further Read and Write operations to this table will perform at least not worse than before compression then what type should I choose ? (to minimize risk of possible issues with performance after compression).

    Also, a sub-question: 

    after I compress  a 500-million-row table,  how can I un-compress it /rollback?  Are there any pitfalls involved in rolling back?

    ( I am doing a detailed POC on applying compression to several largest tables in our environment, those tables are huge and critical, and I want to make sure I am well prepared for the unexpected).  This will be the statement that I ll use to compress: (or do you suggest to do individual indexes instead of entire table? if yes then why would I want to do that?).

    ALTER TABLE My2TBTable_1  REBUILD PARTITION = ALL  WITH (DATA_COMPRESSION = PAGE);

    THANK YOU!

    I ran the code to calculate the "U" value from the article you linked to.  I've not done that before.  The results were nothing like I expected.  The totally static tables that have suffered no updates for up to a decade were all calculated with a "U" percentage of 99.9% and the most actively updated tables were calculated as 0.0%.  Something is definitely wrong with that code.  I'm not sure I'd rely on it even if you subtracted the percentages from 100 to (IMHO) invert the percents to be what the writeup says.

    Be careful.

    p.s. I should know better by now than to trust such code in any Microsoft article.  Sheesh!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    VoldemarG wrote:

    How else can I compress DATA?  You mean COMPRESS function?

    or What do you mean by this:

    <<Compess both DATA and Indexes - the command you gave only compresses the table (and its clustered index if it has one as it is the same).>>

    If you have a clustered index, the table is the index. IF you have non clustered indexes, they are not compressed when you compress the table. You need to compress them separately, and it's a good idea as sometimes you have more space used by indexes than by the data in the table.

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

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