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, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
Elementary dear Watson, er, Jones.

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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 4,100, Visits: 5,456
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:38 AM
Points: 7,850, Visits: 9,600
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: Thursday, October 16, 2014 4:46 AM
Points: 5,439, Visits: 1,400
Nice question....


Post #1523983
Posted Wednesday, December 18, 2013 11:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
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: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
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