• SQL-DBA-01 - Friday, March 24, 2017 10:44 AM

    Hi Experts

    I found that suddenly for a procedure the query execution plan changed and it started timing out and taking longer than expected time. There has no aparent change which has happened to the database/proc.

    Any suggestion to check about how the procedure execution plan might have been changed and causing this effect? Or any other underlying suggestion?

    This is a part of the reason that I stopped doing any index maintenance at all on my main production server way back on the 17th of January, 2016 (more than a year ago).  Rebuilds and reorgs do a very nice job of repacking indexes to their given Fill Factor size.  If it's at 0 or 100, there's no room left on pages.  For clustered indexes that are following the "ever increasing/immutable" rules (to name 2 of the 5 rules), it's not so bad.  For Non-Clustered indexes, it's terrible.  Pages are full and the indexes are not in the same order as new inserts and so right after a rebuild/reorg, the system slows down until the page splits stop.

    I've never seen an index reorg cause a change in execution plans by itself.  Rebuilds are a different story.  They inherently rebuild stats on the index which causes a recompile of the procs that use the index.  Between the fully packed index pages causing page split on inserts and a possibly bad execution plan caused by the recompile at perhaps the worst time, it can make quite a mess as inserts take a whole lot more time and the selects have to wait on all that.

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