MSDNSQL Server compiles only the statement that needs to be recompiled, not the entire batch. This "statement-level recompilation" minimizes CPU time and memory during batch recompilations, and obtains fewer compile locks.
Views
* dm_exec_cached_plans is first port of call and plan_handle is the PK.
* sys.dm_exec_query_stats: Returns performance statistics for cached query plans. This contains one row per query plan so if a stored procedure or batch contains two SELECT statements you may get two rows here
* sys.dm_exec_sql_text - Returns the text of the sql statement based on the SQL handle
* sys.dm_exec_query_plan - Returns the showplan in XML format for a batch or module based on the plan handle
Ad Hoc Queries
From SQL Sever 2005 onwards, ad hoc queries will be cached although the ad hoc query is cached it requires an exact textual match to be used. Even the provided parameters of the query should be the same for reuse.
The text match is both case- and space-sensitive, even on case-insensitive servers.
Auto-parameterized queries
Simple parameterization replaces literals with a variable. E.g. WHERE x > 1. If you look at the query plan the 1 may be substiuted with a variable that looks like @x. If a subsequent query differs in only the values of the constants, it will match against the auto-parameterized query.
sp_executesql
sp_executesql procedure is used to take sql statements as strings with parameters to enforce plan reuse.
Execution Contexts
For every cached plan there can be seceral execution contexts - differing by parameter values. They are cached and reused. A single ec will only run a single batch at a time and will not be shared with other sessions\users until the current batch completes. Whenever a query plan is removed from plan cache, all of the associated execution contexts are also removed along with it. When plan cache is searched for possible plan reuse opportunities, the comparisons are against query plans, not against execution contexts. Once a reusable query plan is found, an available execution context is found (causing execution context reuse) or freshly generated.
Commands¶
Flush the cache for a db:
DBCC FLUSHPROCINDB (
)
There is no way to remove just a single query plan from the cache for SQL 2005 and below, but for SQL 2008 this is now possible: see MSDN - FreeProcCache.