Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

*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

share|improve this question

migrated from stackoverflow.com Aug 3 '11 at 6:20

This question came from our site for professional and enthusiast programmers.

3  
Benchmark for your particular situation. –  Mitch Wheat Aug 3 '11 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 '11 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. –  bumble_bee_tuna Aug 3 '11 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? –  Mitch Wheat Aug 3 '11 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. –  bumble_bee_tuna Aug 3 '11 at 2:39

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
BTW is hard to give any more detail than the SQLCAT article you've already read. –  Remus Rusanu Aug 3 '11 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. –  bumble_bee_tuna Aug 3 '11 at 18:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.