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

Data Compression Double Take Expand / Collapse
Author
Message
Posted Saturday, March 3, 2012 11:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Today @ 8:50 AM
Points: 569, Visits: 1,034
Comments posted to this topic are about the item Data Compression Double Take
Post #1261172
Posted Sunday, March 4, 2012 2:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 25, Visits: 171
Have a bunch of MS Dynamics GP databases. The tables typically are substantially de-normalised (very "wide" tables of partially redundant data), have long fixed length character columns, and large numbers of decimal columns containing zeros. The databases and all their contained tables (some 1000 tables in a typical GP database) compress excellently using row compression; we haven't implemented page compression. In the large number of tables it appeared difficult to select appropriate candidates; it seemed easier to row compress all (except tables with a very low number of rows) and uncompress the exceptions if and when we found them. Have found no noticeably poor candidates as yet. In fairness to MS the table design is probably a legacy from the earlier multi-database support

In terms of CPU usage I am assuming that SELECTs with a high number of logical reads (in the hundereds of thousands of rows) should show high CPU when compressed. The effect does not appear noticeable, and the server in any case currently has horsepower to spare. Any effect is dwarfed by other CPU effects we may have from custom code

Since we are currently blessed with both sufficient RAM to contain the entire set of databases (nearly zero read IO from the smaller databases), and more recently some really fast write IO, our performance experience is a trifle difficult to compare

Tony T
Post #1261250
Posted Monday, March 5, 2012 7:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
Just remember that a database encrypted with TDE does not compress all that much.

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1261584
Posted Monday, March 5, 2012 12:36 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 8:32 AM
Points: 582, Visits: 4,947
TDE occurs when the pages are written to disk or read from disk. This means that TDE has little to no impact on page or row compression. You are correct about compression at the file level though.
Post #1261771
Posted Tuesday, March 6, 2012 11:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:28 PM
Points: 2,908, Visits: 1,834
Compression to save disk space is of dubious value unless it saves IO.

With technologies such as Fusion-IO and SSDs where you are talking about literally thousands of times that of mechanical disks IO performance does compression offer still offer benefits?

Where does the bottleneck shift once mechanical disk IO is mitigated?


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1262400
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse