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

  
     
Recent Posts
   
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
Add to briefcase
21 posts, Page 1 of 3
123»»»

Page/Row Compression Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Tushar Kanti
Posted Thursday, September 2, 2010 9:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 13, 2016 7:23 AM
Points: 117, Visits: 486
Comments posted to this topic are about the item Page/Row Compression
Post #980019
UMG Developer
Posted Thursday, September 2, 2010 9:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 30, 2015 9:47 AM
Points: 2,160, Visits: 2,204
Thanks for the question!
Post #980020
cdiebolt
Posted Friday, September 3, 2010 2:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 25, 2016 10:57 AM
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.


Post #980075
Stewart "Arturius" Campbell
Posted Friday, September 3, 2010 3:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 5,264, Visits: 6,989
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
Posted Friday, September 3, 2010 3:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 14, 2016 4:25 AM
Points: 873, Visits: 1,275
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
Posted Friday, September 3, 2010 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 7,712, Visits: 11,055
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
Posted Friday, September 3, 2010 4:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 16, 2016 1:20 AM
Points: 2,528, Visits: 607
Great question and discussions - learnt a lot from them!
Post #980107
Daniel Bowlin
Posted Friday, September 3, 2010 6:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 17, 2016 8:01 AM
Points: 2,834, Visits: 2,629
Tough question. Also some good discussion. Thanks.
Post #980165
webrunner
Posted Friday, September 3, 2010 8:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 2,904, Visits: 3,646
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
Post #980220
SQLRNNR
Posted Friday, September 3, 2010 8:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 10:17 PM
Points: 20,008, Visits: 18,244
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
Post #980228
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2016 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.