Execution plan components, components, aging

first, the implementation of the plan buffer

　　The optimizer-generated execution plan is saved in a special part of the SQL Server memory pool, known as a plan buffer or process buffer. process buffering is part of the SQL Server cache. Saving a plan in buffering allows SQL Server to avoid having to re-submit the same query again through the entire query optimization process. SQL Server supports different techniques, such as planning for buffering aging, and scheduling buffer types to increase the reusability of cached plans. It also holds two binary values called hash and query plan hash.

Second, the implementation plan components

The optimizer-generated execution plan consists of two components:

• Query plan: This means specifying the physical operation commands required to execute the query;
• Execution context: This maintains a variable portion of the query in the context of a given user;

　　1. Query plan

A query plan is a reentrant, read-only data structure with commands to specify all the physical operations required to execute the query. The Reentrant property enables the query plan to be accessed concurrently by multiple connections. Physical operations include the size of the tables and indexes that are accessed, the way and order in which they are accessed, the types of connection operations performed between multiple tables, and so on. The user context is not saved in the query plan. For a single query, it may be a copy of two query plans: Serial plan and parallel plan.

　　2. Execution context

The execution context is another data structure that maintains a mutable part of the query. Although the server records the execution plan in the process buffer, these plans are context-independent. Therefore, each user who executes the query will have a separate execution context that holds the data that is dedicated to its execution, such as parameter values and connection details.

Iii. Ageing of the implementation plan

The process buffer is part of the SQL Server cache and the SQL Server cache also saves the data page. As new execution plans are added to the process buffers, process buffering will grow larger, affecting the storage of useful data pages in memory. To avoid this situation, the SQL Server dynamic control process buffers the execution of planned storage, retains the most common execution plan, and discards a schedule that is not used for a period of time.

SQL Server associates the execution plan with a life field to record the frequency of execution plan reuse. When the execution plan is generated, the lifetime field initial value is populated with the cost of generating the plan . Complex queries that require large-scale optimizations will have a lifetime field value that is higher than simple query functionality.

At fixed intervals, the lifetime field of all execution plans in the process buffer is determined by the lazy writer process of SQL Server (this process manages most of the background processes in SQL Server). If the execution plan has not been reused for a long time, the life field is eventually reduced to 0. Once the life of the Execution plan field is 0, the plan becomes a candidate to be removed from memory. When the memory pressure increases to not enough free memory to service the new request, SQL Server removes the plan from the process buffer for all life fields to 0. However, if the system has enough memory and has free memory pages to serve new requests, the execution plan with a life field of 0 can be kept in the process buffer for a long time so that they can be reused when needed.

In addition to aging, the lifetime field increases the cost value of the build schedule each time the execution plan is reused. For example, suppose there are two execution plans, with a build cost of 100 and 10. They start with a lifetime field value of 100 and 10, respectively. If two plans are immediately reused, their lifespan increases to 200 and 20, respectively. Because of these life field values, the lazy writer process drops the second planned cost value to 0 much easier than the first plan, unless the second plan is more often reused. Therefore, even if a more expensive plan is reused less frequently than the more economical plan, because of the impact of the overhead on the life field, the expensive plan may still remain non-0 for a longer period of time.

Iv. Analysis of the execution plan buffer

You can access the dynamic management view Sys.dm_exec_cached_plans to get a lot of information about the execution plan in the process buffer, such as:

SELECT *  from Sys.dm_exec_cached_plans

Here's a look at what useful information this view provides:

Column Name	Describe
Refcounts	The number of other objects in the buffer that refer to this plan
Usecounts	Number of times the object has been added to the buffer plan
Size_in_bytes	The size of the plan saved in the buffer
Cacheobjtype	Buffered Object type: Compiled Plan: a complete implementation program; Compiled plan stub: the tag used for ad hoc queries; Parse Tree: The plan for accessing the view;
ObjType	The type of object that generated the plan: Proc (process); Prepared (pre-defined); Ad hoc (impromptu); View (views);
Plan_handle	A plan handle, an identifier for this plan in memory, used to retrieve the query text and execution plan

Details can be learned here: http://msdn.microsoft.com/zh-tw/library/windowsazure/hh977100.aspx

Only partial information can be obtained by using sys.dm_exec_cached_plans alone. The next two parts are just as important. using the dynamic management functions Sys.dm_exec_query_plans and Sys.dm_exec_cached_plans combinations, the XML execution plan itself can be obtained so that it can be displayed and processed. If you then introduce Sys.dm_exec_sql_text, you can also retrieve the original query text. this looks useless when running the known query in the example here, but when you go to your production system and start reading the execution plan from the buffer, it is convenient to get the original query, in order to get a detailed performance metric for the buffered plan, you can use the Sys.dm_exec_query_ Stats returns data. In addition to other data, query hash and query plan hash are stored in this DMF.

Execution plan components, components, aging