存储过程性能——这是WILD

本文关键字：WILD 这是 存储过程 性能 | 更新日期: 2023-09-27 18:16:45

这个问题与其说是关于寻找解决方案，不如说是关于如何解释我从SQL Server中看到的bizarrest行为。

我有一个签名如下的存储过程: 
alter procedure MySP @param1 uniqueidentifier, 
                     @param2 uniqueidentifier, 
                     @param3 uniqueidentifier

给定一组特定的参数，这个过程在c#(使用SqlCommand.ExecuteReader())中运行需要很长时间——大约2分钟。在直接查询会话中使用相同的参数，SP运行时间不到2秒。

花了很长时间，我甚至不想解释我们是如何偶然发现这个解决方案的，但这就是我们所做的:

在SP的开头，我们声明了3个局部变量，并将它们赋值给参数的值，如下所示:

declare @param1_copy uniqueidentifier, 
        @param2_copy uniqueidentifier, 
        @param3_copy uniqueidentifier
select @param1_copy = @param1,
       @param2_copy = @param2,
       @param3_copy = @param3

然后，在SP的其余部分中，我们用本地副本替换了对输入参数的所有引用。

瞧。SP在2秒内执行。整个团队都惊呆了。

现在，女士们先生们，有人能解释这种行为吗?

存储过程性能——这是WILD

这听起来像是参数嗅探。

来自Microsoft的定义:

"参数嗅探"指的是SQL Server的执行环境在编译或重新编译期间"嗅探"当前参数值，并将其传递给查询优化器，以便它们可以用来生成可能更快的查询执行计划的过程。"current"一词指的是在引起编译或重新编译的语句调用中存在的参数值。

看起来你已经找到了一个修复方法，另一个是使用EXEC…编译:

当执行具有非典型参数值的存储过程时，"EXEC…WITH RECOMPILE"可用于确保新的查询计划不会替换使用典型参数值编译的现有缓存计划。

我建议阅读Erland Sommarskog的优秀文章:应用程序慢，SSMS快?理解性能之谜。

里面有关于这个问题的全部细节。

一般来说，当缓存一个查询计划时，一些查询设置会在缓存键中使用。这些设置在默认SSMS设置中的设置与在默认连接字符串中的设置不同，因此可能存在不同的查询计划。

我有一个类似的情况，我有一个类型为xml的参数，并且需要花费很多时间来执行。我做了同样的方法，创建了一个局部变量，并传入参数值，它的工作速度超快:)

SQL开始编译SP以创建其执行计划时，会考虑其参数中传递的值来影响执行计划。但是，如果您将参数值分配给一个局部变量，并在整个SP代码中使用该变量，那么SQL将不会考虑影响执行计划的参数，因此您将从查询中获得更快的响应。

相关文章：