0

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

2
  • The compression setting of a table is not automatically applied to its nonclustered indexes. Each index must be set individually. – Brian Nov 24 '20 at 19:02
  • I see. But my preceding question is how beneficial is that? Is that recommended ? does it make any sense for all the non clustered indexes in those partitioned tables – BeginnerDBA Nov 24 '20 at 19:06
1

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.

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.