How Bad are Bad Page Splits?

  • Mike Byrd

    Ten Centuries

    Points: 1253

    Comments posted to this topic are about the item How Bad are Bad Page Splits?

    Mike Byrd

  • Jeff Moden

    SSC Guru

    Points: 995116

    Nice job, Mike.

    One of the biggest problems is that there are a huge number of common circumstances where lowing the FILL FACTOR will do absolutely nothing to prevent the page splits.  Most of these circumstances occur when you have a large number of "Append Only" Inserts followed by (usually) "ExpAnsive" Updates.  The Inserts will NOT follow the FILL FACTOR.  Instead, they will fill each page to the max (approaching 100%) as the average row size will allow.  When those same rows are Updated with "ExpAnsive" Updates, they're virtually guaranteed to split.

    That's really, really bad and not just for Selects.  Paul Randal demonstrates that a bad (he calls them "nasty") page split will take 4.5 to 43 times longer longer than a good one.  Since these types of splits are all happening on the latest data and they're all very close together at the logical end of the index, you all get massive blocking for other Inserts not to mention for Selects.  It also causes a shedload of extra activity on the log file and that can slow a whole lot more down than just things having to do with the given table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • John Mitchell-245523

    SSC Guru

    Points: 148433

    Thanks for setting up the tests and crunching the numbers, Mike!

    I just wanted to take issue with this (emphasis mine):

    Data can be added row by row to a page until the specified fill factor is reached. When that is reached the next row inserted must go to a new page.

    Shouldn't that say "until the page is full"?  My understanding is that the purpose of specifying a fill factor is to allow empty space for new rows to be written into, and fill factor is only ever observed when the index is built or rebuilt.

    John

  • BobAtDBS

    SSCarpal Tunnel

    Points: 4583

    Loved the analysis, one thing bothered me.  Either I have a gross misunderstanding or the author said something incorrectly:

    Fill factor is defined as the maximum percent of the fullness of an index page of data. Data can be added row by row to a page until the specified fill factor is reached. When that is reached the next row inserted must go to a new page.

    I thought the whole purpose of a fill factor of less than 100% was to allow the pages to "fill up" after a reorg so that you didn't get page splits immediately?  If I reorg to 80%, what do I gain when the next row is added if I can't go to 81%?


    Student of SQL and Golf, Master of Neither

  • Mike Byrd

    Ten Centuries

    Points: 1253

    All of ya’lls comments are absolutely correct.  Not sure where my brain was when I wrote the original paragraph, but obviously not in this universe.  This is what I should have said:

    Let’s discuss what constitutes a good page split and a bad page split.  Data can be added row by row to a page until it is filled (there is not enough space within the page for another row to be added).  When there is not enough space within the page a new page is added to the index.  Normally it is the next page in the extent, but if the extent is filled then a new extent (8 pages) are allocated to the index.  Fill Factor is not a player here other than it sets aside space for additional rows after an index rebuild.”

    Mike Byrd

  • Jeff Moden

    SSC Guru

    Points: 995116

    John Mitchell-245523 wrote:

    Thanks for setting up the tests and crunching the numbers, Mike!

    I just wanted to take issue with this (emphasis mine):

    Data can be added row by row to a page until the specified fill factor is reached. When that is reached the next row inserted must go to a new page.

    Shouldn't that say "until the page is full"?  My understanding is that the purpose of specifying a fill factor is to allow empty space for new rows to be written into, and fill factor is only ever observed when the index is built or rebuilt.

    John

    I totally missed that in my admittedly quick read.  What you say is 100% correct, John.  There is a very rare but useful instance where that's not correct but, when it's RBAR, it's always correct.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995116

    Mike Byrd wrote:

    All of ya’lls comments are absolutely correct.  Not sure where my brain was when I wrote the original paragraph, but obviously not in this universe.  This is what I should have said:

    Let’s discuss what constitutes a good page split and a bad page split.  Data can be added row by row to a page until it is filled (there is not enough space within the page for another row to be added).  When there is not enough space within the page a new page is added to the index.  Normally it is the next page in the extent, but if the extent is filled then a new extent (8 pages) are allocated to the index.  Fill Factor is not a player here other than it sets aside space for additional rows after an index rebuild.”

    Submit a change to Steve Jones.  I'm sure he'd be happy to accommodate.   I also recommend that the word "are" after the "(8 pages)" be an "is" because the plural of pages is parenthetical.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

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