I am going through articles about table compression in SQL Server. To me it seems quite easy to setup compressed tables and the benefits make the decision no brainer.

I wanted to consult with this community and see if there is any gotcha that I need to know. I am concern that the table compression feature might not be as simple as it looks. Could there any unwanted side effect when I migrate my tables to compressed table?

  • Currently, data compression is an Enterprise-Only feature - just be aware that you will not be able to restore databases with compressed data to non-enterprise instances (if that matters), Nov 10 '16 at 10:17

In my experience there are a couple of things to consider:

  1. Sometimes, perhaps due to some maintenance task which doesn't respect compression settings, indexes and tables have compression turned off so carefully consider any routine tasks you have which rebuild indexes.
  2. Space saving can be significant
  3. When a query against a compressed table/index reads data from disk it was quicker (in my experience) than a query reading an uncompressed table due to the I/O limitations of disk.
  4. When the pages were already in the buffer pool, though, the query was slower than when compared to uncompressed data because of the additional CPU involved in uncompressing the data.

So consider your situation in regards to CPU, RAM and storage capacities. Don't forget to consider columnstore indexes which offer both good compression and query performance.

  • Also add on evaluating whether or not the compression will actually save you space in the table. Run sp_estimate_data_compression_savings to be sure that you aren't in fact making the table larger, or wasting CPU by not saving anything. Relevant sirsql.net/content/2010/06/08/…
    – Nic
    Nov 10 '16 at 15:13

The benefits of using table compression increase with the size of the table. If you have a small table the spend CPU time might be a larger penalty than the benefit of reduced I/O. Also you could be hit hard on performance if you have a small amount of memory for the buffer. Then pages will be loaded and uncompressed frequently with a penalty on CPU increased CPU usage. This will most likely show as low Page Lifetime Expectancy (PLE). In short - it depends...


One less well-known gotcha is that page compression can dramatically affect the performance of datatype change operations on the table. In my experience, changing a column from NULL to NOT NULL on an uncompressed table can be pretty fast but that change on a page compressed table can require 10X or more of the table's size to be written to the transaction log. In some cases I've needed to rebuild the compressed table as uncompressed, perform the datatype change, and to rebuild the table with compression again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.