Execution Plan reuse

Question

Execution Plan reuse

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Konstantin Reu Hall of Fame Points: 3149 More actions · Answer 2

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

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!

Konstantin Reu Hall of Fame Points: 3149 More actions · Answer 4

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