Greater Than Many
This post is rather short, because it’s more of a link round-up than anything.
I think that index compression is so generally useful that I’d start off any new system with it enabled, just to avoid issues with needing to apply it later. Where it’s particularly useful is on systems where data is plenty, and memory is scarce.
Having index structures that are much smaller both on disk and in memory is quite useful. It’s also nice when you’re on Standard Edition, and you need to make the most of out the 128GB cap on the buffer pool.
For some great information and detail on index compression, check out My Friend Andy™ who has very helpful blog posts about it.
- Data Compression: How row compression works
- Data Compression: How page compression works
- Data Compression: Costs & benefits
And More
There are some obvious bits here, like being extra careful with choosing string length.
LOB data can lead to weird locking, and mess with memory grants.
And of course, overly-wide, non-normalized tables can also lead to issues.
If you’re running an OLTP workload, you may also want to make sure that your critical tables aren’t heaps.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.