SQL Server过程执行计划的奇怪问题

Question

我想知道你们能不能帮我解决我最近在SQL Server上遇到的一个奇怪的问题。

我有一个存储过程(让我们称之为SPold)，它相当大，需要大量的计算(在app中不可能做到这一点，因为大约6000个用户的信息需要在1-er中返回(我根据姓氏将这个值减少到1000 ))。存储过程通常在几秒钟内执行，并且每隔几分钟调用一次。

今天早上，存储过程的执行时间突然增加了4-10倍，导致大量超时。我发现，通过使用新名称(SPnew)复制该过程并执行，我将再次获得快速执行时间。这表明执行计划是原始SPold的问题所在，所以我决定使用recompile来执行它。这将更快地返回结果(尽管没有SPnew快)，但随后用户对SPold的调用再次变慢。就像新计划没有被执行一样。

我所做的是修复这个问题，将Exec SPnew放入SPold中，现在对SPold的调用再次快速返回。

有人知道这是怎么回事吗？唯一一夜之间更新的是统计数据，尽管我认为这应该会同时影响SPold和SPnew。

Answer 1

我还遇到了两个Sql Server2005的“奇怪”案例，这可能也与您的问题有关。

在第一种情况下，我的程序在以dbo身份运行时执行得非常快，而在不同的用户帐户下从应用程序运行时速度很慢。

在第二种情况下，该过程的查询计划针对第一次调用该过程时使用的参数值进行了优化，随后该计划也被重用于其他参数值，从而导致执行速度变慢。

对于第二种情况，解决方案是将参数值复制到过程中的局部变量中，然后在查询中使用变量而不是参数。

Answer 2

听起来，由于参数嗅探，您遇到了缓存不正确的查询计划。

你能post存储过程吗？

• Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
• I Smell a Parameter!

在SQL Server2005中，可以使用OPTIMIZE FOR查询提示获取参数的首选值，以解决与参数探查相关的一些问题：

OPTIMIZE FOR指示查询优化器在编译和优化查询时使用局部变量的特定值。该值仅在查询优化期间使用，而不在查询执行期间使用。OPTIMIZE FOR可以抵消优化程序的参数检测行为，也可以在创建计划指南时使用。有关详细信息，请参阅Recompiling Stored Procedures和Optimizing Queries in Deployed Applications by Using Plan Guides。

尽管SQL Server 2005不支持针对未知参数进行优化(在SQL Server 2008中引入)，但这将消除对给定参数的参数嗅探：

OPTION (OPTIMIZE FOR (@myParam UNKNOWN))

在SQL Server2005中，可以通过将参数复制到局部变量中，然后在查询中使用该局部变量来实现相同的效果。