<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 Tushar Kanti
»
Page/Row Compression
21 posts, Page 1 of 3
1
2
3
»
»»
Page/Row Compression
Rate Topic
Display Mode
Topic Options
Author
Message
tushkieeeee
tushkieeeee
Posted Thursday, September 02, 2010 9:44 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, April 25, 2013 7:59 AM
Points: 103,
Visits: 410
Comments posted to this topic are about the item
Page/Row Compression
Post #980019
UMG Developer
UMG Developer
Posted Thursday, September 02, 2010 9:45 PM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163,
Visits: 2,148
Thanks for the question!
Post #980020
cdiebolt
cdiebolt
Posted Friday, September 03, 2010 2:42 AM
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:34 AM
Points: 886,
Visits: 283
I don't totally agree with the answers. For me, Page/Row Compression affects also backup, as the backups of a compressed data tend to be smaller. I think it's mostly a problem of how the question is written though.
Post #980075
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Friday, September 03, 2010 3:25 AM
Hall of Fame
Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 3,129,
Visits: 4,312
Thanks for the question
I enjoyed researching this topic, as I am investigating the pros and cons of applying page level compression on our larger partitioned tables.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #980089
Carlton Leach
Carlton Leach
Posted Friday, September 03, 2010 3:38 AM
SSC Eights!
Group: General Forum Members
Last Login: Monday, May 20, 2013 6:52 AM
Points: 863,
Visits: 1,022
The word "affected" has done a great job of muddling this question. I would tend argue it changes all the options provided.
Post #980099
Hugo Kornelis
Hugo Kornelis
Posted Friday, September 03, 2010 3:56 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 5,244,
Visits: 7,061
Though I am very happy to see a question on a relatively new and very valuable option, I am a bit disappointed that the answers are debatable.
It all depends on what you mean when you say "affected".
- Restore and backup:
You could argue that this is simply copying pages from one place to another. What is on the page is irrelevant. The internal code to implement this is unchanged. Hence unaffected.
You could also argue that backup files of compressed databases are a lot smaller. Hence affected.
- Existing query plan:
I don't expect disagreement here. Indeed, all query plans will be re-optimized after enabling or disabling compression.
- Logshipping:
I *think* that even in a compressed database, the information in the log file is still uncompressed. If that is correct, than obviously logshipping is not affected, as the log file is not affected.
But if I am wrong, then the same reasoning applies as for the backup and restore. If compression makes the log file smaller, then you could argue that the code is unchanged (unaffected), or that you save a lot of bandwith (affected).
- Bulk import and export
I am frankly surprised by the remark in Books Online. Yes, obviously the exported data can be larger than the compressed database. D'uh! Should Books Online now also include a warning that printed reports might take more rainforests than the size of the data file suggests?
The internal code for the bulk operations are just as unaffected as any other code. SQL Server uncompresses the data when reading from disk to cache, and recompresses it when writing from cache to disk. Only a few SQL Server components ever access the disk directly, the cache manager (to read data into cache) and the lazy writer and checkpoint processes (to flush modified data from cache to disk) being the most important ones. All other components access cached data only. That includes the bulk operation. So this code is just as unaffected as the backup and restore code (that, incidentally, are two other components with direct access to disk - othwise, a backup of a compressed database would be just as large of the backup of an uncompressed one).
So thanks for making me think, and for enabling an interesting discussion. But for future QotD's (and I hope you will submit more), please try to make the question as answer as concise as possible, and to weed out any possible interpretation differences.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog:
http://sqlblog.com/blogs/hugo_kornelis
Post #980105
Michael Riemer
Michael Riemer
Posted Friday, September 03, 2010 4:00 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 1,241,
Visits: 347
Great question and discussions - learnt a lot from them!
Post #980107
Daniel Bowlin
Daniel Bowlin
Posted Friday, September 03, 2010 6:56 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 2,673,
Visits: 2,418
Tough question. Also some good discussion. Thanks.
Post #980165
webrunner
webrunner
Posted Friday, September 03, 2010 8:03 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 2,118,
Visits: 2,213
Carlton Leach (9/3/2010)
The word "affected" has done a great job of muddling this question. I would tend argue it changes all the options provided.
I agree, although I admit was guessing that it would affect all the items. Maybe there's a way to phrase the question more narrowly without giving away any hints about the answer.
Thanks,
webrunner
-------------------
"The chemistry must be respected." - Walter White
"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.:
http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #980220
SQLRNNR
SQLRNNR
Posted Friday, September 03, 2010 8:18 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754,
Visits: 12,337
Interesting question with a bit of an 'It Depends' set of Answers.
Log Shipping would be affected if the database were set to log-ship first and then compression were enabled after the fact. This change would be replicated through the logs to the new server - thus log-shipping files would have been affected. After that, no affect.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #980228
« Prev Topic
|
Next Topic »
21 posts, Page 1 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.
