locked
At row or page level? RRS feed

  • Question

  • Hi all of you,

    Under what circumstances can you apply compression at row or page level?

    is it tight on how is built the schema of the table?

    is it about to the number of rows?

    using some ntext columns would require apply at row level?

    What does happen if your table is partitioned?

    At row level only suitable for clustered table?

    I understand if you have a HEAP table best strategy would be compression at page level because when you look data you are using Table Full Scan, "visiting" all the pages allocated.

    If you have one balanced tree witn non-clustered indexes

    Sorry for be a pain with that, the book I am currently using (70-461) does not give info about that and it's of paramount importance in huge OLTP scenarios.

    I've tested this table with a couple of million rows and the Compression Wizard suggested me at page level

    This table is a balanced tree



    • Edited by Enric Vives Saturday, March 26, 2016 5:13 PM
    Saturday, March 26, 2016 5:09 PM

Answers

  • As currently done SQL Server compression saves only a modest amount of space and is barely worth the effort, IMHO.

    Unless you do a test and find an unusually high level of compression on your specific data, I'd hardly worry about it.  Well, except for some certificate test, of course.

    Generally the page level will offer better compression.

    But it really ought to be at the table level.

    And/or the pages should be larger.

    Then compression would work well enough to be worth the bother.

    Josh

    • Marked as answer by Enric Vives Saturday, March 26, 2016 10:31 PM
    Saturday, March 26, 2016 5:46 PM

All replies

  • As currently done SQL Server compression saves only a modest amount of space and is barely worth the effort, IMHO.

    Unless you do a test and find an unusually high level of compression on your specific data, I'd hardly worry about it.  Well, except for some certificate test, of course.

    Generally the page level will offer better compression.

    But it really ought to be at the table level.

    And/or the pages should be larger.

    Then compression would work well enough to be worth the bother.

    Josh

    • Marked as answer by Enric Vives Saturday, March 26, 2016 10:31 PM
    Saturday, March 26, 2016 5:46 PM
  • Thanks for your reply, Josh

    Saturday, March 26, 2016 5:51 PM
  • Compression savings depends a lot on the schema and data specifics.  Execute sp_estimate_data_compression_savings to estimate how much your data should compress.

    I compressed a large table and saw about a 70% compression ratio, saving over 3TB of storage.  Generally, IO bound DSS workloads with large databases will benefit most from compression.  Performance may be better or worse with compression depending on your CPU, memory, storage and workload. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, March 26, 2016 6:29 PM
  • In addition to Dan's post: row compression often pays off well enough to be worth it, and overhead is small. Row compression is basically just a different storage format. Page compression saves more space but has more overhead, and is normally not suitable for table that are updated frequently.
    Keep in mind that compression is only available in Enterprise Edition.

    Saturday, March 26, 2016 6:42 PM