Page Compression

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715446

    Comments posted to this topic are about the item Page Compression

  • Koen Verbeeck

    SSC Guru

    Points: 258929

    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

  • M&M

    SSC-Insane

    Points: 21679

    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

  • paul s-306273

    SSChampion

    Points: 10555

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • TomThomson

    SSC Guru

    Points: 104767

    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

  • ashjoe7

    SSC Rookie

    Points: 36

    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 .

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715446

    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.

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    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

  • Rafael Krisller

    Right there with Babe

    Points: 737

    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 )

  • Cliff Jones

    SSChampion

    Points: 10517

    Good question and great topic, thanks.

  • WayneS

    SSC Guru

    Points: 95341

    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

  • M&M

    SSC-Insane

    Points: 21679

    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 14 (of 14 total)

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