Page Compression

  • Comments posted to this topic are about the item Page Compression

  • Great question, with subtle differences between the answers 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (6/12/2011)


    Great question, with subtle differences between the answers 🙂

    I agree with you, it is a little tough to get this answer right 🙂

    M&M

  • This was removed by the editor as SPAM

  • I thought I had the correct answer, but the subtlety of the question caught me out.

  • Excellent question.

    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

  • Good question.

    But one major niggle and one minor niggle:

    Major niggle: if page compression is enabled on a table already containing data, each page is rebuilt and evaluated for page compression; page compression can occur here (unless I've got it wrong) and may make the data fit into fewer pages than if only row compression occurred. So the answer always applies to pages which are empty/unallocated when compression is enabled, but not always to pages which already contain data when compression is enabled.

    Minor niggle: page compression won't occur even when a new row doesn't fit if the engine determines that page compression would not free up a significant amount of space.

    edit: SCTEs

    Tom

  • This question is little tricky i.e When compression is enabled on server then only

    page compression can occur .if page compression is not enabled and if page is full and a new row is added then compression may not worked .

  • Tom.Thomson (6/13/2011)


    Good question.

    But one major niggle and one minor niggle:

    Major niggle: if page compression is enabled on a table already containing data, each page is rebuilt and evaluated for page compression; page compression can occur here (unless I've got it wrong) and may make the data fit into fewer pages than if only row compression occurred. So the answer always applies to pages which are empty/unallocated when compression is enabled, but not always to pages which already contain data when compression is enabled.

    Minor niggle: page compression won't occur even when a new row doesn't fit if the engine determines that page compression would not free up a significant amount of space.

    edit: SCTEs

    Fair points. I've added a note that this is after the initial page compression setting.

  • Missed it! But learned some important detail.

    Great question!

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • Nice question!

    See Data Compression: Strategy, Capacity Planning and Best Practices in ( http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx )

  • Good question and great topic, thanks.

  • Steve, this is a really good question. Thanks!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Tom.Thomson (6/13/2011)


    Good question.

    But one major niggle and one minor niggle:

    Major niggle: if page compression is enabled on a table already containing data, each page is rebuilt and evaluated for page compression; page compression can occur here (unless I've got it wrong) and may make the data fit into fewer pages than if only row compression occurred. So the answer always applies to pages which are empty/unallocated when compression is enabled, but not always to pages which already contain data when compression is enabled.

    Minor niggle: page compression won't occur even when a new row doesn't fit if the engine determines that page compression would not free up a significant amount of space.

    edit: SCTEs

    Thanks Tom for adding these points, very useful really.

    M&M

Viewing 14 posts - 1 through 13 (of 13 total)

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