Post reply

If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION

  • March 6, 2013 at 7:37 am

    #1593962

    Yep, 'test it and see' indeed.

    If you have lots of half-empty Char(100) columns, I guess row-compression may speed things up even comparing memory access speeds.

    My tests were using an efficient design where 'every byte counts', so compression would have a harder time to prove its worth.

    Once we move the transactional system to 2008, I'll test that one for compression gains (it will still fit in memory uncompressed, but the schema is inefficient so maybe compression will be worth it).

    SQL Server matters are rarely cut'n'dry eh...

    Cheers,

    JohnA

    MCM: SQL2008

  • March 11, 2013 at 5:59 am

    #1595493

    GilaMonster (3/6/2013)

    SQLCharger (3/6/2013)

    My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.

    I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be 10 - 20% worse.

    Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.

    If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.:cool:

    That depends entirely on the workload and on the type of compression. Also you have the effect of reducing memory footprint thereby reducing IO load (more can fit into memory), reducing the CPU impact of moving data around, etc.

    Page compression's overhead can be unacceptable, row compression often isn't, when there's an overhead at all.

    Take a look at the whitepaper on compression, the performance results are at the bottom.

    http://msdn.microsoft.com/en-us/library/dd894051.aspx

    I've seen other benchmarks where even OLTP apps got performance improvements from compressing at row or page level.

    Pretty much it's a case of 'test it and see', unless you're running a vendor app where that testing's been done.

    Gail,

    Thomas Kejser has just posted some relevant metrics on his excellent blog:

    http://blog.kejser.org/2013/03/11/quantifying-the-cost-of-compression/

    Cheers,

    JohnA

    MCM: SQL2008

Viewing 2 posts - 16 through 16 (of 16 total)

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