0

*I am asking this on SO not SF because I am a developer and am interested in the data access performance aspects of this not the Administration aspect.

I've been trying to do some research / learning into the underpinnings of TABLE | INDEX ... ROW | PAGE Compression. There is a wealth of information about how to implement these features and I am aware of the basic concepts that while you are using slightly more CPU it is negligible v I/O saved. However I am failing to find a very detailed explanation of when this should be utilized when it is not appropriate to utilize and when to use page v row. Even in several books I have read on performance tuning a database architecture ( they seem to just go on about how great it is and then gloss over the internal underpinnings).

Even this SQLCAT article on MSDN (while the most in depth I have found) doesn't really seem to do the topic justice. I have some rough ideas, as in a heavy OLTP application with allot of updates and inserts the CPU penalty may weigh more heavily against the gains in I/Os.

If anyone can provide me with a good explanation or point me in the direction of some detailed literature I would greatly appreciate it.

Thanks in Advance

5
  • 3
    Benchmark for your particular situation. Aug 3, 2011 at 2:14
  • 1
    Whether you're a developer or an administrator doesn't decide where the question belongs. The question does.
    – Don Roby
    Aug 3, 2011 at 2:19
  • 1
    @Mitch I agree with this however I think knowing when to use something in the first place save you allot of time benchmarking things that need not be bench marked. You wouldn't try to put a aftermarket honda spoiler on a Ferrari, yet alone spend time putting it on a dyno to tell you its slower then it was in the first place. Aug 3, 2011 at 2:26
  • "save you allot of time benchmarking things that need not be bench marked" - almost everything needs to be benchmarked. If you don't, how do you know when there's a problem? Aug 3, 2011 at 2:35
  • Like I said I agree, however having the foresight to make the right design choice initially = less and more efficient benchmarking. Anyhow this seems to be a topic that is not that well understood. Aug 3, 2011 at 2:39

1 Answer 1

6

The theory goes that Page and Row compression helps if your database issues significant data IO. Well tuned OLTP applications fit the entire database in memory and only need to write the log for write-ahead-logging and to flush dirty pages on checkpoint (note that in typical OLTP a page is dirtied many many times before is flushed) therefore OLTP applications may see degradation from compression. This puts compression in the DW/OLAP camp, and compression benefits increase with the compression ratio (some data is more compressible than other).

In practice what I noticed is that the average OLTP workload actually benefits from compression too. Besides reduced IO, the compressed row format is significantly narrower for most data (numeric and fixed length fields) and this adds benefits in terms of memory density (more rows fit in fewer pages, less memory used, less TLB misses, more reads from fewer cache lines etc etc). Things break as the OLTP loads moves toward the higher end spectrum (+16 cores, powerful IO subsystems capable of 1000s IOPS, RAM so generous as to negate the need for any page read post warm up etc). On these high end systems compression starts to have measurable impact and degrades performance.

So I would say ask yourself these questions:

  • will my deployment machine fit in memory the entire uncompressed database with plenty of room to spare? If yes, then the case for compression weakens significantly.
  • Is my data compressible? Numeric fields, fixed length columns are compressible (row compression). Unicode data is compressible most of the times. Repeated values on a page are compressible (page compression) (eg. long common prefixes to values repeated on clusters of rows close in index order). Note that page compression implies row compression.
  • what is my read vs. write ratio? Writes are affected more by compression. Read impact is less (compressed pages can respond from internal decompressed cached structures after the first read).
  • Is your data humongous? The is a threshold after which the administrative cost of size-of-data (eg. the size of backup files) becomes significant and compression may be considered to save that space, even if it hurts performance.

Ultimately though, we won't be able to guess. Measure. Measure on the intended deployment hardware, with data size close to real situation and a the load you expect.

2
  • BTW is hard to give any more detail than the SQLCAT article you've already read. Aug 3, 2011 at 4:38
  • No but you put some things in perspective that they failed to address, thank you for the insight. I did several hours of additional research after asking the question and I feel that I have a very good grasp on when to pursue it as an option. It is not the set and forget fix all that many are touting it to be. Thank you again. Aug 3, 2011 at 18:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .