Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 4 '12 at 9:15

This question came from our site for professional and enthusiast programmers.

    
"Are these prevented from reusing cached query plans, even with everything using the default schema?" Yes. –  Damien_The_Unbeliever Jun 1 '12 at 14:49
    
@Damien_The_Unbeliever - Actually the answer is "no" unless you have users set up with different default schemas. –  Martin Smith Jun 5 '12 at 11:21

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
Makes things clear, thanks for your efforts! –  Bort Jun 1 '12 at 20:16
    
@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. –  Martin Smith Jun 4 '12 at 10:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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