如何解决参数嗅探导致的SQL Server存储过程性能下降？

参数嗅探导致 SQL Server 存储过程性能下降

参数嗅探是 SQL Server 使用的一种优化技术，通过选择来提高查询性能基于首次编译存储过程时的参数值的执行计划。但是，在某些情况下，参数嗅探可能会导致性能问题。

考虑以下存储过程：

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

首次使用 @MyDate 为 NULL 执行此存储过程时，查询优化器生成针对该特定参数值优化的执行计划。但是，如果随后使用不同的 @MyDate 值执行存储过程，查询优化器可能无法编译最佳执行计划，从而导致性能下降。

此问题称为“参数嗅探变坏” 。在上面的例子中，即使@MyDate在存储过程中使用时实际上是NULL，基于初始编译生成的糟糕的执行计划仍然有效。

要解决这个问题，可以禁用参数通过欺骗参数来嗅探：

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

这种方法创建输入参数的副本，并使用该副本进行查询优化过程，防止参数出现偏差

在 SQL Server 2008 及更高版本中，可以使用 OPTIMIZE FOR UNKNOWN 提示来优化参数嗅探：

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
WITH RECOMPILE, OPTIMIZE FOR UNKNOWN
AS
    -- Do Something using @MyDate

此提示指示查询优化器根据未知生成执行计划参数值，消除参数嗅探相关性能问题的风险。

以上是如何解决参数嗅探导致的SQL Server存储过程性能下降？的详细内容。更多信息请关注PHP中文网其他相关文章！