SQL Server 2008 Compression

  • TheRedneckDBA

    SSChampion

    Points: 14001

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

    The Redneck DBA

  • Michel Steiner

    SSC Enthusiast

    Points: 148

    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

    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

    hi,

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

    Yaron

  • dma333

    Right there with Babe

    Points: 772

    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

    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

    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

    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

    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

    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

    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

    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

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

  • calico-604598

    Right there with Babe

    Points: 730

    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: 12284

    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.

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

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