September 2, 2010 at 9:44 pm
Comments posted to this topic are about the item Page/Row Compression
September 2, 2010 at 9:45 pm
Thanks for the question!
September 3, 2010 at 2:42 am
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.
September 3, 2010 at 3:25 am
This was removed by the editor as SPAM
September 3, 2010 at 3:38 am
The word "affected" has done a great job of muddling this question. I would tend argue it changes all the options provided.
September 3, 2010 at 3:56 am
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. 😉
September 3, 2010 at 4:00 am
Great question and discussions - learnt a lot from them!
September 3, 2010 at 6:57 am
Tough question. Also some good discussion. Thanks.
September 3, 2010 at 8:03 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 3, 2010 at 8:18 am
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[/url]
Learn Extended Events
September 3, 2010 at 9:39 am
Hugo Kornelis (9/3/2010)
- 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.
To its defense, the BOL page does state very clearly that because
Compression occurs in the storage engine and the data is presented to most of the other components of SQL Server in an uncompressed state
•Compression does not affect backup and restore.
The options in the question clearly match the How Compression Affects Other SQL Server Components paragraph of the referenced in the answer BOL page, so this is a good question.
Oleg
September 3, 2010 at 10:04 am
Thanks for the question. I enjoyed educating myself about this feature and was fortunate to find the same article referenced in the explanation.
J DBA
September 3, 2010 at 11:38 am
Good question! But wouldn't the backup and restore get affected because of the compressed row size? The DB backup would be relatively smaller and hence the restores would be faster.
Thanks,
Amol Naik
September 3, 2010 at 11:45 am
I appreciate all the comments and discussion.
@cdiebolt .. The question was a bit tricky with the language. However the backup/restore operations are not affected as per the performance of the sql server. However I agree that the size of the backup/restore is altered.
@hugo Kornelis.. Thanks fot the detailed explanation on all the options. Though I have some argument on the
log shipping part that you mentioned. It's definitely true that the log of the compressed database does not have compressed information and hence does not have any size compression. On the other hand the backup/restore has compressed size because of the compressed database. Another thing about the log compression that you mentioned is actually exploited in the Mirroring in SQL Server 2008 refer this link http://msdn.microsoft.com/en-us/library/cc645581.aspx
@CirquedeSQLeil.. Didnt get much of what you intended to say but logs are not affected by database compression.
September 3, 2010 at 11:57 am
I really appreciate the comments and arguments.
@cdiebolt.. The language of the question is tricky. The intention was to ask about the performance of the server affected. However the size of the backup definitely is reduced by a good margin because of Page/Row level compression.
@hugo Kornelis.. Thanks for the elaboration on all the options. I would like to mention a few things about the comment you made about the sql log compression. The log is definitely not getting compressed and hence has no benefit. On the other hand the size of the backup is reduced cause of the database compression and not because of any compression on the backup. However your comment about the log compression saving bandwidth is actually exploited in SQL Server 2008 Mirroring as mentioned in this link http://msdn.microsoft.com/en-us/library/cc645581.aspx
@CirquedeSQLeil.. Didn't get what you intended to ask but then log's are not compressed when you set up database compression. Hope that helps.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply