c# - 存储过程性能 - 这是 WILD？

Question

这个问题与其说是寻找解决方案，不如说是获得对我在 SQL Server 中见过的怪异行为的解释。

我有一个具有以下签名的存储过程:

alter procedure MySP @param1 uniqueidentifier, 
                     @param2 uniqueidentifier, 
                     @param3 uniqueidentifier

在给定一组特定参数的情况下，此过程需要很长时间才能从 C# 运行(使用 SqlCommand.ExecuteReader())- 大约 2 分钟。在直接查询 session 中使用相同的参数，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 秒内执行。这里的团队目瞪口呆。

现在，女士们先生们，谁能解释一下这种行为？

Answer 1

这听起来像 parameter sniffing .

来自微软的定义:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.

看起来您已经找到了一个解决方法，另一个是使用 EXEC...WITH RECOMPILE:

When executing a stored procedure with atypical parameter values, "EXEC ... WITH RECOMPILE" can be used to ensure that the fresh query plan does not replace an existing cached plan that was compiled using typical parameter values.