Schema qualification necessary for query plan reuse?

Question

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?

Answer 1

For a plan to be reused all of the attributes in sys.dm_exec_plan_attributes where is_cache_key=1 must be the same. A list of these is below.

acceptable_cursor_options
compat_level
date_first
date_format
dbid
dbid_execute
is_replication_specific
language_id
merge_action_type
objectid
optional_clr_trigger_dbid
optional_clr_trigger_objid
optional_spid
required_cursor_options
set_options
status
user_id

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

DBCC FREEPROCCACHE;

CREATE TABLE dbo.FooBar(X int);

EXEC('SELECT * FROM FooBar');
EXEC('SELECT * FROM FooBar');

EXEC('SELECT * FROM dbo.FooBar');
EXEC('SELECT * FROM dbo.FooBar');

And then execute the following query

SELECT usecounts,
       text,
       value AS [user_id] 
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE text LIKE 'SELECT * FROM %FooBar'  and attribute='user_id'

You will see the following results

usecounts   text                                user_id
----------- ----------------------------------- -------
2           SELECT * FROM dbo.FooBar            -2
2           SELECT * FROM FooBar                1 

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

Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.

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 the schema_id of the default schema for the executing user rather than an identifier for that specific user. Running the four EXEC statements again under a different login with default schema "dbo" gives.

usecounts   text                                user_id
----------- ----------------------------------- -------
4           SELECT * FROM dbo.FooBar            -2
4           SELECT * FROM FooBar                1 

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.

usecounts   text                                user_id
----------- ----------------------------------- -------
6           SELECT * FROM dbo.FooBar            -2
4           SELECT * FROM FooBar                1
2           SELECT * FROM FooBar                2

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.