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


SQL Server 2008 Compression


SQL Server 2008 Compression

Author
Message
ben.rosato
ben.rosato
Mr or Mrs. 500
Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)

Group: General Forum Members
Points: 583 Visits: 1566
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
David Walker-278941
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 231
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
ben.rosato
Mr or Mrs. 500
Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)

Group: General Forum Members
Points: 583 Visits: 1566
Compression doesn't appear to work on nvarchar(max).
calico-604598
calico-604598
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 827
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
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4111 Visits: 2935
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68607 Visits: 18570
This is a great article. Highly informative and is helpful in a decision I'm looking to make currently about compression.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

David Walker-278941
David Walker-278941
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 231
sknox (4/22/2010)
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.


Ah, yes, that does make sense. Still, it would be useful to know what the "units" are on the Time scale.

I think that with a lot of SQL Server work, the disk subsystem is the bottleneck... not just in these tests.
TheRedneckDBA
TheRedneckDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4352 Visits: 2614
David Walker-278941 (4/22/2010)
[quote]sknox (4/22/2010)


Ah, yes, that does make sense. Still, it would be useful to know what the "units" are on the Time scale.

I think that with a lot of SQL Server work, the disk subsystem is the bottleneck... not just in these tests.


Sorry - just noticed the time question. The tests I did were just short 3 min. tests, and those "units" are just what MS Excel decided I wanted, and I didn't feel like fighting. Looks like they are about 15 sec. intervals.

Before you implemented anything, I'd suggest playing on a dev box with a copy of the database you are considering compressing with a more realistic load, and run tests for a lot longer.

We are just getting ready to run live with our first 2008 box (all 2005 otherwise) with a brand new app, so for now I'm just doing backup compression and not DB compression. From what I can tell it's just as easy to implement compression later as it is initially (keeping in mind a short burst of CPU/Disk use will probably be involved).

The Redneck DBA
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4111 Visits: 2935
David Walker-278941 (4/22/2010)
sknox (4/22/2010)

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.

I think that with a lot of SQL Server work, the disk subsystem is the bottleneck... not just in these tests.


Certainly, but as that's not always the case, and especially as encryption and compression have historically been compute-intensive tasks, I felt it was important to note that fact. As the disk subsystem is the bottleneck here, compression helps performance by decreasing reliance on that subsystem. But if you know that your disk subsystem has better performance characteristics -- or that your CPU has worse -- you should expect less dramatic results -- possibly even decreased performance in extreme cases.
Mike Byrd
Mike Byrd
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 389
Good article and glad to see numbers attached to it.

One question, though, when you apply row or page compression is the table (and its respective indexes) still accessible or does it maintain some sort of lock on it. Reason for question is that my production OLTP database is in a 24x7 environment and we had no maintenance window.

Mike Byrd
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