Execution Plan Cache

A few days ago, I was not very familiar with Plan cache. I sent a question on msdn.

Some Questions about SYS. dm_exec_cached_plans and SYS. dm_exec_query_stats

The implementation plan is described in the online manual as follows:

The SQL Server Execution Plan contains the following main components:

• Query plan

  The main body of the Execution Plan is a reentrant read-only data structure, which can be used by any number of users. This is called a query plan. User context is not stored in the query plan. In-memory query plan copies can never exceed two: one copy is used for all serial execution, and the other is used for all parallel execution. Parallel copies overwrite all parallel executions, regardless of the degree of parallelism of parallel executions.

• Execution Context

  Each user performing a query has a data structure that contains the specific data (such as the parameter value) for execution. This data structure is called the execution context. You can use the Context Data Structure again. If a user executes a query and a structure is not used, the structure is reinitialized with the context of the new user.

However, in the process cache, not only the two are available.

1. Compiled plans

2. Execution Context

3. cursor

4. lexical analysis tree

(If you are interested in these four items, you can refer to "SQL Server survival cheats" chr4.1.

Regarding the reuse of execution context, the SQL Server survival tips are not very clear or I have not understood what it means.

The following are the execution context issues mentioned in inside Microsoft SQL Server 2005: Query tuning and optimization.

Execution plans

Executable plans, or execution contexts, are considered to be dependent on compiled plans and do not show up inSYS. dm_exec_cached_plansView. executable plans are runtime objects created when a compiled plan is executed. just as for compiled plans, executable plans can be object plans stored in the object store, or SQL plans, stored in the SQL store. each executable plan exists in the same cache store as the compiled plan to which it is dependent. executable plans contain the participating runtime information for one execution of a compiled plan, and include the actual runtime parameters, any local variable information, object IDs for objects created at run time, the user ID, and information about the currently executing statement in the batch.

When SQL server starts executing a compiled plan, it generates an executable plan from that compiled plan. each individual statement in a compiled plan gets its own executable plan, which you can think of as a runtime query plan. unlike compiled plans, executable plans are for a single session. for example, if there are 100 users simultaneously executing the same batch, there will be 100 executable plans for the same compiled plan. executable plans can be regenerated from their associated compiled plan, and they are relatively inexpensive to create. later in this section, we'll look atSYS. dm_exec_cached_plan_dependent_objectsView, which contains information about your executable plans.

In the SQL Server survival tips, the execution context can be reused. But the premise is not concurrency

Insider:

SYS. dm_exec_cached_plan_dependent_objectsTo query the execution context, which stores the execution context of plan_handle (of course, there is CLR ).

 

I want to explain the problem on msdn.

Environment: Microsoft SQL Server 2008 R2 (RTM)-10.50.1600.1 (Intel x86) APR 2 2010 15:53:02 copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 5.2 <x86> (build 3790: service Pack 2)
Test Database: adventureworks
When I set mandatory parameters for the adventureworks database.
Run the following code one by one in three sessions:

Select soh. salesordernumber,
SOD. productid
From sales. salesorderheader as Soh
Inner join sales. salesorderdetail as SOD
On soh. salesorderid = sod. salesorderid
Where soh. salesordernumber = 'so43662'
Select soh. salesordernumber,
SOD. productid
From sales. salesorderheader as Soh
Inner join sales. salesorderdetail as SOD
On soh. salesorderid = sod. salesorderid
Where soh. salesordernumber = 'so58928'
Select soh. salesordernumber,
SOD. productid
From sales. salesorderheader as Soh
Inner join sales. salesorderdetail as SOD
On soh. salesorderid = sod. salesorderid
Where soh. salesordernumber = 'so70907'
Query again

The following SQL statement:
Select B. Text, A. plan_handle, A. SQL _handle from SYS. dm_exec_query_stats
Cross apply SYS. dm_exec_ SQL _text (A. SQL _handle) B
-- Cross apply SYS. dm_exec_query_plan (A. plan_handle) c
Where B. Text like '% select % salesorderdetail %' and B. Text not like '%'

Select B. Text, C. *, A. plan_handle from SYS. dm_exec_cached_plans
Cross apply SYS. dm_exec_ SQL _text (A. plan_handle) B
Cross apply SYS. dm_exec_query_plan (A. plan_handle) c
Where B. Text like '% select % salesorderdetail %' and B. Text not like '%'
It is reasonable to say that only one plan is generated when the forced parameters are used. However, when I use the preceding SQL query, the following results are sent:

SYS. dm_exec_cached_plans: returns a row for each query plan cached by SQL Server to accelerate query execution.

SYS. dm_exec_query_stats: return the aggregate performance statistics of the cache query plan. Each query statement in the cache plan corresponds to a row in this view, and the row survival is associated with the plan itself.

Why do four messages appear when SYS. dm_exec_cached_plans is used, but only one message appears when SYS. dm_exec_query_stats is used?

Explanation:

Comparison

SYS. dm_exec_cached_plan_dependent_objectsCan usecounts of SYS. dm_exec_cached_plans tell me how SQL Server obtains the compiled plan and execution context.

Next we will use the test code below to test and see if usecount has changed. Can we tell you how it works?

Run the first time:

Run the second time:

 

SYS. the specific SQL usage in dm_exec_cached_plans is 1, the pre-compiled plan is 3, the cache type is complied plan, and the execution context pre-compiled is 3, the others are 1. Why is pre-compilation 3 because the pre-compilation plan has been used. Only one parameter is required. Why is there three more? The answer I get from msdn is that the three items are false, only Shell plans. I think this explanation is not detailed. Compare the results of the first run and the results of the second run, force parameterization, the pre-compilation plan has been used at the time of submission, and three compiled plans have been generated according to the pre-compilation. When you run the second time, you will not reference the plan in cache_plan, but it will reuse S.

 

Ys. dm_exec_cached_plan_dependent_objectsThe execution context in it, so it is Shell plan, and the pre-compiled plan is actually reused.