While reading this article about Plan Caching in SQL server, I came across a tidbit I was unaware of:
… for reuse it is necessary that the objects that the batch references do not require name resolutions. For example, Sales.SalesOrderDetail does not require name resolution, whereas SalesOrderDetail does because there could be tables named SalesOrderDetail in multiple schemas. In general, two-part object names (that is, schema.object) provide more opportunities for plan reuse.
I'm looking for some clarification about how important it is to use two-part object names, since "In general, two-part object names provide more opportunities for plan reuse.", yet it starts by saying it's necessary.
More specifically, most of the stored procedures I deal with are in the dbo schema and reference only dbo objects, without specifying the dbo prefix. Are these prevented from reusing cached query plans, even with everything using the default schema?
Best Answer
For a plan to be reused all of the attributes in
sys.dm_exec_plan_attributes
whereis_cache_key=1
must be the same. A list of these is below.The one affected by using two part names is
user_id
If you try the following under the credentials of a user with default schema
dbo
And then execute the following query
You will see the following results
This shows that both plans got re-used when the identical statement got run for the second time. The docs for sys.dm_exec_plan_attributes explain for the
user_id
This appears to be incorrect! It seems from my testing that the value it actually uses for the
user_id
in the second case is theschema_id
of the default schema for the executing user rather than an identifier for that specific user. Running the fourEXEC
statements again under a different login with default schema "dbo" gives.Showing the plans for both versions of the query were able to be re-used between users. Finally running the four
EXEC
statements again under a third login with default schema "guest" gives.Showing that the plan for the
dbo
qualified query was successfully shared between the users with different default schemas but the non schema qualified query needed a new plan compiled.If you don't see this sharing happening ensure that all logins you are testing have the same
set_options
,language_id
,date_first
,date_format
as these are among the cache keys listed at the beginning and any differences in those will prevent the plans being reused between sessions.