Post reply

Single index reorg took 2 full days or more to complete

  • January 30, 2023 at 2:27 am

    #4143087

    I'm running an index maintenance job on 1 of my large database as the users recently perform massive housekeeping on it thus causing majority of the indexes become fragmented thus slowing down most of the queries running on the DB. I kick off the index maintenance job last week as we have 1 week of plant shutdown, but until now the job isn't even completed. I'm using the scripts from https://ola.hallengren.com as my index maintenance job.

    When I check the logs, I notice some indexes especially those having page count over than 10m took about 2 days to complete their reorg jobs. How is this even possible? Is there anyway I could improve it? Or instead of reorg, will it be faster if I just rebuild index instead? Mind you that during this 7 days there are no other jobs running on the DB as user applications had been turned off.

  • January 30, 2023 at 3:23 am

    #4143102

    JasonO wrote:

    Or instead of reorg, will it be faster if I just rebuild index instead?

    The answer to that question is a resounding OMG YES!!!!  Even if you're in the Full Recovery Model.  It also doesn't take much fragmentation for REORGANIZE to lose it's mind, either.  If the fragmentation is "wide spread" regardless of how little logical fragmentation there is, it can seriously cost you in time, disk activity, and log file entries.

    Having it take 2 days, though?  That sounds a bit much but I don't actually know because not only have I not used REORGANIZE on a 78GB index before, I flat out stopped using REORGANIZE at all since Monday, 18 Jan 2016.

    I've also done a shedload of testing over the years... logical fragmentation just isn't the problem that most people think it is especially with SSDs or OLTP systems that are mostly reading one row at a time.  It can make the first load to memory on spinning rust pretty bad but, once in memory, it just doesn't matter.

    Page density matters because it wastes memory but it doesn't hurt performance by much once in memory.

    I've also proven that it's better to do no index maintenance than it is to do it wrong and, if you're using REORGANIZE, there's a seriously good chance you're doing it wrong (99.9% chance in my unmeasured humble opinion).

    Ola has recently checked in a mod to his good code that checks for page density.  I'd key my Index Maintenance on that instead of logical fragmentation with the understanding that REBUILDing a fragmented index can cause a ton of page splits on the morning after because it has not room to grow if you REBUILD fragmented indexes that have a zero FillFactor.  There are two exceptions to that general rule... Sequential Silos, which fragment like crazy but never have page density issues, and "exploding broom tip" indexes ("Ever-Increasing" indexes that follow the quick pattern of Insert/Process/"ExpAnsive Update").  If you can't fix the latter so they don't fragment any more, then rebuild them with a 97% FillFactor so you know what they are and call it a day.

    Just to repeat it again, it's actually better to not do any index maintenance than it is to do it wrong and about 98% of the world is doing it wrong because they follow supposed "Best Practice that aren't and were never meant to be.  In fact, it turns out that they're a "Worst Practice" for most indexes and are the biggest reason why people insist that Random GUIDs are a fragmentation problem when, in fact, they're the very epitome of how people think an index should work (except for true append-only indexes).

    Whatever you do, I strongly recommend that you simply stop using REORGANIZE until you know a whole lot more about what it doesn't actually do and the pain it costs you and your indexes while it's not operating like you might expect it to.

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

  • January 30, 2023 at 3:29 am

    #4143103

    p.s.  If you want to see a presentation on all this, here's one that talks a lot about Random GUIDs just because they're the "poster child" for fragmentation.  It shows how to fix them for very long periods of time and also shows how the lessons learned there apply to other indexes as well as the insane load that Reorganize puts on the log file compared to Rebuilds, even in the FULL Recovery Model.  Watch it past the Q'n'A for a super interesting "out-take".

    https://www.youtube.com/watch?v=rvZwMNJxqVo

    And, if you're listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25 that come on suddenly and loudly.

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

  • January 30, 2023 at 2:27 pm

    #4143308

    Every single test I've seen over the last, almost 20 years, suggests, with the exception of Columnstore indexes, REORGANIZE is a complete waste of processing power and time. Just don't use it.

    Except on Columnstore indexes. Big exception.

    "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

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

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