Post reply

Index fragmentation - performance within application context

  • August 6, 2018 at 6:21 am

    #2000399

    I haven't read the whole article. The core of both statements is correct. Fragmentation can lead to extra reads on disk. The issue is, defragmentation leads to refragmentation. To really evaluate if defragmentation on a rowstore index is worth it, you need to also get a count of page splits and the waits entailed there to compare it to general I/O waits caused by extra reads. That will let you know if fragmentation is hurting you.
    Measuring just execution time to determine index size doesn't take into account blocking and waits. Not sure of the context of that statement, but it might be a poor measure, depending.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 6, 2018 at 6:39 am

    #2000404

    Grant Fritchey - Monday, August 6, 2018 6:21 AM

    The issue is, defragmentation leads to refragmentation. To really evaluate if defragmentation on a rowstore index is worth it, you need to also get a count of page splits and the waits entailed there to compare it to general I/O waits caused by extra reads. That will let you know if fragmentation is hurting you.

    Absolutely spot on, Grant!

    With the months long study/testing I've been doing on page splits and fragmentation, I've found that defragmentation can be like a bad drug habit on certain "kinds" of indexes (not to be mistaken for "types" of indexes like CI/NCI).  The more you do it, the more you need to do it.   The currently accepted "standards" for defragmentation for those types of indexes actually guarantee rampant page splits and the need for defragmentation.

    I do have a fix for those "kinds" of indexes, though.  The hard part is identifying that you actually have one of those "kinds" of indexes.  There are other "kinds" of indexes that I've identified or refconfirmed and I'm working on the methods to auto-magically identify those and what to do about them.

    The other part is that most people only test for logical fragmentation and ignore the physical fragmentation.

    --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 2 posts - 1 through 3 (of 3 total)

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