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?
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.
ALTER INDEX.
– Dan Guzman
Sep 12 at 9:36
