Post reply

Optimizer switching sql execution plans

  • October 16, 2019 at 6:55 pm

    #3689401

    Hi,

    I have noticed that Optimizer is switching to good and bad plans randomly after IndexRebuild and Update stats job.

    For example, for 1 week it is using good plan where procedure is taking 1 sec and after that it is switching to bad plan and procedure is taking 50 sec.

    Is this behavior related to any bug? How do we make sure optimizer not to choose bad plan?

    Thanks

  • October 17, 2019 at 7:10 pm

    #3689897

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

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

  • October 17, 2019 at 8:14 pm

    #3689910

    This is not a bug - it is related to the first query that is executed after the stats have been updated.  That first query will generate the execution plan based on the parameters entered.

    There are various methods available to address this type of issue - but it will start with reviewing the code and execution plans.  Most likely the issue can be resolved by modifying the code, but if that doesn't work you could add an OPTIMIZE FOR hint to the query.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 23, 2019 at 12:59 pm

    #3691306

    Specifically, this behaviour can indicate issues with parameter sniffing.

    There are a number of approaches to address this if it is the case, so here's a handy link to understand more about it, how to detect if this is what's happening in your case and how to deal with it : https://www.brentozar.com/sql/parameter-sniffing/

    Hope it helps.

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

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