SQL Server 2008 Compression

Question

SQL Server 2008 Compression

TheRedneckDBA

SSChampion

Points: 14001
More actions
April 21, 2010 at 10:18 pm

#234135

Comments posted to this topic are about the item SQL Server 2008 Compression
The Redneck DBA

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Michel Steiner SSC Enthusiast Points: 148 More actions · Answer 1

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 SSC Veteran Points: 222 More actions · Answer 2

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 SSC-Addicted Points: 462 More actions · Answer 3

hi,

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

Yaron

dma333 Right there with Babe Points: 792 More actions · Answer 4

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 SSCommitted Points: 1929 More actions · Answer 5

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 SSChampion Points: 14001 More actions · Answer 6

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 SSChampion Points: 14001 More actions · Answer 7

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 Ten Centuries Points: 1064 More actions · Answer 8

Small issue: "peaked my interest" should be "piqued my interest". It's a little distracting to see "peaked"! Good article, though.

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 9

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.

ben.rosato SSCommitted Points: 1929 More actions · Answer 10

Jason Shadonix (4/22/2010)
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."

Yep, must be due to the ntext data type. I wonder if nvarchar(max) compresses.

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 11

This is confusing: "As you can see from the disk bytes/sec counter, there is a considerable savings caused by both row and page level compression, with the page level compression yielding the highest improvement."

How is this? The number of bytes per second is much lower with compression on. What is the "considerable savings"? A lower number of bytes per second is a bad thing, not a good thing. Can you explain this please?

Finally, what is the "time" axis on all of the charts? What kind of time? The axis is not labeled with any units (minutes, seconds, hours...). Is this the time since the test started, and if so, how is that significant?

Thanks.

ben.rosato SSCommitted Points: 1929 More actions · Answer 12

Compression doesn't appear to work on nvarchar(max).

calico-604598 Right there with Babe Points: 730 More actions · Answer 13

Hi,

If a very big table doesn't have a clustered index and I decide to compress this table, is it possible that this table become fragmented ?

calico

sknox SSChampion Points: 12388 More actions · Answer 14

David Walker-278941 (4/22/2010)
This is confusing: "As you can see from the disk bytes/sec counter, there is a considerable savings caused by both row and page level compression, with the page level compression yielding the highest improvement."
How is this? The number of bytes per second is much lower with compression on. What is the "considerable savings"? A lower number of bytes per second is a bad thing, not a good thing. Can you explain this please?

We're talking SQL Server performance, not disk performance. This is not how many bytes per second the disk array is capable of; this is how many bytes SQL Server had to read/write to disk during the testing -- and the less disk access needed, the better.

This is also why (I believe) the compression results in faster backup and access times -- the bottleneck in this particular test is the disk array.