SQL changing what index it uses

  • Hi, I have a database that has many inserts and was using a specific index and was humming along quite nicely. Then all of a sudden it stopped using that index and everything grinded to a holt. No changes were made on the database and it just suddenly happened at 4:21am. How or why would SQL just stop using the most effective index for that query. I have changed the stored proc to now force it to use that index and it's all good again. But why did it happen in the first place.


    Thanks,

    Kris

  • Index usage can change within a database due to several factors, but part of it can be due to the data changing and the statistics no longer reflecting the true data spread. The query optimizer may now have a different, incorrect, impression of the data layout within your tables.

    Have a look atย this article for some pointer on index maintenance.

     

  • The statistics went out of date and need to be updated. As was already stated, the data changed, the statistics changed, a new threshold was reached, and when the plan recompiled, a new, less effective, plan was formed. The plan recompiled due to data changes, but the parameter value used to compile it at 4:21AM was an outlier causing a bad parameter sniffing issue. Any, or all of these in combination, could have lead to the change.

    Index hints as a solution might work, but also could prevent the optimizer from possibly making better choices in the future. I'm very cautious about implementing those.

    "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

  • Kris-155042 wrote:

    But why did it happen in the first place.

    Heh... It's not a "Fault"... It's a "Feature" that helps keep DBAs and accidental DBAs employed. ๐Ÿ˜€

    --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 3 (of 3 total)

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