性能调优5：执行计划
查询优化器基于当前的统计信息和参数，衡量开销之后，选择“最优”的执行计划，需要注意的是，“最优”是相对的，优化器不可能穷举所有的执行计划来评估其开销，这个“最优”的标准是对当前参数和当前的统计信息来说的，优化器从生成的备选执行计划中选择开销最小的。由于执行计划的编译和生成是很耗费资源和时间的，因此，sql server会把生成的任一执行计划缓存起来，以便重用。
由于关系表的数据和结构可能发生改变，数据更新会导致统计信息过时，而之前的参数可能不具有代表性，使得已生成的执行计划不能代表其他参数值，导致查询性能低下。因此，应当监控执行计划的性能，当发现参数嗅探问题时，应该及时修改代码以重编译；当发现统计信息过期时，应及时更新统计信息等。
一，缓存机制
sql server使用特定的缓存机制，以重用第一次执行查询时生成的执行计划，总的来说，sql server内部有以下四种执行计划缓存机制：
- ad hoc 查询缓存
- 参数化ad hoc查询缓存
- sys.sp_executesql 执行的查询，是一种参数化的查询语句
- 存储过程
对于adhoc查询的缓存，是sql server自动进行的，用户不能干预，而后两种是用户可以干预的，用户可以通过优化代码来复用“模板化”的查询。所谓模板化语句，是指除了个别的常量发生变化之外，语句主体不变，可以把变化的常量作为一个参数，不变的语句主体作为一个模板来处理，sql server优化器把这个模板编译成执行计划，传入不同的参数会使用相同的执行计划。
1，ad hoc查询缓存
对于任意一个ad hoc查询，sql server都会缓存它的执行计划，但是，只有当批处理语句的文本完全匹配时，才会复用已缓存的执行计划，完全匹配的处理过程是:
- sql server根据批处理语句的文本计算出一个hash值，对后续的ad hoc查询的文本同样计算hash值，当两个hash值相同时，说明两个批处理的文本完全相同，相当于同一个查询的重复执行，sql server优化器会复用已缓存的执行计划。
- 如果ad hoc查询的文本有任意一个字符发生变化（比如，大写字符变小写字符，不同的换行，多了一个空格等），都会导致计算出的hash值不同，进而不能复用执行计划。也就是说，ad hoc查询的文本必须完全匹配才能复用执行计划。
大量的ad hoc查询缓存会占用计划缓存的空间，这些缓存可能只会被使用一次，以后再也不会被使用。如果数据库系统中存在大量的一次性查询语句，应设置server 级别的性能优化选项：optimize for ad hoc wrokloads。
“针对即席工作负载进行优化”是一个server级别的性能优化选项，用于提高包含许多临时批处理的工作负载的计划缓存的效率，如果把该选项设置为true，则数据库引擎在首次编译批处理时只保留计划缓存中的一个存根，而不是存储整个执行计划。当再次调用该批处理时，数据库引擎识别出该批处理在之前被执行过，进而从计划缓存中删除该执行计划的存根，并把完全编译的执行计划添加到计划缓存中。当非参数化的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))来设置，
参考文档：