Post reply

Execution Plan reuse

  • January 10, 2016 at 3:47 am

    #1850799

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

    MSDN: Dropping an index used by the execution plan.

    Dropping any index on the table won't cause a recompile. Only dropping one that is used by the query plan. Hence the option you selected is wrong.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 11, 2016 at 12:28 pm

    #1851096

    GilaMonster (1/10/2016)

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

    MSDN: Dropping an index used by the execution plan.

    Dropping any index on the table won't cause a recompile. Only dropping one that is used by the query plan. Hence the option you selected is wrong.

    Thank you for replying on so old topic ! Did not expect....

    I try to catch up on missed QotT.

    I see contradiction. Any index -> means indexes are used by cached plan as well as unused indexes by cached plan.

    What I mean.

    This particular question can be broken into parts. Originally, it sounds

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

    Here are two logical parts:

    Question: Dropping any index on the table, when it is used by the query plan.

    and

    Question: Dropping any index on the table, when it is not used by the query plan.

    Between these two is "OR". I mean if either of both is correct -> initial statement is correct top. Simple.

    Regarding this "branch"

    Question: Dropping any index on the table, when it is not used by the query plan.

    I pointed out, that dropping index used by query plan always leads to recompilation. (Second one is correct)

    Therefore I conclude that this answer is correct by it's definition.

    PS. Probably question was formed wrong ? Author tried to ask "Does drop any index on table always cause recompilation ? ". In this case I'd say "No".

    Could you have a look at this old controversial question again ?

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

  • January 11, 2016 at 1:13 pm

    #1851110

    I understand your reasoning, but that's not the correct scope for the OR in that English construction.

    The construction "X, whether Y or not-Y", is equivalent to "IF (y OR not-Y), THEN X". It is not[/i] equivalent to "(IF y THEN x) OR (IF not-y THEN x)". Put in those formal logic symbols we all love so much, it is the first of the following two:

    (Y ? ¬Y)?X This correctly represents the English "X, whether Y or not-Y"

    (Y?X) ? (¬Y?X) This is an incorrect representation of the English "X, whether Y or not-Y"

    That first is actually just equivalent to X, which is not surprising, because that English construction is used to emphasize that the truth value of Y is irrelevant to the truth value of X.

    That means that the answer "Dropping an index on the table invalidates the plan, whether the index is used by the plan or not", is equivalent to "Dropping an index on the table invalidates the plan", which is indeed incorrect.

    Hopefully that helps clear up the confusion about the wording, and doesn't just muddy the waters 🙂

    Cheers!

  • January 11, 2016 at 1:21 pm

    #1851113

    Jacob Wilkins (1/11/2016)

    I understand your reasoning, but that's not the correct scope for the OR in that English construction.

    The construction "X, whether Y or not-Y", is equivalent to "IF (y OR not-Y), THEN X". It is not[/i] equivalent to "(IF y THEN x) OR (IF not-y THEN x)". Put in those formal logic symbols we all love so much, it is the first of the following two:

    (Y ? ¬Y)?X This correctly represents the English "X, whether Y or not-Y"

    (Y?X) ? (¬Y?X) This is an incorrect representation of the English "X, whether Y or not-Y"

    That first is actually just equivalent to X, which is not surprising, because that English construction is used to emphasize that the truth value of Y is irrelevant to the truth value of X.

    That means that the answer "Dropping an index on the table invalidates the plan, whether the index is used by the plan or not", is equivalent to "Dropping an index on the table invalidates the plan", which is indeed incorrect.

    Hopefully that helps clear up the confusion about the wording, and doesn't just muddy the waters 🙂

    Cheers!

    Jacob Wilkins, thank so much !

    I got where I went wrong 😉 It's good lesson as well as your explanation.

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

Viewing 4 posts - 31 through 33 (of 33 total)

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