参数探测(Parameter Sniffing)影响存储过程执行效率解决方案

如果SQL query中有参数，SQL Server 会创建一个参数嗅探进程以提高执行性能。该计划通常是最好的并被保存以重复利用。只是偶尔，不会选择最优的执行计划而影响执行效率。

SQL Server尝试通过创建编译执行计划来优化你的存储过程的执行。通常是在第一次执行存储过程时候会生成并缓存查询执行计划。当SQL Server数据库引擎编译存储过程中侦测到有参数值传递进来的时候，会创建基于这些参数的执行计划。这种在编译存储过程中侦测参数值的方法，通常被称为“参数探测”。有时参数探测会产生效率低下的执行计划;特别是当一个存储过程调用与具有不同的基数的参数值。

什么是参数探测

探测一词就显示出了更多的不可靠性，有时候会产生好的结果就不可避免的产生一些坏的结果。参数探测是在SQL SERVER通过第一次执行时调用的参数创建的最优的执行计划。 这个第一次是指不管你执行或者是重新编译因为在缓存中没有一个现成的执行计划存在。以后使用相同的参数调用同一个存储过程的时候同样会得到一个最佳的执行方案。但是使用不同的参数的时候可能得不到最佳的方案，就是坏的结果。

并不是所有的执行计划是平等的，执行计划会按照要做什么进行一些必要的优化。SQL SERVER再去选择并确定最优的执行策略。它着眼于做什么样的查询，使用参数值来看看统计数据，做了那些计算，最终决定通过哪些步骤来解决查询。这是如何创建一个执行计划的比较简单的解释。对我们来说，重要的一点是，SQL Server通过这些参数用来确定如何处理查询。一组参数的最优执行计划可能是一个索引扫描操作，而另一组参数可能使用索引查找能更好地解决。

 

参数探测影响示例

假设我们有一个基于国家查询运行销售数据的存储过程：

EXEC rpt_Sales @Country ='China' - SQL Server构建针对大量销售额的国家而优化的执行计划，并且运行时间大约为750毫秒。

EXEC rpt_Sales @Country ='Monaco' - 它重用了中国的大数据缓存执行计划。这对于小国来说不是很好，但也没有那么糟糕因为只返回少量数据，所以它仍然运行在500毫秒。

现在我们重新启动SQL Server，有人首先查询Monaco：

EXEC rpt_Sales @Country ='Monaco' - SQL Server构建了一个针对微小数据量的国家而优化的执行计划，它运行时间只有50毫秒 - 比摩纳哥重用中国计划时要好很多！

EXEC rpt_Sales @Country ='China' - 它重用了Monaco的缓存计划来处理小数据。 它需要30秒，如果很多人同时运行这个存储过程，我们的服务器要开始崩溃了！

 

如何临时修复参数探测问题

1． 重启服务器 – 新手最先想到的解决方案，遇到问题先重启。重启服务器后会清除所有的缓存。重启完后，有人首先使用'China'去运行了rpt_Sales, 它将建立一个好的执行计划。一些初级DBA会觉得这个问题已经被修复。

2． 重启SQL Server 实例 – 随着工作经验的增长，他们意识到不能随意的重启服务器。发现重启实例也可以达到重启服务器的效果。。

3． 运行DBCC FREEPROCCACHE - 此命令会从缓存中清除所有执行计划，但不清除SQL Server的其他缓存和统计信息。相比重启来说，是个比较好的方案，因为至少可以确保数据库在线的情况下完成。

4． 重建索引 – 这其实是个意外，当SQL Server重建一个表的索引时会同时更新索引的统计信息。这也会修复了参数探测问题，因为当SQL Server意识到传入查询使用的对象的统计信息 (statistics)更新时，它将为该查询构建一个新的执行计划。

5．更新统计信息 (statistics) - 当人们学习到上述额外发现，他们意识到他们可以通过只更新统计信息数据，这是一个比重建索引容易得多的操作。

6．对一个表或存储过程运行sp_recompile - 此系统存储过程接受表或存储过程名称作为参数，并将所有相关的执行计划标记为在下次运行时强制重新编译。

7．清除特定的执行计划缓存 - 为单个查询运行DBCC FREEPROCCACHE, 只清除特定(比如rpt_Sales)的执行计划缓存。

 

禁用参数探测

既然参数探测会带来不确定的因素，我们可以通过使用本地变量来禁止参数探测。

比如：

create procedure rpt_Sales (@Country varchar(20))
 as
 begin
  declare @LocCountry varchar(20)
  set @LocCountry = @CustID

  select * from orders
  where Country = @LocCountry
 end

 

归纳总结 
参数探测（Parameter Sniffing）可以在存储过程级别上启用或禁用； 
如果检索的数据列基本上平均分布，我们不必使用本地变量（禁用Parameter Sniffing）；例如，查询主键列或唯一键列（Unique Key）； 
如果检索的数据列分布很大，则可以使用本地变量，禁用参数探测（Parameter Sniffing）；