Steve Malley (3/6/2013)

---

I should have mentioned in my initial post that I was speaking of a Primary Key *Clustered* constraint. This creates a corresponding index.

GilaMonster (3/6/2013)

---

Why?

SQLCharger (3/6/2013)

---

My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.

I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be 10 - 20% worse.

Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.

If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.

GilaMonster (3/6/2013)

---

That depends entirely on the workload and on the type of compression. Also you have the effect of reducing memory footprint thereby reducing IO load (more can fit into memory), reducing the CPU impact of moving data around, etc.

Page compression's overhead can be unacceptable, row compression often isn't, when there's an overhead at all.
Take a look at the whitepaper on compression, the performance results are at the bottom.
http://msdn.microsoft.com/en-us/library/dd894051.aspx

I've seen other benchmarks where even OLTP apps got performance improvements from compressing at row or page level.

Pretty much it's a case of 'test it and see', unless you're running a vendor app where that testing's been done.