Post reply

Index Fragmentation Explained: Page Splits, Logical Reads, and What to Do

  • June 22, 2026 at 12:00 am

    #4859152

    Comments posted to this topic are about the item Index Fragmentation Explained: Page Splits, Logical Reads, and What to Do

  • June 23, 2026 at 12:10 am

    #4871407

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • June 24, 2026 at 12:07 am

    #4872236

    First, I appreciate anyone that steps up to the proverbial plate to share knowledge.  Thank you for that and thank you for your effort.

    Unfortunately, there's a huge amount of very old and very wrong information out there.  A classic example is what you're cited Random GUIDs for and that's just one example of many...

    Microsoft finally removed the 5/30 recommendation from their documentation on the 20th of April, 2001.  I say "finally" because , back in 2009, the guy that came up with those numbers, Mr. Paul Randal himself, posted the following article...

    https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

    Notice the very last line of his article where he says "In the meantime, take those numbers with a pinch of salt and don’t treat them as absolute.  I also tell you that a lot of us followed the bad 5/30 example that was never meant to be a "best practice".

    I haven't done "regular" index maintenance since Monday, the 18th of January, 2016.  That's the day after the 5/30 index maintenance caused my production server to generate so many pages splits for about 8 hours after the maintenance that the server was virtually unusable and we simply had to wait for the page splits to stop on their own.

    And waiting for higher percentage fragmentation to do index maintenance is not going to help.  You're just delaying the inevitable.

    And if you're using REORGANIZE on just about anything, you're actually perpetuating fragmentation because REORGANIZE removes free space from indexes when you need it the most.

    With that, here's a recorded presentation that uses Random GUIDs to demonstrate with because they're the proverbial "Poster Child" for fragmentation of all types.  It proves that they not the problem and that it's our index maintenance that actually makes Random GUIDs behave so badly.  In fact, except for being so wide byte-wise, Random GUIDs are actually the epitome of how people think indexes should behave.

    And, no... I'm NOT recommending that you use Random GUIDs everywhere or anywhere... Like I said above, I'm just using them to prove how bad people understanding of index maintenance and REORGANIZE actually is.   There's also a part that shows the misery you can be creating if you create ever-increasing keyed indexes without understand the ramifications of "ExpAnsive" updates.

    --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.

    Change is inevitable... Change for the better is not.

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

  • June 24, 2026 at 12:18 am

    #4872238

    (continued from above)

    Here's the presentation... and understand that it "just the beginning".  One of the biggest things that you should get out of it is that it's better to do NO index maintenance that it is to do it wrong and if you're following anything relatively general (like but not limited to the 5/30 and similar methods) for all of your indexes, you are making a terrible mistake.

    https://www.youtube.com/watch?v=rvZwMNJxqVo&t=81s

    Heh... and here's another test... explain how an index can have more than 99% logical fragmentation but also have more than 99% page density.

    • This reply was modified 2 weeks, 4 days ago by Jeff Moden.
    • This reply was modified 2 weeks, 4 days ago by Jeff Moden.

    --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.

    Change is inevitable... Change for the better is not.

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

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

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