Data Compression Objects

Question

Post reply

Data Compression Objects

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
December 16, 2013 at 9:37 pm

#74191

Comments posted to this topic are about the item Data Compression Objects
December 17, 2013 at 1:53 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Nice solid yet simple enough question, thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

I have been enabling page compression on our entire data warehouse recently, so this was a pretty easy question 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 3

Koen Verbeeck (12/17/2013)
I have been enabling page compression on our entire data warehouse recently, so this was a pretty easy question 🙂

Whats your performance been like since ?

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Nice question, thanks.

Igor Micev,My blog: www.igormicev.com

steve.jacobs SSCommitted Points: 1830 More actions · Answer 5

steve.jacobs

SSCommitted

Points: 1830

December 17, 2013 at 6:40 am

#1674044

+1 Steve. Thanks

Ken SSCoach Points: 17191 More actions · Answer 6

Ford Fairlane (12/17/2013)
Koen Verbeeck (12/17/2013)
I have been enabling page compression on our entire data warehouse recently, so this was a pretty easy question 🙂
Whats your performance been like since ?

I was about to ask this same thing. I have been considering doing this as well. So far I have only added page compression to our archive tables and not to ones being accessed more frequently.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 7

Koen Verbeeck (12/17/2013)
I have been enabling page compression on our entire data warehouse recently, so this was a pretty easy question 🙂

What kind of compression ratio have you obtained?

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 8

KWymore (12/17/2013)
Ford Fairlane (12/17/2013)
Koen Verbeeck (12/17/2013)
I have been enabling page compression on our entire data warehouse recently, so this was a pretty easy question 🙂
Whats your performance been like since ?
I was about to ask this same thing. I have been considering doing this as well. So far I have only added page compression to our archive tables and not to ones being accessed more frequently.

If the compression brings you over 70% storage gain, than you should compress without any measurements, otherwise you should make some measurements concerning the CPU usage on compressed and uncompressed tables.

Once I did some investigation on compression on a small system. I compressed the entire database (mixed: page and row) and run a tool that was processing data against compressed and uncompressed databases. The processing time was same, and i obtained a storage gain. The avg CPU usage increased but not for much. It's recommended the avg CPU should not be more than 80%. So you can even compress the more frequently accessed tables.

If the reads operations dominate over the writes (e.g. more than 95:5) than you go with compression.

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

Ken SSCoach Points: 17191 More actions · Answer 9

IgorMi (12/17/2013)
KWymore (12/17/2013)
Ford Fairlane (12/17/2013)
Koen Verbeeck (12/17/2013)
I have been enabling page compression on our entire data warehouse recently, so this was a pretty easy question 🙂
Whats your performance been like since ?
I was about to ask this same thing. I have been considering doing this as well. So far I have only added page compression to our archive tables and not to ones being accessed more frequently.
If the compression brings you over 70% storage gain, than you should compress without any measurements, otherwise you should make some measurements concerning the CPU usage on compressed and uncompressed tables.
Once I did some investigation on compression on a small system. I compressed the entire database (mixed: page and row) and run a tool that was processing data against compressed and uncompressed databases. The processing time was same, and i obtained a storage gain. The avg CPU usage increased but not for much. It's recommended the avg CPU should not be more than 80%. So you can even compress the more frequently accessed tables.
If the reads operations dominate over the writes (e.g. more than 95:5) than you go with compression.
Regards
IgorMi

Thank you, I will keep that in mind when I am able to get back to that testing!

sneumersky SSCertifiable Points: 7667 More actions · Answer 10

Our data warehouse got a compression ratio of 68% using page compression. The best compression ratios are usually with star schemas with all (or close to all) integer surrogate keys. However, beware of compression's effect on data loading time. If you already are squeezing your data load into a small window of time or if your data load is CPU bound, you MAY be in for a surprise.

See The Data Loading Performance Guide for how compression affects CPU usage/data loading time and much, much more. It was created for SQL Server 2008, but it remains relevant today.

http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx