谈一谈SQL Server中的执行计划缓存
[转]http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServer.html
简介
我们平时所写的SQL语句本质只是获取数据的逻辑，而不是获取数据的物理路径。当我们写的SQL语句传到SQL Server的时候，查询分析器会将语句依次进行解析（Parse）、绑定（Bind）、查询优化（Optimization，有时候也被称为简化）、执行（Execution）。除去执行步骤外，前三个步骤之后就生成了执行计划，也就是SQL Server按照该计划获取物理数据方式，最后执行步骤按照执行计划执行查询从而获得结果。但查询优化器不是本篇的重点，本篇文章主要讲述查询优化器在生成执行计划之后，缓存执行计划的相关机制以及常见问题。
为什么需要执行计划缓存
从简介中我们知道，生成执行计划的过程步骤所占的比例众多，会消耗掉各CPU和内存资源。而实际上，查询优化器生成执行计划要做更多的工作，大概分为3部分：
- 首先，根据传入的查询语句文本，解析表名称、存储过程名称、视图名称等。然后基于逻辑数据操作生成代表查询文本的树。
- 第二步是优化和简化，比如说将子查询转换成对等的连接、优先应用过滤条件、删除不必要的连接（比如说有索引，可能不需要引用原表）等。
- 第三步根据数据库中的统计信息，进行基于成本（Cost-based）的评估。
上面三个步骤完成之后，才会生成多个候选执行计划。虽然我们的SQL语句逻辑上只有一个，但是符合这个逻辑顺序的物理获取数据的顺序却可以有多条，打个比方，你希望从北京到上海，即可以做高铁，也可以做飞机，但从北京到上海这个描述是逻辑描述，具体怎么实现路径有多条。那让我们再看一个SQL Server中的举例，比如代码清单1中的查询。
1: SELECT *
2: FROM A INNER JOIN B ON a.a=b.b
3: INNER JOIN C ON c.c=a.a
代码清单1.
对于该查询来说，无论A先Inner join B还是B先Inner Join C，结果都是一样的，因此可以生成多个执行计划，但一个基本原则是SQL Server不一定会选择最好的执行计划，而是选择足够好的计划，这是由于评估所有的执行计划的成本所消耗的成本不应该过大。最终，SQL Server会根据数据的基数和每一步所消耗的CPU和IO的成本来评估执行计划的成本，所以执行计划的选择重度依赖于统计信息，关于统计信息的相关内容，我就不细说了。
对于前面查询分析器生成执行计划的过程不难看出，该步骤消耗的资源成本也是惊人的。因此当同样的查询执行一次以后，将其缓存起来将会大大减少执行计划的编译，从而提高效率，这就是执行计划缓存存在的初衷。
执行计划所缓存的对象
执行计划所缓存的对象分为4类，分别是：
- 编译后的计划：编译的执行计划和执行计划的关系就和MSIL和C#的关系一样。
- 执行上下文：在执行编译的计划时，会有上下文环境。因为编译的计划可以被多个用户共享，但查询需要存储SET信息以及本地变量的值等，因此上下文环境需要对应执行计划进行关联。执行上下文也被称为Executable Plan。
- 游标：存储的游标状态类似于执行上下文和编译的计划的关系。游标本身只能被某个连接使用，但游标关联的执行计划可以被多个用户共享。
- 代数树：代数树（也被称为解析树）代表着查询文本。正如我们之前所说，查询分析器不会直接引用查询文本，而是代数树。这里或许你会有疑问，代数树用于生成执行计划，这里还缓存代数树干毛啊？这是因为视图、Default、约束可能会被不同查询重复使用，将这些对象的代数树缓存起来省去了解析的过程。
比如说我们可以通过dm_exec_cached_plans这个DMV找到被缓存的执行计划，如图1所示。
图1.被缓存的执行计划
那究竟这几类对象缓存所占用的内存相关信息该怎么看呢？我们可以通过dm_os_memory_cache_counters这个DMV看到，上述几类被缓存的对象如图2所示。
图2.在内存中这几类对象缓存所占用的内存
另外，执行计划缓存是一种缓存。而缓存中的对象会根据算法被替换掉。对于执行计划缓存来说，被替换的算法主要是基于内存压力。而内存压力会被分为两种，既内部压力和外部压力。外部压力是由于Buffer Pool的可用空间降到某一临界值（该临界值会根据物理内存的大小而不同，如果设置了最大内存则根据最大内存来）。内部压力是由于执行计划缓存中的对象超过某一个阈值，比如说32位的SQL Server该阈值为40000，而64位中该值被提升到了160000。
这里重点说一下，缓存的标识符是查询语句本身，因此select * from SchemaName.TableName和Select * from TableName虽然效果一致，但需要缓存两份执行计划，所以一个Best Practice是在引用表名称和以及其他对象的名称时，请带上架构名称。
基于被缓存的执行计划对语句进行调优
被缓存的执行计划所存储的内容非常丰富，不仅仅包括被缓存的执行计划、语句，还包括被缓存执行计划的统计信息，比如说CPU的使用、等待时间等。但这里值得注意的是，这里的统计只算执行时间，而不算编译时间。比如说我们可以利用代码清单2中的代码根据被缓存的执行计划找到数据库中耗时最长的20个查询语句。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
AS DECIMAL(28, 2)) AS [Average Duration (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC
代码清单2.通过执行计划缓存找到数据库总耗时最长的20个查询语句
上面的语句您可以修改Order By来根据不同的条件找到你希望找到的语句，这里就不再细说了。
相比较于无论是服务端Trace还是客户端的Profiler，该方法有一定优势，如果通过捕捉Trace再分析的话，不仅费时费力，还会给服务器带来额外的开销，通过该方法找到耗时的查询语句就会简单很多。但是该统计仅仅基于上次实例重启或者没有运行DBCC FreeProcCache之后。但该方法也有一些弊端，比如说：
- 类似索引重建、更新统计信息这类语句是不缓存的，而这些语句成本会非常高。
- 缓存可能随时会被替换掉，因此该方法无法看到不再缓存中的语句。
- 该统计信息只能看到执行成本，无法看到编译成本。
- 没有参数化的缓存可能同一个语句呈现不同的执行计划，因此出现不同的缓存，在这种情况下统计信息无法累计，可能造成不是很准确。
执行计划缓存和查询优化器的矛盾
还记得我们之前所说的吗，执行计划的编译和选择分为三步，其中前两步仅仅根据查询语句和表等对象的metadata，在执行计划选择的阶段要重度依赖于统计信息，因此同一个语句仅仅是参数的不同，查询优化器就会产生不同的执行计划，比如说我们来看一个简单的例子，如图3所示。
图3.仅仅是由于不同的参数，查询优化器选择不同的执行计划
大家可能会觉得，这不是挺好的嘛，根据参数产生不同的执行计划。那让我们再考虑一个问题，如果将上面的查询放到一个存储过程中，参数不能被直接嗅探到，当第一个执行计划被缓存后，第二次执行会复用第一次的执行计划！虽然免去了编译时间，但不好的执行计划所消耗的成本会更高！让我们来看这个例子，如图4所示。
图4.不同的参数，却是完全一样的执行计划！
再让我们看同一个例子，把执行顺序颠倒后，如图5所示。
图5.执行计划完全变了
我们看到，第二次执行的语句，完全复用了第一次的执行计划。那总会有一个查询牺牲。比如说当参数为4时会有5000多条，此时索引扫描应该最高效，但图4却复用了上一个执行计划，使用了5000多次查找！！！这无疑是低效率的。而且这种情况出现会非常让DBA迷茫，因为在缓存中的执行计划不可控，缓存中的对象随时可能被删除，谁先执行谁后执行产生的性能问题往往也让DBA头疼。
由这个例子我们看出，查询优化器希望尽可能选择高效的执行计划，而执行计划缓存却希望尽可能的重用缓存，这两种机制在某些情况会产生冲突。
在下篇文章中，我们将会继续来看由于执行计划缓存和查询分析器的冲突，以及编译执行计划所带来的常见问题和解决方案。
将执行缓存考虑在内时的流程
上篇文章中提到了查询优化器解析语句的过程，当将计划缓存考虑在内时，首先需要查看计划缓存中是否已经有语句的缓存，如果没有，才会执行编译过程，如果存在则直接利用编译好的执行计划。因此，完整的过程如图1所示。
图1.将计划缓存考虑在内的过程
图1中我们可以看到，其中有一步需要在缓存中找到计划的过程。因此不难猜出，只要是这一类查找，一定跑不了散列（Hash）的数据结构。通过sys.dm_os_memory_cache_hash_tables这个DMV可以找到有关该Hash表的一些信息，如图2所示。这里值得注意的是，当执行计划过多导致散列后的对象在同一个Bucket过多时，则需要额外的Bucket，因此可能会导致查找计划缓存效率低下。解决办法是尽量减少在计划缓存中的计划个数，我们会在本文后面讨论到。
图2.有关存储计划缓存的HashTable的相关信息
当出现这类问题时，我们可以在buckets_avg_scan_miss_length列看出问题。这类情况在缓存命中率（SQL Server: Plan Cache-Cache Hit Ratio）比较高，但编译时间过长时可以作为考虑对象。
参数化和非参数化
查询计划的唯一标识是查询语句本身，但假设语句的主体一样，而仅仅是查询条件谓词不一样，那在执行计划中算1个执行计划还是两个执行计划呢？It’s Depends。
假设下面两个语句,如图3所示。
图3.仅仅谓词条件不一样的两个语句
虽然执行计划一样，但是在执行计划缓存中却会保留两份执行计划，如图4所示。
图4.同一个语句，不同条件，有两份不同的执行计划缓存
我们知道，执行计划缓存依靠查询语句本身来判别缓存，因此上面两个语句在执行计划缓存中就被视为两个不同的语句。那么解决该问题的手段就是使得执行计划缓存中的查询语句一模一样。
参数化
使得仅仅是某些参数不同，而查询本身相同的语句可以复用，就是参数化的意义所在。比如说图3中的语句，如果我们启用了数据库的强制参数化，或是使用存储过程等。SQL Server会将这些语句强制参数话，比如说我们根据图5修改了数据库层级的选项。
图5.数据库层级的选项
此时我们再来执行图3中的两条语句，通过查询执行计划缓存，我们发现变量部分被参数化了，从而在计划缓存中的语句变得一致，如图6所示，从而可以复用.
图6.参数话之后的查询语句
但是，强制参数会引起一些问题，查询优化器很多时候就无法根据统计信息最优化一些具体的查询，比如说不能应用一些索引或者该扫描的时候却查找。所产生的负面影响在上篇文章中已经说过，这里就不细说了。
因此对于上面的问题可以有几种解决办法。
平衡参数化和非参数化
在具体的情况下，参数化有些时候是好的,但有些时候却是性能问题的罪魁祸首，下面我们来看几种平衡这两者之间关系的手段。
使用RECOMPILE
当查询中，不准确的执行计划的成本要高于编译的成本时，在存储过程中使用RECOMPILE选项或是在即席查询中使用RECOMPILE提示使得每次查询都会重新生成执行计划，该参数会使得生成的执行计划不会被插入到执行计划缓存中。对于OLAP类查询来说，不准确的执行计划所耗费的成本往往高于编译成本太多，所以可以考虑该参数或选项，您可以如代码清单1中的查询所示这样使用Hint。
SELECT * FROM Sales.Customer
WHERE CustomerID>20000 AND TerritoryID = 4
OPTION (recompile)
代码清单1.使用Recompile
除去我们可以手动提示SQL Server重编译之外，SQL Server也会在下列条件下自动重编译：
- 元数据变更，比如说表明称改变、删除列、变更数据类型等。
- 统计信息变更。
- 连接的SET参数变化，SET ANSI_NULLS等的值不一样，会导致缓存的执行计划不能被复用，从而重编译。这也是为什么我们看到缓存的执行计划中语句一模一样，但就是不复用，还需要相关的参数一致，这些参数可以通过sys.dm_exec_plan_attributes来查看。
使用Optimize For参数
RECOMPILE方式提供了完全不使用计划缓存的节奏。但有些时候，特性谓语的执行计划被使用的次数h更多，比如说，仅仅那些谓语条件产生大量返回结果集的参数编译，我们可以考虑Optimize For参数。比如我们来看代码清单2。
DECLARE @vari INT
SET @vari=4
SELECT * FROM Sales.Customer
WHERE CustomerID>20000 AND TerritoryID = @vari
OPTION (OPTIMIZE FOR (@vari=4))
代码清单2.使用OPTIMIZE FOR提示
使用了该参数会使得缓存的执行计划按照OPTIMIZE FOR后面的谓语条件来生成并缓存执行计划，这也可能造成不在该参数中的查询效率低下，但是该参数是我们选择的，因此通常我们知道哪些谓语条件会被使用的多一些。
另外，自SQL Server 2008开始多了一个OPTIMIZE FOR UNKNOWN参数，这使得在优化查询的过程中探测作为谓语条件的局部参数的值，而不是根据局部变量的初始值去探测统计信息。
在存储过程中使用局部变量代替存储过程参数
在存储过程中不使用过程参数，而是使用局部变量相当于直接禁用参数嗅探。毕竟，局部变量的值只有在运行时才能知道，在执行计划被查询优化器编译时是无法知道该值的，因此强迫查询分析器使用条件列的平均值进行估计。
虽然这种方式使得参数估计变得非常不准确，但是会变得非常稳定，毕竟统计信息不会变更的过于频繁。该方式不被推荐,如果可能，尽量使用Optimizer的方式。
代码清单3展示了这种方式。
CREATE PROC TestForLocalVari
@v INT
AS
DECLARE @vari INT
SET @vari=@v
SELECT * FROM Sales.Customer
WHERE CustomerID>20000 AND TerritoryID = @vari
代码清单3.直接引用局部变量，而不是存储过程参数
强制参数化
在本篇文章的前面已经提到过了强制参数化，这里就不再提了。
使用计划指导
在某些情况下，我们的环境不允许我们直接修改SQL语句，比如所不希望破坏代码的逻辑性或是应用程序是第三方开发，因此无论是加HINT或参数都变得不现实。此时我们可以使用计划指导。
计划指导使得查询语句在由客户端应用程序扔到SQL Server的时候，SQL Server对其加上提示或选项，比如说通过代码清单4可以看到一个计划指导的例子。
EXEC sp_create_plan_guide N'MyPlanGuide1',
@stmt=N'SELECT * FROM Sales.Customer WHERE CustomerID>20000 AND TerritoryID=@vari',
@type=N'sql',
@module_or_batch=NULL,
@params=N'@vari int',
@hints=N'OPTION (RECOMPILE)'
代码清单4.对我们前面的查询设置计划指导
当加入了计划指导后，当批处理到达SQL Server时，在查找匹配的计划缓存时也会去找是否有计划指导和其相匹配。如果匹配，则应用计划指导中的提示或选项。这里要注意的是，这里@stmt参数必须和查询语句中的一句一模一样，差一个空格都会被认为不匹配。
PARAMETERIZATION SIMPLE
当我们在数据库层级启用了强制参数化时，对于特定语句，我们却不想启用强制参数化，我们可以使用PARAMETERIZATION SIMPLE选项，如代码清单5所示。
DECLARE @stmt NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
EXEC sp_get_query_template N'SELECT * FROM Sales.Customer WHERE CustomerID>20000 AND TerritoryID=2',
@stmt OUTPUT, @params OUTPUT
PRINT @stmt
PRINT @params
EXEC sp_create_plan_guide N'MyTemplatePlanGuide', @stmt, N'TEMPLATE', NULL,
@params, N'OPTION(PARAMETERIZATION SIMPLE)'
代码清单5.通过计划指南对单条语句应用简单参数化
小结
执行计划缓存希望尽量重用执行计划，这会减少编译所消耗的CPU和执行缓存所消耗的内存。而查询优化器希望尽量生成更精准的执行计划，这势必会造成大量的执行计划，这不仅仅可能引起重编译大量消耗CPU，还会造成内存压力，甚至当执行计划缓存过多超过BUCKET的限制时，在缓存中匹配执行计划的步骤也会消耗更多的时间。
因此利用本篇文章中所述的方法基于实际的情况平衡两者之间的关系，就变得非常重要。
