SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 Compression


SQL Server 2008 Compression

Author
Message
TheRedneckDBA
TheRedneckDBA
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5922 Visits: 2621
Comments posted to this topic are about the item SQL Server 2008 Compression

The Redneck DBA
Michel Steiner
Michel Steiner
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 84
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!
john.richter
john.richter
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 285
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.
yaron
yaron
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 224
hi,

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

Yaron
dma333
dma333
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 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)?



ben.rosato
ben.rosato
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 1566
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?
TheRedneckDBA
TheRedneckDBA
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5922 Visits: 2621
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."

The Redneck DBA
TheRedneckDBA
TheRedneckDBA
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5922 Visits: 2621
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.

The Redneck DBA
David Walker-278941
David Walker-278941
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 231
Small issue: "peaked my interest" should be "piqued my interest". It's a little distracting to see "peaked"! Good article, though.
David Walker-278941
David Walker-278941
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 231
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search