Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Data Compression Objects Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 8:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
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

Post #1523738
Posted Tuesday, December 17, 2013 10:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
Elementary dear Watson, er, Jones.

Thanks, Steve!
Post #1523805
Posted Tuesday, December 17, 2013 1:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 3,915, Visits: 5,104
Nice one, thanks Steve
We have been applying page compression on our ods and data warehouses and have saved in ezcess of 1TB in storage. The day-end process doesn't take any longer, just the cpu usage has increased by about 5%.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1523877
Posted Tuesday, December 17, 2013 6:44 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 8,718, Visits: 9,266
Nice tidy question on a well documented issue, so I'm surprised that more than 30% so far have it wrong.

Tom
Post #1523919
Posted Wednesday, December 18, 2013 1:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:46 AM
Points: 5,343, Visits: 1,387
Nice question....


Post #1523983
Posted Wednesday, December 18, 2013 6:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 2,677, Visits: 215
I'm a little late to the party but thanks for the info.
Post #1524098
Posted Wednesday, December 18, 2013 11:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 21,642, Visits: 15,311
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 don't know about Koens perf gains, but I typically see a significant gain in performance. I have seen queries run up to 10x faster with 1/10 the IO. Sure there may be more CPU, but it is for a shorter duration. I'm a little picky in where I compress though.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1524250
Posted Tuesday, December 24, 2013 1:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
Guess what, simple straightforward question for those who read. I was the fortunate one.
Post #1525708
Posted Tuesday, December 24, 2013 12:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 4, 2014 12:29 PM
Points: 1,812, Visits: 584
nice and easy..
thanks steve.
Post #1525806
Posted Wednesday, January 8, 2014 12:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:24 AM
Points: 1,144, Visits: 299
easy one just logical answer after eliminating obviously incorrect ones
Post #1528779
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse