Execution Plan reuse

  • Nice question Steve, thanks.

    Wasn't sure between the large number of inserts & the index changes, so decided to do some research first, and then got it right.

    Logic told me that the large number of inserts had to cause a recompile, as it would eventually force an update of statistics. So should have got it without research...

  • Good question, thanks.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • I think it would be useful to tell us for which version of SQL Server this question is corresponding. I am not sure that with the new query optimizer ( corresponding to SQL Server 2014 ) the answer is the same one than for SQL Server 2012 ( the differences seem important according a session I listened in Paris SQL Server Days 2 weeks ago ).

    Have a nice day

  • patricklambin (12/17/2014)


    I think it would be useful to tell us for which version of SQL Server this question is corresponding. I am not sure that with the new query optimizer ( corresponding to SQL Server 2014 ) the answer is the same one than for SQL Server 2012 ( the differences seem important according a session I listened in Paris SQL Server Days 2 weeks ago ).

    Have a nice day

    The QO in 2014 did not change. It is the Cardinality Estimator that changed. The things that can cause a plan to be removed from cache in 2014 are the same as for 2012 and older.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • This was removed by the editor as SPAM

  • Gr8 question, very informative, thanx 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • I'm not really sure why "A large number of changes to keys caused by insert or delete operations" is a correct answer. The answer states nothing about whether the inserts and deletes are on tables used by the query. Since we are so used to trick questions, I think a lot of people would have been skeptical about this selection.

  • I'm confused by the second option, deleting an index whether it is used by the plan or not. If an index was used by the plan, wouldn't dropping it then require a recompile?

  • Iwas Bornready (12/18/2014)


    I'm confused by the second option, deleting an index whether it is used by the plan or not. If an index was used by the plan, wouldn't dropping it then require a recompile?

    The point of that option is that it is regardless of the index use. Since it is not true for both cases (used or not used) then it should not be selected.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (12/18/2014)


    Iwas Bornready (12/18/2014)


    I'm confused by the second option, deleting an index whether it is used by the plan or not. If an index was used by the plan, wouldn't dropping it then require a recompile?

    The point of that option is that it is regardless of the index use. Since it is not true for both cases (used or not used) then it should not be selected.

    This is the one that got me too. I didn't read the whole answer well enough before selecting it. Nice question. Thanks Steve.

  • I knew two and guessed the third.

    Good question, learned something as well.

    Not all gray hairs are Dinosaurs!

  • Actually, Changing the max Memory Setting drops the contents of the Procedure cache causing ALL queries to be re-complied. Also a large number of inserts, updates or deletes will not necessarily cause a re-compile. If the threashold of data doesn't cause a the resampling for the statisics then the query plan is still valid and therefore no recompilation.

    Try It !!

  • david.gugg (12/17/2014)


    Dropping any index on the table, whether it is used by the query plan or not

    This should probably say "Dropping an index on the table that is not used by the query plan." If the index is used by the query, won't it necessarily cause a recompile? 70% choose this option.

    Had the same question as even the answer say

    the two operations in the list that do not affect plans are changing memory and changing indexes not used in the plan

    does it mean than any index change will not cause recompile

    ------------------------------------------------------------------------------------

    Ashish

  • Looks like updating statistics alone does not cause a recompile, at least in SQL Server 2014:

    http://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/

    If data hasn't changed, then no recompile.

  • Here is something wrong with question.

    I picked

    "Dropping any index on the table, whether it is used by the query plan or not"

    According to MSDN https://msdn.microsoft.com/en-us/library/ms181055.aspx

    one of reason to recompile plan

    "Dropping an index used by the execution plan."

    From my sense:

    In case index is used by query plan and it dropped - query plan will be invalidated, and recompiled.

    Just checked it. EXevent shows recompile_cause = "Schema changed" as it should be.

    Were is my point ? 😉

    MS SQL 2008 MCITP x 3
    MS SQL 2012 MCSE x 2

Viewing 15 posts - 16 through 30 (of 33 total)

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