性能调优5：执行计划

执行计划的编译和生成是很耗费资源和时间的，因此，SQL Server会把生成的任一执行计划缓存起来，以便重用。

一，缓存机制

SQL Server使用特定的缓存机制，以重用之前已经生成的执行计划：

• Ad hoc 查询缓存
• 参数化Ad Hoc查询缓存
• sys.sp_executesql 执行的查询，是一种参数化的查询语句
• 存储过程

前两种机制是SQL Server自动进行的，用户不能干预，而后两种是用户可以干预的。

1，Ad Hoc查询缓存

对于任意一个Ad Hoc查询，SQL Server都会把它的执行计划缓存，只有当该批处理语句完全匹配该查询时，才会复用已缓存的执行计划。

SQL Server根据批处理语句的文本计算出一个Hash值，对后续的Ad Hoc查询的文本有计算Hash值，当两个Hash值相同时，说明两个批处理的文本完全一直，是同一个查询，SQL Server优化器会复用已缓存的执行计划，如果Ad Hoc查询的文本有任意一个字符变化（比如，大写变小写，不同的换行），都会导致计算出的Hash值不同，进而不能复用执行计划。也就是说，Ad Hoc必须完全匹配才能复用。

2，参数化Ad-Hoc

SQL Server 自主决定是否把查询中的常量作为参数来对待，除了常量不同之外，其他语句都相同，这就是一个查询语句的模板，不同的参数使用相同的执行计划。例如，对于以下两个查询语句，除了常量1和2不同之外，其他语句都相同，

select ID, Name from dbo.Users where ID=1
select ID, Name from dbo.Users where ID=2

SQL Server对该语句做参数化处理，得到模板，只要符合该模板，就复用已缓存的执行计划。

select ID, Name from dbo.Users where ID=@id

3，Prepared 查询缓存

用户使用 sys.sp_executesql 控制参数和模板，只要模板相同，而参数不同，都可以复用已缓存的执行计划。

4，存储过程

用户创建的存储过程，在第一次执行时，编译和生成执行计划，并缓存到计划缓存中，当下次调用相同的存储过程，即使使传递的参数不同，SQL Server都会复用执行计划。

二，参数嗅探

参数嗅探是指在创建存储过程，或者参数化查询的执行计划时，根据传入的参数进行预估并生成执行计划。SQL Server生成的执行计划对当前参数来说是最优的，而对其他大多数参数来说，是非常低效的。有些时候，针对一个查询的第一次传参，已经产生了一个执行计划，当后续传参时，由于存在对应参数的数据分布等问题，导致原有的执行计划无法高效地响应查询请求，这就出现参数嗅探问题。

参数嗅探的本质是优化器根据参数来生成的执行计划不是最优的，导致复用执行计划时，查询性能十分低下。对于参数嗅探问题，必须重新生成执行计划，可以使用语句重编译，编译提示（optimize for）等功能来避免。

三，影响执行计划复用的因素

SQL Server不会永久保存计划的缓存，并且存在缓存中的执行计划也不会永久不变，每个计划都会有一个Age值，当SQL Server探测到内存压力时，会触发Lazy Writer进程，用于清空所有的脏页，释放数据缓存。当扫面到计划缓存时，会降低Age值，当复用一次计划时，会增加Age值。当系统遇到内存压力，或Age值降到0时，执行计划会被移除内存。

除了这两个条件之外，当遇到下面的条件时，执行计划一会被移除内存，被重新编译：

• 查询引用的基础表的结构被更改
• 查询引用的索引被更改或被删除
• 查询引用的统计信息被更新
• 执行计划被强制重新编译（详见本问第四小节）
• 单一查询中混合了DDL和DML操作，也称为延迟编译
• 在查询中修改set选项
• 查询所用到的临时表的结构被修改
• 等等

在执行计划执行过程中，执行计划被重新编译，是优化器根据表结构，索引结构和统计信息做出优化的结构，目的是为了避免继续使用不合适的执行计划。

四，强制重新编译执行计划

修改存储过程，触发器等模块（Module）能够使其执行计划重新编译，除此之外，还有其他方法，能够强制重新编译执行计划

1，标记，下次重新编译

使用该存储过程，标记一个执行模块（SP，Trigger，User-Defined Function）在下次执行时，重新编译执行计划

sys.sp_recompile [ @objname = ] 'object'

2，不复用执行计划

在创建存储过程时，使用WITH RECOMPILE 选项，在每次执行SP时，都重新编译，使用新的执行计划。

CREATE PROCEDURE dbo.usp_procname 
    @Parameter_Name varchar(30) = 'Parameter_default_value'
WITH RECOMPILE

3，执行时重新编译

在执行存储过程时，重新编译存储过程的执行计划

exec dbo.usp_procname @Parameter_name='Parameter_value' 
WITH RECOMPILE

4，语句级别的重新编译

在SP中，使用查询选项 option（recompile），只重新编译该语句级别的执行计划

select column_name_list
from dbo.tablename
option(recompile)

SQL Server在执行查询之后，查询提示（RECOMPILE）指示存储引擎将计划缓存抛弃，在下次执行存储过程时，强制查询优化器重新编译，生成新的执行计划。在重新编译时，SQL Server 优化器使用当前的变量值生成新的计划缓存。

五，控制执行计划

优化器会根据查询选择执行计划，选择索引，表关联算法等，但是，当发现优化器选择了低效的执行计划时，可以使用hint来控制执行计划，SQL Server提供了三种类型的hint：

• 查询提示（query hint）：告知优化器在整个查询过程中都应用某个提示，
• 关联提示（join hint）：告知优化器在关联时使用特定的关联算法
• 表提示（table hint）：告知优化器使用表扫描，还是表上特定的索引

1，查询提示

使用option来设置查询提示，

• 用于group by 聚合，可以控制分组的算法：hash group 和order group
• 用于控制关联的算法， option(hash join)
• 通常情况下，优化器决定表关联的顺序，可以使用force order选项，使优化器按照join的顺序来关联， option(force order)
• 使用maxdop来确定语句执行的最大并发度，option(maxdop 1)，取消并发执行。
• 按照指定的参数来优化 option(optimize for (@para_name= constant_value))

2，关联提示

在 join关键字前面使用Loop，Merge和Hash来控制关联的算法

3，表提示

在引用的表名后面，通过with()来设置表提示 table_name with(hints)，

当使用索引时，使用 with(index(index_name))来设置，

参考文档：