1

I'm struggling to find out the answer, we currently use compression on all our data warehouse tables, which has improved performance and also greatly reduced the storage space used.

I've only recently realised, that we aren't compressing any of our non-clustered indexes. Should we also be doing this? Are there any gotchas that we should be aware of?

Improve this question
1

1 Answer 1

Reset to default
1

I've only recently realised, that we aren't compressing any of our non-clustered indexes. Should we also be doing this? Are there any gotchas that we should be aware of?

The main question here is, do you want to save space & memory usage at the cost of more cpu cycles? This is something we will not be able to answer for you.

Since you are able to compress the clustered indexes, no other restrictions noted in Considerations for When You Use Row and Page Compression are possible limitations.

You could always run the sp_estimate_data_compression_savings procedure to calculate the gains you could get from compressing the nonclustered indexes. See here for a solution for every table in your data warehouse.

Speaking of data warehouses, you could also take a look at (clustered / nonclustered) columnstore indexes and see if they are a fit for your (fact) tables.

Improve this answer
3
  • Can you elaborate on the unique constraint gotcha? AFAIK, there are no special concerns as one can specify data compression for unique constraints and change the option for existing unique constraints with ALTER INDEX.
    – Dan Guzman
    Sep 12, 2019 at 9:36
  • @DanGuzman you are right, it is not a concern. Guess I executed some unneeded changes in the past when compressing tables. Thanks a lot for pointing that out
    – Randi Vertongen
    Sep 12, 2019 at 9:58
  • Thanks for clarifying. This is essentially how I thought it would behave, but wasn't sure. We've had compression turned on with all our data warehouse tables for quite some time. Whilst there is in theory a performance hit on the CPU side of things for decompressing the file, since making the move to compressing all tables, we've seen an improvement in the overall speed of things, obviously due to quicker disk reads, smaller memory foot print, etc. I'm going to start applying to same logic to our non clustered indexes, as we don't currently have any cpu constraints.
    – Chris
    Sep 12, 2019 at 22:58

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.