Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Page/Row Compression


Page/Row Compression

Author
Message
Tushar Kanti
Tushar Kanti
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 494
Comments posted to this topic are about the item Page/Row Compression
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks for the question!
cdiebolt
cdiebolt
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 292
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.



Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5786 Visits: 7137
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”
Carlton Leach
Carlton Leach
SSC Eights!
SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)

Group: General Forum Members
Points: 883 Visits: 1285
The word "affected" has done a great job of muddling this question. I would tend argue it changes all the options provided.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11579
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
Michael Riemer
Michael Riemer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2692 Visits: 626
Great question and discussions - learnt a lot from them!
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Tough question. Also some good discussion. Thanks.
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3031 Visits: 3755
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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