Menu
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.
550
Page-level compression compresses data by storing repeating values and common prefixes only once and then making references to those values from other locations within the table. When page compression is applied to a table, row compression techniques are also applied.
In short though, page compression encompasses the algorithms contained with row level compression and then also covers Prefix compression and Dictionary compression. Prefix and dictionary compression finds patterns in the data and replaces them with smaller values.
Though Page Level compression is superior (save more space), compression depends on the type of data you stored in the table, data duplication and it's HEAP or non HEAP. Non HEAP table gives better compression. A table has three types of allocation unit IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA.
There are three forms of data compression you can use with SQL Server: row-level compression, unicode compression, and page-level compression. To learn more about heaps, see Heaps (Tables without Clustered Indexes) in the MSDN library.
Old question, but from experience, a simple rule of thumb is:
Linchi Shea articles seem to be behind a login now....
Linchi Shea has posted some interesting articles on this topic:
SQL Server 2008 Page Compression: Compression ratios with real-world databases
SQL Server 2008 Data Compression: Impact of Data Distribution
SQL Server 2008 Page Compression: Performance impact on table scans
SQL Server 2008 Page Compression: Performance impact on inserts
SQL Server 2008 Page Compression: Using multiple processors
This might also be of interest:
The SQL Server Storage Engine blog also has a few interesting posts on Compression.
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
