Question on compression for very large tables in sql server
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 (&quot;decompress&quot;). 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.