主页 / dba / 问题 / 74159
Accepted
Shaun Bowe
Asked: 2014-08-17 05:02:24 +0800 CST

SQL 参数嗅探可能的解决方法

  • 772

我们的系统大约有 500 个“客户”，他们的记录数量差异很大。这是一个（非常简化的）示例查询，它可以根据传递的参数返回 0 - 100000 行。此查询运行 find 但我相当肯定它会受到参数嗅探的影响，具体取决于缓存的参数。

exec sp_executesql N'
               SELECT *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

如果我修改查询以包含参数作为选择，它似乎会为每个单独的客户缓存查询。

exec sp_executesql N'
               SELECT ''bda43162-2d98-4e79-8e81-7056f6df5e51'', *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

性能大大提高，因为每个客户都缓存了自己的查询版本。这种方法有副作用吗？

假设：

  • 对于系统的这一部分，它必须是动态 SQL
  • 此查询经常运行
  • 客户数量不会快速增长

编辑：我考虑过使用 OPTION (RECOMPILE)，但如果我可以通过这种方法获得编译查询的好处，我不想每次都重新编译。

sql-server parameter

1 个回答

  1. Best Answer

    参数嗅探意味着一组参数产生的执行计划与另一组截然不同，如果缓存了错误的计划，您将获得不利的性能影响。

    此答案基于您的简化查询 - 要获得有关查询的准确建议，您需要发布查询以及参数嗅探产生的两个不同计划。（我总是更愿意找到准确的根本原因，而不是对简化的示例进行故障排除，但我必须使用您发布的代码，所以就这样吧。）

    您的查询中只有一个表（假设 Widgets 不是视图）：

    SELECT * FROM Widgets WHERE CustomerId=@0

    这意味着，如果您在 CustomerID 上有一个非聚集索引，则某些 CustomerID 值可能会生成一个计划，其中包含非聚集索引查找和键查找，而其他参数将在 Widgets 表中执行聚集索引扫描。

    有几种方法可以解决这种情况，我将以通常最安全到最高风险的方式列出它们：

    对查询使用 OPTION (RECOMPILE)。这确实需要更改代码以将行添加到查询中，但是该查询的每次执行都应该获得最合适的计划。风险是更高的 CPU 用于计划执行（尽管这在像这样的单表、单谓词查询中通常无关紧要，因为计划将非常容易生成）。

    缓存各种计划。您注意到将查询作为字符串传递将使每个参数缓存其自己的单独计划。虽然这在今天可以工作，但它确实会使计划缓存膨胀（占用更多 SQL Server 的内存）。这里的风险是有人会打开强制参数化，这是一个数据库级别的选项，它将参数化您的所有查询，无论它们是否作为字符串发送，然后您突然又要再次解决这个问题。

    其余的都是有效的解决方案，但不适用于您的单表、单谓词查询。我在这里列出它们只是为了后代和清晰：

    使用 OPTIMIZE FOR UNKNOWN 查询提示，或者我们喜欢称之为优化平庸。需要更改查询，并为您提供通常足够好的计划。这将避免由于参数嗅探导致查询计划的随机更改，但风险是它仍然不是性能最高的计划。

    使用具有特定 CustomerID 的 OPTIMIZE FOR 查询提示。这也需要更改代码，并且您将为您的一位大客户优化查询。这将获得一个非常适合大客户的查询计划，而对于小客户来说则不太好。小客户的表现会下降，但大客户不会削弱系统。风险在于您的客户分布将发生变化，这将不再是整个应用程序的正确计划。

    使用查询计划指南。您可以准确地获得所需的查询计划，然后将计划指南固定到内存中。这是关于计划指南的在线图书部分。我通常不喜欢这个，因为如果你的索引发生变化，查询计划将不会利用新的索引。如果您的查询更改，计划指南将不再有效。突然间，系统可能会表现得非常糟糕，人们会忘记之前计划指南的帮助。

    使用具有手动逻辑的存储过程。拥有调用不同存储过程的分支，一个用于大客户，一个用于小客户。这仅用于更大、更复杂的查询，这些查询可能在几分钟和几小时之间变化（或根本不完成）。

    • 11

相关问题