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 123»»»

SQL Server 2008 Compression Expand / Collapse
Author
Message
Posted Wednesday, April 21, 2010 10:18 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 1,491, Visits: 2,115
Comments posted to this topic are about the item SQL Server 2008 Compression

Jason Shadonix
MCTS, SQL 2005
Post #908301
Posted Wednesday, April 21, 2010 11:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 2:10 AM
Points: 14, Visits: 46
Hi,

Maybe another reason not to use backup compression is that if your backups are sent to a device supporting de-duplication (HP's VLS & D2D, EMC's DataDomain, etc.) these backups will not de-dupe. At the end you will use more disk space because de-duplication would give you a compression factor far bigger than the 5 you got in your test.

Cheers!
Post #908317
Posted Thursday, April 22, 2010 5:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:34 AM
Points: 17, Visits: 210
Interesting results. This feature just became more desirable. I do wonder how caching affects results. BTW, 'peaked' should be 'piqued' in the sense of to excite or arouse. It's a lovely word with an odd spelling, and it's nice to see it being used.
Post #908464
Posted Thursday, April 22, 2010 6:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, December 8, 2013 9:53 PM
Points: 148, Visits: 219
hi,

i think that in SQL 2008 R2 the compression is now included in Standard version also.

Yaron
Post #908500
Posted Thursday, April 22, 2010 6:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 8:22 AM
Points: 74, Visits: 27
Great article. I'd like to see more about how query performance was affected. Also, when backing up a compressed table to a compressed backup, is that faster than an uncompressed table to a compressed backup (since it's already compressed)?


Post #908528
Posted Thursday, April 22, 2010 7:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:59 PM
Points: 361, Visits: 1,529
I have a table that's 10GB. There is a ntext column that holds the largest portion of the data. When I check the estimated compression for this table it only shows a 10MB reduction. That seems strange to me. Why would it not be able to compress that?
Post #908617
Posted Thursday, April 22, 2010 8:56 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 1,491, Visits: 2,115
ben.rosato (4/22/2010)
I have a table that's 10GB. There is a ntext column that holds the largest portion of the data. When I check the estimated compression for this table it only shows a 10MB reduction. That seems strange to me. Why would it not be able to compress that?



The only thing I see in BOL that might be a hint is the following:

"Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately."




Jason Shadonix
MCTS, SQL 2005
Post #908692
Posted Thursday, April 22, 2010 9:01 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 1,491, Visits: 2,115
dma333 (4/22/2010)
Great article. I'd like to see more about how query performance was affected. Also, when backing up a compressed table to a compressed backup, is that faster than an uncompressed table to a compressed backup (since it's already compressed)?


I didn't do any in-depth analysis of query performance, but I did run the query for the same amount of time for the three tables, and observed the compressed tables ended up with more records in them at the end of the three min run. Not sure what (if anything) you can read out of that.

Good question on the compressed backups of compressed databases. I hadn't thought of that. Since my test database contained a combination of compressed and uncompressed tables, not sure of the answer to your question. Should be fairly easy to test though.


Jason Shadonix
MCTS, SQL 2005
Post #908701
Posted Thursday, April 22, 2010 9:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
Small issue: "peaked my interest" should be "piqued my interest". It's a little distracting to see "peaked"! Good article, though.
Post #908723
Posted Thursday, April 22, 2010 9:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
Jason Shadonix (4/22/2010)
dma333 (4/22/2010)
Great article. I'd like to see more about how query performance was affected. Also, when backing up a compressed table to a compressed backup, is that faster than an uncompressed table to a compressed backup (since it's already compressed)?


I didn't do any in-depth analysis of query performance, but I did run the query for the same amount of time for the three tables, and observed the compressed tables ended up with more records in them at the end of the three min run. Not sure what (if anything) you can read out of that.

Good question on the compressed backups of compressed databases. I hadn't thought of that. Since my test database contained a combination of compressed and uncompressed tables, not sure of the answer to your question. Should be fairly easy to test though.


During a presentation by Microsoft, which of course might not be completely unbiased, the presenter did some backups and restores during the presentation. Backups AND restores were both significantly faster. I don't recall the times, but since backups and restores are both generally I/O bound, when you write less data, the whole process takes less time.

I think that the backup took about one-third of the time to back up with compresison on, and the restore took one-fourth of the time when the backup had been compressed... but that was just one presentation, and from my memory.

So, less space AND less time.
Post #908733
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse