Page/Row Compression

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    Comments posted to this topic are about the item Page/Row Compression

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the question!

  • cdiebolt

    Ten Centuries

    Points: 1043

    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.

  • This was removed by the editor as SPAM

  • Carlton Leach

    SSCarpal Tunnel

    Points: 4202

    The word "affected" has done a great job of muddling this question. I would tend argue it changes all the options provided.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Michael Riemer

    SSCertifiable

    Points: 5136

    Great question and discussions - learnt a lot from them!

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Tough question. Also some good discussion. Thanks.

  • webrunner

    SSC-Dedicated

    Points: 30311

    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

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Oleg Netchaev

    SSCertifiable

    Points: 5272

    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

  • J DBA

    SSCarpal Tunnel

    Points: 4954

    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

  • AmolNaik

    SSCarpal Tunnel

    Points: 4767

    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

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    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.

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    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 21 total)

You must be logged in to reply to this topic. Login to reply