<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Nicholas Cain
»
SQL Server 2008 and Data Compression
23 posts, Page 2 of 3
««
1
2
3
»»
SQL Server 2008 and Data Compression
Rate Topic
Display Mode
Topic Options
Author
Message
Nicholas Cain
Nicholas Cain
Posted Thursday, January 08, 2009 11:17 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:27 PM
Points: 2,007,
Visits: 6,040
The only problem with that is that in a real world scenario everyone will get different results. Depending upon the datatypes that you use, the free space in your pages and the like you will get differing amounts of compression and different performance gains/losses.
Everyone needs to run their own evaluation, however it would be great to have a location where folks could put their real world examples (such as prior space utilization, prior performance, schemas, new utilization, new performance and a calculation of how much compression has improved (or degraded) their performance).
From a community perspective that could really help folks make a slightly more informed decision prior to going through the work involved in testing it all out.
Shamless self promotion -
read my blog http://sirsql.net
Post #632682
Hugo Shebbeare
Hugo Shebbeare
Posted Thursday, January 08, 2009 10:23 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:03 PM
Points: 11,
Visits: 224
I blogged about this, and thoroughly recommend it to anyone who is stuck in production on SQL 2005 (vardecimal option for financial databases), or who has SQL 2008:
http://www.sqlservercentral.com/blogs/hugo/archive/2009/01/03/sql-2008-row-and-page-compression-or-sql-2005-post-sp2-vardecimal-conversion.aspx
Town of Mount Royal, QC
514 812 5087 (txt also)
hugo@intellabase.com (msn im also)
Post #633080
ShellyP
ShellyP
Posted Friday, January 09, 2009 8:02 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 2:45 PM
Points: 289,
Visits: 328
does this compression methodology play nicely with TDE?
Post #633428
Nicholas Cain
Nicholas Cain
Posted Friday, January 09, 2009 8:16 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:27 PM
Points: 2,007,
Visits: 6,040
It's invisible to TDE.
Shamless self promotion -
read my blog http://sirsql.net
Post #633455
richcoulson
richcoulson
Posted Friday, January 09, 2009 8:39 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, October 27, 2009 9:00 AM
Points: 136,
Visits: 64
We have a warehouse database that is normally around 400gigs which compresses down to 80gigs with row level compression. Performance of queries varies widely depending on the disk io needed to satisfy the query. I do have one very disk io intensive query that normally runs in 2 minutes, but now runs in 1 minute. Cutting the time in half is very impressive. Both compressed and uncompressed databases are on the same server running under the same sql server instance. The server is one HP loaned us to do some testing with. It is their latest DL580 with 24 intel cores and 32 gigs of ram and is blazing fast.
Something interesting I noticed, but have not had a chance to dig into is that once the data is cached in memory and the same queries ran a second time, the performance seems to take about 20% longer for the compressed database. Seems odd since I am pretty sure data in cache does not get compressed. Both data and indexes are using row level compression. I haven’t had a chance to look at optimization plans yet to see if they are different.
Post #633496
Nicholas Cain
Nicholas Cain
Posted Friday, January 09, 2009 8:45 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:27 PM
Points: 2,007,
Visits: 6,040
richcoulson (1/9/2009)
We have a warehouse database that is normally around 400gigs which compresses down to 80gigs with row level compression. Performance of queries varies widely depending on the disk io needed to satisfy the query. I do have one very disk io intensive query that normally runs in 2 minutes, but now runs in 1 minute. Cutting the time in half is very impressive. Both compressed and uncompressed databases are on the same server running under the same sql server instance. The server is one HP loaned us to do some testing with. It is their latest DL580 with 24 intel cores and 32 gigs of ram and is blazing fast.
Something interesting I noticed, but have not had a chance to dig into is that once the data is cached in memory and the same queries ran a second time, the performance seems to take about 20% longer for the compressed database. Seems odd since I am pretty sure data in cache does not get compressed. Both data and indexes are using row level compression. I haven’t had a chance to look at optimization plans yet to see if they are different.
Can I just say...wow...that's a lot of compression. Have you looked at page level yet?
Shamless self promotion -
read my blog http://sirsql.net
Post #633502
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Friday, January 09, 2009 8:49 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425,
Visits: 13,738
I thought that data was compressed in memory for read aheads, but once it needed to be compared to something else it was uncompressed.
That's great compression, but it is strange that those queries are taking longer. I might call PSS and make sure there's no bug here. Or you might gather some more info, post a question here or the MS forums and I'll ping someone at MS.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #633508
richcoulson
richcoulson
Posted Friday, January 09, 2009 8:51 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, October 27, 2009 9:00 AM
Points: 136,
Visits: 64
No, haven't tried page level yet. Our redgate compressed backups went down from 40gigs to 24gigs for the compressed database. Compression is going to be huge for us as we look for more sever consolidation. With more processors being available on servers now, we seem to be more and more disk constrained, which should be lessened with compression. We also have to population pilot and test database every night, which will now be quicker with smaller backups.
Post #633509
richcoulson
richcoulson
Posted Friday, January 09, 2009 9:09 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, October 27, 2009 9:00 AM
Points: 136,
Visits: 64
Just to clarify, the 20% increase is compared to the same cached query of the non-compressed database.
Times:
non-compressed first run 2 minutes, second run 11 seconds
compressed first run 1 minute, second run 14 seconds
I run dbcc freeproccache and dbcc dropcleanbuffers before each first run. The above times are consistantly recreated.
I have seen other queries with similar results for the cached runs. Keep in mind my testing has not been extensive and has only been with a handfull of queries that have been poor performers. We have to give our demo server back to hp, so today will be my last day of testing. We will be setting up a permanant test server in the next few weeks, so we will be able to resume further testing. Steve, if there are any stats such as statistics io you want now, I can send them to you. We do have Microsoft dedicate support engineer we can bring in as well once we get our permanent test server up.
Post #633538
mishaluba
mishaluba
Posted Sunday, January 11, 2009 9:54 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525,
Visits: 617
Thanks for the article! There are a lot of misconceptions about compression out there and you will never know if it's right for you, until you evaluate, test, evaluate, test, etc. The script provided with the article will go a long way towards simplifying this process for me!
Post #634466
« Prev Topic
|
Next Topic »
23 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
