参数嗅探 [英] parameter sniffing

问题描述

假设我们有一个带有 6 个参数的性能不佳的存储过程.如果六个参数之一被传输到存储过程中的局部变量，是否足以禁用参数嗅探，或者是否有必要将传递给存储过程的所有 6 个参数传输到存储过程中的局部变量?

Suppose we have a poorly performing stored procedure with 6 parameters. If one of the six parameters is transferred to a local variable within the stored procedure, is that enough to disable parameter sniffing or is it necessary to transfer all 6 parameters that're passed to the stored procedure into local variables within the stored procedure?

推荐答案

根据 Paul White 的评论，将变量分配给局部变量是旧版本 SQL Server 的一种解决方法.它对 sp_executesql 没有帮助，Microsoft 可以编写一个更智能的解析器，使此解决方法无效.该解决方法的工作原理是将解析器混淆参数值，因此为了使其适用于每个参数，您必须将每个参数存储在一个局部变量中.

Per Paul White's comment, assigning a variable to a local variable is a workaround from older versions of SQL Server. It won't help with sp_executesql, and Microsoft could write a smarter parser that would invalidate this workaround. The workaround works by confusing the parser about a parameter's value, so in order for it to work for each parameter, you'd have to store each parameter in a local variable.

更新的 SQL Server 版本有更好的解决方案.对于不经常运行的昂贵查询，我会使用 option (recompile).例如:

More recent versions of SQL Server have better solutions. For an expensive query that is not run often, I'd use option (recompile). For example:

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

这将导致查询计划器在每次调用存储过程时重新创建(重新编译")计划.鉴于规划成本低(通常低于 25 毫秒)，这是昂贵查询的明智行为.值得花 25 毫秒来检查您是否可以为 250 毫秒查询的特定参数创建更智能的计划.

This will cause the query planner to recreate ("recompile") a plan every time the stored procedure is called. Given the low cost of planning (typically below 25ms) that is sensible behavior for expensive queries. It's worth 25ms to check if you can create a smarter plan for specific parameters to a 250ms query.

如果您的查询运行过于频繁以至于计划成本非常高，您可以使用选项(优化未知).这将导致 SQL Server 创建一个计划，它希望对所有参数的所有值都能正常工作.当您指定此选项时，SQL Server 会忽略参数的第一个值，因此这实际上可以防止嗅探.

If your query is run so often that the cost of planning is nontrivial, you can use option (optimize for unknown). That will cause SQL Server to create a plan that it expects to work well for all values of all parameters. When you specify this option, SQL Server ignores the first values of the parameters, so this literally prevents sniffing.

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

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

This variant works for all parameters. You can use optimize for (@par1 unknown) to prevent sniffing for just one parameter.

这篇关于参数嗅探的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！