Sql Server中存储过程使用时间长了查询效率变得很慢的分析

1. 问题描述：

同一各存储过程，第一次创建并执行查询只要几十毫秒，使用一段时间后执行查询竟然需要3秒多！！！

2. 分析问题

一般碰到这种问题，我的第一个想法是：数据量太大导致查询时间提高。带着这个设想我查看了一下数据库的数据，发现数据量是有所提升但将查询单独拿出做sql查询时，查询时间还是几十毫秒。而一旦执行存储过程查询，查询时间又成了3秒。

因为对数据库优化器了解不多，所以没想到执行计划的问题。当时的想法是是否是查询语句的原因，打开存储过程发现之前因时间原因查询语句结构写得有点乱，就随手把结构调一调方便自己查看并排查原因。看了一会还是没找到问题怎么办？肯定要执行一下得吧，毕竟优化了结构也是可以得，虽然没找到原因。注意，就是这个动作让我意外发现问题得所在：当我点完执行，生成新的执行计划后，我再调用这个存储过程，奇迹发生了。原本执行需要3秒得查询又变回几十毫秒，那么问题应该就是出现再执行计划上面了。

通过查找资料，我了解到存储过程是预编译的，存储过程在上一次执行过程中就生成了最优的执行计划并将之缓存起来，后面我们再继续调用该存储过程都是用的该缓存的执行计划，而随着数据的变化和数据量的增加，该缓存执行计划对于后面传入的参数来说并非是最优的执行计划了，这也就是参数嗅探问题。参数嗅探问题定位的流程可参考：讲的很详细的参数嗅探问题流程

3. 解决方式

既然缓存的存储过程不是实际的最优执行计划，那么我们在调用存储过程的时候进行一次强制重编译就好了，重编译后在拿到最新的执行计划。强制重编译的方法：在定义存储过程后加WITH  RECOMPILE ，具体操作如下图：

对于查询不是很频繁的存储过程，可使用OPTION (OPTIMIZE FOR UNKNOWN)解决参数嗅探问题，那么OPTION (OPTIMIZE FOR UNKNOWN)又是何物呢，官方的解释是：

OPTIMIZE FOR 编译和优化查询时提示查询优化器对本地变量使用特定值。仅在查询优化期间使用该值，在查询执行期间不使用该值。

指定查询优化器在查询优化期间使用统计数据而不是初始值来确定局部变量的值。OPTIMIZE FOR 可以抵消优化器的默认参数检测行为，也可在创建计划指南时使用

OPTIMIZE FOR UNKNOWN

指示查询优化器在查询已经过编译和优化时为所有局部变量使用统计数据而不是初始值，包括使用强制参数化创建的参数。有关强制参数化的详细信息，请参阅强制参数化。

如果在同一查询提示中使用 OPTIMIZE FOR @variable\_name = literal_constant 和 OPTIMIZE FOR UNKNOWN，则查询优化器将对特定的值使用指定的 literal_constant，而对其余变量使用 UNKNOWN。这些值仅用于查询优化期间，而不会用于查询执行期间。

使用方法：

所以对于查询频繁的可以用强制生成新执行计划，对于查询不频繁的用OPTION (OPTIMIZE FOR UNKNOWN)