Reusing Query Plans

In versions of SQL Server prior to SQL Server 7.0, only query plans for compiled objects, such as stored procedures and triggers, were kept in the procedure cache. Ad hoc query plans were immediately discarded. In SQL Server 7.0 and 2000, ad hoc query plans might remain in cache as well, if the cost to compile them is high and the query is reused frequently.

In SQL Server 2000, when SQL Server begins processing a query, it first determines if the query is an ad hoc query and if it is inexpensive to compile. If so, SQL Server doesn't bother caching the query plan in memory in order to avoid flooding the cache with query plans that probably won't be reused very often and that are cheaper simply to recompile if needed again. If an ad hoc query is not considered cheap to compile, SQL Server will allocate space in the buffer cache to store the query plan.

Plans are saved in cache along with a cost factor that reflects the cost of actually creating the plan by compiling the query. For ad hoc query plans, SQL Server sets its cost to 0, which indicates that the plan can be kicked out of the procedure cache immediately if space is needed for other plans. Until space is needed, the plan can remain in cache. When another query comes along, SQL Server checks the procedure cache to see if a query plan exists that it can reuse. If an ad hoc plan exists in memory and the query can reuse that plan, SQL Server will increment the query plan cost factor by 1. This allows ad hoc query plans that are constantly being reused to remain in the cache for a little while longer; as their cost factor increases , they are not first in the list to be removed from cache when space is needed. If the plan is reused often, for example, if the same user or other users keep resubmitting the same SQL query, the query plan will likely remain in cache.

This feature can help improve performance for complex queries that are executed frequently, because SQL Server can avoid having to compile a query plan every time it's executed if the query plan is found in memory first.

Multiple Plans in Cache

SQL Server will reuse existing query plans in cache whenever possible. Because plans are re-entrant , the same query plan can be used by multiple connections at the same time, reducing the amount of memory needed to cache the reusable query plans. However, some situations will result in multiple query plans for the same query or procedure to reside in cache. The most common cause is differences in certain SET options, database options, or configuration options that affect the way queries are processed . For example, a query might optimize differently for one session if the ANSI_NULLS option is turned on than it would for a session where it is turned off. The following list of SET options must match for a query plan to be reused by a session:

• ANSI_PADDING

• FORCEPLAN

• CONCAT_NULL_YIELDS_NULL

• ANSI_WARNINGS

• ANSI_NULLS

• QUOTED_IDENTIFIER

• ANSI_NULL_DFLT_ON

• ANSI_NULL_DFLT_OFF

If any one of these setting values does not match with the setting options for a cached plan, the session will generate a new query plan. Likewise, if the session is using a different language or DATEFORMAT setting than a cached plan, it will need to generate a new one.

Another issue that can affect whether a query plan can be reused is whether the table and view names in a query are fully qualified with a username. If the owner name must be resolved implicitly, then a plan cannot be reused. This is because a different table might be referenced based on the current user context. For example, if the user tom executes the following query

 select * from titles 

then SQL Server will first try to resolve the titles table reference by determining if the current user owns a table named titles . If no table named titles is owned by the current user, then SQL Server will look for a titles table owned by the dbo user ID. If one user owns a table called titles , his query plan will be resolved differently from a user who does not own a titles table. Because of this ambiguity, the query plan for this query cannot be reused. To avoid this ambiguity and increase the likelihood of the query being reused, fully qualify the table owner, as in the following:

 select * from dbo.titles 

Examining the Plan Cache

You can view the query plans currently in memory by executing a query against the syscacheobjects table. This table is actually a pseudotable that resides only in the master database. As a pseudotable, it takes up no space on disk and is materialized in memory only when a query is executed against it. Table 35.1 describes some of the more useful columns in the syscacheobjects table.

Table 35.1. Description of Useful Columns in the syscacheobjects Table

Column Name	Description
bucketid	The internal hash key for this plan in an internal hash table in cache, which helps SQL Server locate the plan more quickly.
cacheobjtype	The type of plan in cache.
objtype	The type of object or query for which the plan is cached.
objid	One of the main keys used for looking up a plan in the cache. This is the object ID stored in sysobjects for database objects (procedures, views, triggers, and so on). For cache objects such as ad hoc or prepared SQL, objid is an internally generated value.
dbid	The ID of the database in which the cached plan was compiled.
uid	The user ID of the creator of the plan (for ad hoc query plans and prepared plans).
refcounts	The number of other cached objects that reference this cached plan (if refcount is 1, it is the base object).
usecounts	The number of times this cached plan has been used since it was initially cached.
pagesused	The number of memory pages required to store the cached plan in cache memory.
setopts	A bitmap representing the SET option settings that affect a compiled plan. This is compared with the current session settings to determine if the plan can be reused.
langid	The ID of the language in effect for the session that created the cached plan. This is compared with the current session settings to determine if the plan can be reused.
dateformat	The date format in effect for the session that created the cached plan. This is compared with the current session settings to determine if the plan can be reused.
sql	The name of the stored procedure or the first 128 characters of the batch submitted that generated the cached plan.

The types of plans stored in the cacheobjtype can be one of the following:

• Compiled Plan ” The actual compiled plan generated that can be shared by sessions running the same procedure or query if the setopts , langid , and dateformat values match.

• Executable Plan ” The actual execution plan and the environment settings for the session that ran the compiled plan. Caching the environment settings for an execution plan makes subsequent executions more efficient. Each concurrent execution of the same compiled plan will have its own executable plan. All executable plans will be associated with a compiled plan having the same bucketid , but not all compiled plans have an associated executable plan.

• Parse Tree ” The internal parsed form of a query generated before compilation and optimization.

• Cursor Parse Tree ” The parse tree generated for a cursor query.

• Extended Proc ” The cached information for an extended stored procedure.

The type of object or query for which a plan is cached is stored in the objtype column. This column can contain one of the following values:

• Proc ” The cached plan is for a stored procedure or inline function.

• Prepared ” The cached plan is for queries submitted using sp_executesql or for queries using the prepare and execute method.

• Ad hoc query ” The cached plan is for queries that don't fall into any other category.

• ReplProc ” The cached plan is for replication agents .

• Trigger ” The cached plan is for a trigger.

• View ” The cached plan is for a view or a non-inline function. You typically will only see a parse tree for a view or non-inline function, not a compiled plan. The view or function typically does not have its own separate plan because it is expanded as part of another query.

• Table ” The cached plan is for a user or system table that has computed columns. This will typically be only a parse tree.

• Default, Check , or Rule ”, The cached plan is simply a parse tree for these types of objects because they are expanded as part of another query in which they are applied.

To determine if plans are being reused, you can examine the usecounts columns. The usecounts value is incremented each time the cached plan is looked up and reused.

TIP

If SQL Server has been running for a while with a lot of activity, the number of rows in the syscacheobjects table can become quite large. If you want to run your own tests to determine which queries get cached and when query plans are reused, you will want to clear out the cache occasionally. You can use the DBCC FREEPROCCACHE command to clear all cached plans from memory. If you want to clear only the cached plans for objects or queries in a specific database, execute the following command:

 DBCC FLUSHPROCINDB (  dbid  ) 

You should not execute these commands in your production servers because that could impact the performance of the currently running applications.