January 14, 2011 at 1:34 am
Excellent use of the phrase "trade offs". Because effectively all you are doing is trading resources in a similar way to how a financial portfolio maybe managed. You're buying disk space and selling CPU (and probably a bit of RAM too to hold the compress and uncompress versions).
It's great to have the ability to do this resource trading but it's important to realize that nothing comes free. The only way to justify the resource trade is to determine and measure the benefits against you're business requirements. It may seem like great news to have released so much disk space, but disk is relatively cheap compared to other hardware resources.
If performance is your goal then compression on very large tables can help considerably. If you dig deep into MPP appliances like Netezza you will find that part of the way they achieve staggering performance is by using compression algorithms close to disk.
January 18, 2011 at 9:48 am
I am currently conducting some tests that look at the result of uncompressed vs. page vs. row. The results are not final yet, but only a handful of tables compressed less than 50% out of my 54 table sample. I am impressed overall with the results at this level. It is important to point out two things about this preliminary test: 1. the overall savings at the database level was not as much as I expected, and, 2. when I compared before and after, I was careful only to compare data pages consumed [not reserved]. So my numbers looked great at the table level, but the overall savings was not what I had hoped for.
A few other observations...
My earlier testing indicates that the proc for estimating compression is not terribly reliable.
I believe that compression need only be applied to: 1) data warehouses, 2) tables larger than one sector read [typically 64 to 128 8k pages].
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply