|
The optimizer can use cached plans when executing a query.
The plan cache is a per-connection cache of the data structures used to execute an access plan, with the goal to reuse a plan when it is efficient to do so. Reusing a cached plan involves looking up the plan in the cache, but typically, this is substantially faster than reprocessing a statement through all of the query processing phases.
Optimization at query execution time allows the optimizer to choose a plan based on the current system state, on the values of current selectivity estimates, and on estimates that are based on the values of host variables. For queries that are executed frequently, the cost of query optimization can outweigh the benefits of optimizing at execution time. To reduce the cost of optimizing these statements repeatedly, the database server considers caching the execution plans for reuse later.
For client statements, the lifetimes of cached execution plans are limited to the lifetimes of the corresponding statements and are dropped from the plan cache when the client statements are dropped. The lifetimes of client statements (and any corresponding execution plans) can be extended by a separate cache of prepared client statements, which is controlled by the max_client_statements_cached option. Depending on how your system is configured, client statements may be cached in a parameterized form to increase the chances that corresponding execution plans will be reused.
The maximum number of plans to cache is specified with the max_plans_cached option.
Use the sp_plancache_contents system procedure to examine the current contents of your plan cache.
You can use the QueryCachedPlans statistic to show how many query execution plans are currently cached. This property can be retrieved using the CONNECTION_PROPERTY function to show how many query execution plans are cached for a given connection, or the DB_PROPERTY function can be used to count the number of cached execution plans across all connections. This property can be used in combination with QueryCachePages, QueryOptimized, QueryBypassed, and QueryReused to help determine the best setting for the max_plans_cached option.
You can use the database or QueryCachePages connection property to determine the number of pages used to cache execution plans. These pages occupy space in the temporary file, but are not necessarily resident in memory.
The database server decides which plans to cache and which plans to avoid caching. Plan caching policies define criteria to meet and actions to take when evaluating statements and their plans. The policies are at work behind the scenes, governing plan caching behavior. For example, a policy might determine the number of executions (training period) a statement must go through, and the results to look for in the resulting plans, to qualify a plan for caching and reuse.
After a qualifying statement has been executed several times by a connection, the database server may decide to build a reusable plan. If the reusable plan has the same structure as the plans built in previous executions of the statement, the database server adds the reusable plan to the plan cache. The execution plan is not cached when the risks inherent in not optimizing on each execution outweighs the savings from avoiding optimization.
Query execution plans are not cached for queries that have long running times because the benefits of avoiding query optimization are small compared to the total cost of the query. Additionally, the database server may not cache plans for queries that are very sensitive to the values of their host variables.
If an execution plan uses a materialized view that was not referenced by the statement, and the materialized_view_optimization option is set to something other than Stale, then the execution plan is not cached and the statement is optimized again the next time it is executed.
To minimize cache usage, cached plans may be stored to disk if they are used infrequently. Also, the optimizer periodically re-optimizes queries to verify that the cached plan is still efficient.
The database server can parameterize qualifying client statements to enhance plan caching opportunities. Parameterized statements use placeholders that act like variables that are evaluated at execution time. Although parameterization may introduce a very small amount of performance overhead for some statements, the parameterized statement text is more general and can be matched to more SQL queries. As a result, statement parameterization can improve the efficiency of plan caching because all SQL queries that match the parameterized statement can share the same cached plan.
The parameterization of statements is controlled by the parameterization_level option. This option can be set to allow the database server to make decisions about when to parameterize (Simple), to parameterize all statements as soon as possible (Forced), or not to parameterize any statement (Off). The default is to allow the database server to decide when to parameterize statements (Simple).
Obtain the parameterization behavior that is in place by querying the parameterization_level connection property. If parameterization is enabled, obtain the number of prepare requests for parameterized statements that the current connection has issued to the database server by querying the ParameterizationPrepareCount connection property.