14

Have other people here played with SQL Server 2008 Compression at either the page or the row level on their datasets much? What have your impressions been on performance both speed and disk-space wise?

Has anyone ever seen compression demonstrably hurt performance?

On some of our huge fact tables we've been playing around and noticing that compression can make a hugely beneficial query speed difference on both tables and its indexes. It's also been saving a lot of disk space (~50% on some data). Our hardware setup is severely disk/io bound relative to the processor and compression so far seems like a trivially easy performance win for us.

Bob Albright
  • 2,242
  • 2
  • 25
  • 32

1 Answers1

13

Old question, but from experience, a simple rule of thumb is:

  • for non-BLOB data row-store page compression ratio is between 3 and 4 times
  • for non-BLOB data column-store compression ratio is between 9 and 11 times

Linchi Shea articles seem to be behind a login now....

Linchi Shea has posted some interesting articles on this topic:

This might also be of interest:

The SQL Server Storage Engine blog also has a few interesting posts on Compression.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Found link on Wayback: https://web.archive.org/web/20080518011433/http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-server-2008-page-compression-performance-impact-on-table-scans.aspx – Henrik Staun Poulsen Feb 10 '20 at 10:32