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?


1 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.


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


CREATE TABLE dbo.FooBar(X int);


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

And then execute the following query

SELECT usecounts,
       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.

  • 2
    @Bort - Actually I've just realised that it depends on the default schema not just the user_id. In my original test I had the second user WITH DEFAULT_SCHEMA=[guest]. As long as two different users have the same default schema it appears they can share each other's plans. Jun 4, 2012 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.