Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required. It also seems to imply that this is the primary downside to data compression, strongly implying that for a read-only archive database, the performance will (with few excep tions) be improved by the usage of data compression of 100% filled pages.

  1. Are the statements above true?
  2. What are the primary "variations" between data compression and otherwise (for reading)

    • "CPU +x%"?
    • "IO -y%"?
    • page split occurence?
    • tempdb usage?
    • RAM usage?
  3. And for writing?

For the purpose of this question, you can limit the context to PAGE-level compression of a big (> 1TB) database, but additional comments are always welcome.


SQL Server Storage Engine Blog (The DW scenario shows compression to be very advantageous)
Data Compression: Strategy, Capacity Planning and Best Practices

A more detailed approach to deciding what to compress involves analyzing the workload characteristics for each table and index. It is based on the following two metrics:

U: The percentage of update operations on a specific table, index, or partition, relative to total operations on that object. The lower the value of U (that is, the table, index, or partition is infrequently updated), the better candidate it is for page compression.
S: The percentage of scan operations on a table, index, or partition, relative to total operations on that object. The higher the value of S (that is, the table, index, or partition is mostly scanned), the better candidate it is for page compression.

Both of the above are demonstrably biased towards recommending page compression for DW-style databases (read-intensive/exclusive, big-data operations).

share|improve this question
What literature specifically? There is always going to be CPU overhead for both compress/uncompress but, as with reads, you are writing to a fewer number of pages too. In fact I would think the write side would benefit even more than the read side since the read side will often have the compressed pages stored in memory (this isn't always, but a best case depending on size of data and memory allocated). –  Aaron Bertrand Apr 3 '13 at 21:55
It is going to be very difficult to provide any of the metrics you are asking for because it wholly depends on the nature of the data and the ability to compress it (and this is going to be different depending on row vs. page, as well). Some people have reported up to 90% compression ratio which is going to have an impact on both memory usage (in a positive way) and CPU to perform that much compression. This paper ballparks CPU overhead at 10% for row compression and higher for page. What you observe may be quite different. –  Aaron Bertrand Apr 3 '13 at 21:59
For a read-only archive database, I guess the question would be whether it can fit in memory. If it can all fit in memory then once it is loaded into the buffer pool there is no real benefit to having it compressed. If, however, it can't all fit into memory, you may still see some benefit in swapping fewer pages in and out of cache even though there will be work performed uncompressing it. –  Aaron Bertrand Apr 3 '13 at 22:03
Neither of the links you added seem to make any mention of this 4x penalty for writing. Do you remember where you picked that up? Would like to see the context. –  Aaron Bertrand Apr 3 '13 at 22:14
Well if you can't fit the data into memory than that scenario is kind of moot, right? :-) –  Aaron Bertrand Apr 3 '13 at 22:33
show 3 more comments

2 Answers

Just my 2cents from my own experiments on 1-2 year old hardware:

Read-only operations (DW-style scans, sorts etc) on page-compressed tables (~80rows/page) I've found to break-even at compression size reduction of ~ 3x.

I.e. if the tables fit into memory anyway, page compression only benefits performance if the data size has shrunk by over 3x. You scan fewer pages in memory, but it takes longer to scan each page.

I guess your mileage may vary if your plans are nested-loop and seek-heavy. Among others, this would also be hardware-dependent (foreign NUMA node access penalties, memory speed etc).

The above is just a rough rule-of-thumb that I follow, based on my own test runs using my own queries on my own hardware (Dell Poweredge 910 and younger). It is not gospel eh!

Edit: Yesterday the excellent SQLBits XI presentation of Thomas Kejser was made available as a video. Quite relevant to this discussion, it shows the 'ugly' face of CPU cost for page compression - updates slowed down by 4x, locks held for quite a bit longer.

However, Thomas is using FusionIO storage and he picked a table that is only 'just' eligible for page compression. If storage was on a typical SAN and the data used compressed 3x-4x then the picture might have been less dramatic.

share|improve this answer
Can that be the old hardware? On new hardware, bare SSD For storage, I find the cores not being able to keep up with the discs easily. I noramlly thuoght the benefit would start a LOT easlier - a 50% reduction in IO is well worth it when not doing that many changes. –  TomTom Jun 3 '13 at 8:38
TomTom, Storage doesn't come into play for these figures. The comparison is between uncompressed-tables-in-memory and compressed-tables-in-memory. –  John Alan Jun 3 '13 at 10:10
Never seen a DWH that was good enough for memory. Seriously. You WILL fall back to disc. –  TomTom Jun 3 '13 at 13:02
Yes of course you will occasionally fall back to disk - reading from disk is where page-compression nearly always has an edge (assuming the data is compressible enough!). But if your workload loads from disk once and then manipulates everything in memory for the rest of the day - how much weight would you give to the disk reading and how much to the in-memory operations? –  John Alan Jun 3 '13 at 18:00
Just came across a relevant presentation slidedeck from SQLBits 2013 by Thomas Kejser: slideshare.net/fusionio/… –  John Alan Jun 3 '13 at 18:50
add comment

Everyone agrees, that benefits of any feature is heavily depends on the use of the database and queries running. Probably your question should be rephrased as

How far can we go in using Data Compression in cases it imposes processing penalties until benefits of Compression outweight by it's limitations?

It would be hard to answer it. Eg. I have huge increase in performance as I'm not using joins or caluculations on cell contents in Where clauses.

share|improve this answer
add comment

Your Answer


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.