I have a confusion on how this compression feature works in SQL server:
For quite some large tables , ones with over TB, we recently implemented PAGE level compression.
Example of how it was done:-
Use [DBname]
ALTER TABLE dbo.sampletable REBUILD PARTITION =1 WITH (DATA_COMPRESSION = PAGE);
ALTER TABLE dbo.sampletable REBUILD PARTITION =2 WITH (DATA_COMPRESSION = PAGE);
and so on…
The tables in question were all partitioned table.
When checking the details of compression i see at table level ( Clustered index ones) it shows compression =PAGE but for existing non clustered indexes , compression says = NONE
Queries i have used are from here and here
I am not sure what should i interpret from above if compression is enabled at table level or needs to be enabled at index level as well?
Is there space and performance benefit in case we should also consider compressing NC indexes as well? Basically what would be my ideal case here to go with compressing them as well?
Thanks
Best Answer
It is impossible for us to answer whether you want to compress your indexes or not. Only you have access to your data, server and queries. Considerations includes:
Page compression is pretty expensive to crack ("decompress"). Pages lives in memory in compressed form, meaning that every time a page is accessed (including in memory), it has to be cracked. You can probably imagine that pages in an index can be navigated (cracked) very frequently.
Continuing on above reasoning. If the data tend to live in cache (even in uncompressed form), then you will notice more of the CPU overhead. But if the data tend to live on disk, then the CPU overhead might drown compared to the potential I/O savings.
People tend to discard row compression as an option. In some cases it can give a pretty decent compression ratio, and it has close to none overhead.