question

aderossi avatar image
aderossi asked

Plan cache reuse

From BOL: > The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched: > `SELECT * FROM Person;` > `SELECT * FROM Person.Person;` That means if I don fully quialify the object with the schema, SQL never reuse plan?
execution-planplan-cache
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
No. It's quite easy to show that plan use count increments for the first query. I think the whole paragraph is very misleading - there's no indication of what is in the plan cache before running either of those statements. What is an '*existing, unused execution plan*' ? Not sure I've seen plan cache with entries with a use count of 0? Also context is important here. If your user has a default schema of 'dbo' then the first query is actually select * from dbo.person which is entirely different from the second query - it's a different table! If the default schema is 'person' then logically the 2 queries are the same, but for plan cache reuse purposes there will be 2 distinct plans. Again easy to show by executing the 2 queries and looking in the plan cache DMVs.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.