执行计划的缓存

前些日子，对 plan cache 不太熟悉。就在msdn 发了一个问题。

关于sys.dm_exec_cached_plans 和sys.dm_exec_query_stats 一些疑问

联机手册里面对执行计划的解释如下：

SQL Server 执行计划包含下列主要组件：

• 查询计划
  
  执行计划的主体是一个重入的只读数据结构，可由任意数量的用户使用。这称为查询计划。查询计划中不存储用户上下文。内存中查询计划副本永远不超过两个：一个副本用于所有的串行执行，另一个用于所有的并行执行。并行副本覆盖所有的并行执行，与并行执行的并行度无关。
  
  
• 执行上下文
  
  每个正在执行查询的用户都有一个包含其执行专用数据（如参数值）的数据结构。此数据结构称为执行上下文。执行上下文数据结构可以重新使用。如果用户执行查询而其中的一个结构未使用，将会用新用户的上下文重新初始化该结构。
  
  

但是其实，在过程缓存中，并不是只有这2个。

1.已编译的计划

2.执行上下文

3.游标

4.词法分析树

（大家如果对这4个比较有兴趣可以看 《sql server 求生秘籍》 chr4.1）里面有比较多的解释。

 关于执行上下文的复用，sql server 求生秘籍也讲的不是很清楚或者我还没理解它要表达的意思。

下面是：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 in thesys.dm_exec_cached_plans view. 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 particular 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 at the sys.dm_exec_cached_plan_dependent_objects view, which contains information about your executable plans.

sql server 求生秘籍中说执行上下文能够复用。但是前提是不是并发的

insider说可以通过 

sys.dm_exec_cached_plan_dependent_objects 来查执行上下文，里面存放的是对于 plan_handle的执行上下文（当然还有clr的）。

 

我再 msdn的问题，今天想拿出来解释一下。

环境：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) 
测试数据库：AdventureWorks
当我给 AdventureWorks 数据库设置强制参数 化后。
使用如下代码分别在3个session中逐个运行

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'
再查询

如下sql：
SELECT b.text,a.plan_handle,a.sql_handle FROM sys.dm_exec_query_stats   a
    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  a
    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 ' %'
按理来说 已经使用了强制化参数，那么应该只会产生一条 计划。 但是当我使用上面的sql 查询是 发下 以下结果

• 

sys.dm_exec_cached_plans ：针对 SQL Server 为了加快查询执行而缓存的每个查询计划返回一行

sys.dm_exec_query_stats：返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。

为什么 使用sys.dm_exec_cached_plans 会出现4条但是用 sys.dm_exec_query_stats 只出现1条呢

解释：

     

我们对比 

sys.dm_exec_cached_plan_dependent_objects 和 sys.dm_exec_cached_plans 的usecounts 是不是能够从中猜测出，sql server 是如何获取已编译计划的，和执行上下文的。

　　下面 我们用 我下面的测试代码来测试 看看 usecount 有什么变化，我们是不是从中能探知一些原理

第一遍运行：

第二遍运行：

 

sys.dm_exec_cached_plans中具体的sql 使用都是1，那个预编译的计划使用是3，cache类型是 complied plan，对于的也生产了执行上下文预编译的是3，其他的是1。为啥预编译的是3，因为已经使用了预编译产生的已编译计划。既然已经强制参数化了，应该只有1条才对。为什么是多了3条呢？我再msdn得到的答案是这3条是假的只是shell plan。这样的解释让我觉得有点不详细。比较第一遍运行的结果和第二遍运行的结果，强制参数化，在提交的时候已经使用了预编译计划，根据预编译又产生了3条已编译计划。当你第二遍运行的时候是不会去引用cache_plan 里面的计划，但是它复用了s

 

ys.dm_exec_cached_plan_dependent_objects 里面的执行上下文，所以说是shell plan，真正被复用的是 预编译的计划。