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:
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
The types of plans stored in the cacheobjtype can be one of the following:
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:
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.