参数嗅探

Question

假设我们有一个性能很差的存储过程，它有6个参数。如果六个参数中的一个被传递给存储过程中的局部变量，这是否足以禁用参数嗅探，或者是否有必要将传递给存储过程的所有6个参数都传递到存储过程中的局部变量中？

Answer 1

根据Paul White的评论，将变量赋值给局部变量是SQL Server旧版本中的一种解决方法。它对sp_executesql没有帮助，微软可以编写一个更智能的解析器，使这个变通方法失效。解决方法是混淆解析器对参数值的理解，因此为了让解析器对每个参数都起作用，您必须将每个参数存储在一个局部变量中。

SQL Server的最新版本提供了更好的解决方案。对于不经常运行的代价高昂的查询，我会使用option (recompile)。例如：

SELECT *
FROM YourTable
WHERE col1 = @par1 AND col2 = @par2 AND ...
OPTION (RECOMPILE)

这将导致查询规划器在每次调用存储过程时重新创建(“重新编译”)计划。考虑到计划的低成本(通常低于25ms)，这对于昂贵的查询来说是明智的行为。检查您是否可以为250ms查询的特定参数创建更智能的计划，这是值得的。

如果您的查询运行得如此频繁，以至于规划成本微不足道，那么您可以使用option (optimize for unknown)。这将导致SQL Server创建一个计划，它希望该计划能够很好地适用于所有参数的所有值。指定此选项时，SQL Server将忽略参数的第一个值，因此这实际上会阻止嗅探。

SELECT *
FROM YourTable
WHERE col1 = @par1 AND col2 = @par2 AND ...
OPTION (OPTIMIZE FOR UNKNOWN)

此变体适用于所有参数。您可以使用optimize for (@par1 unknown)来防止仅对一个参数进行嗅探。

Answer 2

这两天我累坏了，现在我找到了解决办法。如果任何人得到缓解，所以我在这里发布了我的经验。我有一个相当复杂的查询，它有5个CTE和一个已经知道参数嗅探其设计的联合。我们选择了选项重新编译来解决这个问题，它运行得相当好。两年后，我们创建了一个高可用集群，并分离了报表服务器。所有工作都很好的1年，现在由于Covid19，我们需要关闭30天。服务器已打开，但所有活动都正常进行。同时，由于极端的日志大小和数据增长，我们需要截断数据库，从可用组中删除并重新添加可用性。从前两个日期开始，此查询显示参数嗅探活动，除一个外，没有任何补救措施起作用。

拯救我的银弹是 EXEC sp_updatestats。

这对我来说很有效，现在我有时间找到一个永久修复的适当解决方案。