7.4.3 参数嗅探问题

参数嗅探问题

就像我在前面提到的，SQL Server在第一次调用存储过程时根据为其提供的输入生成计划，不论好坏。“第一次调用”也包括因一定时间内没有重用或其他原因从缓存中移除计划之后的第一次调用。优化器“知道”输入参数的值，能够生成适合该输入的计划。然而，当查询中引用局部变量时情况就不一样了。为了便于我们讨论，这些局部变量是在批处理中还是在存储过程中都无关紧要。优化器无法“嗅探”出变量的内容。因此，当它优化查询时，它必须猜测。很明显，如果你不了解这个问题而且没有采取纠正措施将导致性能低下的计划。

为演示这个问题，向Orders表插入一个新订单，为OrderDate列指定GETDATE函数。

INSERT INTO dbo.Orders(OrderDate, CustomerID, EmployeeID)
VALUES(GETDATE(), N'ALFKI', 1);

ALTER PROC dbo.usp_GetOrders
@d AS INT = 0
AS

DECLARE @odate AS DATETIME;
SET @odate = DATEADD(day, -@d, CONVERT(VARCHAR(8), GETDATE(), 112));

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate;
GO

EXEC dbo.usp_GetOrders;

注意 输出的OrderDate列的值是插入新订单时GETDATE的值。

当优化器优化查询时，它并不知道@odate的值是什么。所以它会使用一个保守的硬编码值（hard-coded value），它是表行数的30%。对于这样一个低选择性的估计，优化器通常会选择表扫描，即使实际中的查询是高选择性的，如果使用OrderDate上的索引会更快。

通过请求估计的执行计划（不是实际的），你可以观察到优化器估计和选择的计划。针对这次存储过程调用，你得到的估计执行计划如图7-3所示。

图7-3  执行计划显示估计的行数

你可以看到，优化器选择了执行表扫描（无序的聚集索引扫描），这个选择是根据它的30%的选择性估计作出的（249行/共计830行）。

解决该问题有几种方法。一种是在查询中尽可能地使用内联表达式代替变量，该表达式引用输入参数，而不是变量，而且尽可能地使用这种方法。这我们的示例中，应该是这样。

再次运行usp_GetOrders，你会看到执行计划使用了OrderDate上的索引。

EXEC dbo.usp_GetOrders;

得到的计划与前面图7-1中的相似。I/O成本仅有4次逻辑读取。

解决这个问题的第二种方法是使用根存储过程（stub procedure）。也就是创建两个存储过程。第一个存储过程接收原始参数，把计算结果存入局部变量，然后调用第二个过程，把变量作为输入提供给它。第二个存储过程接收传递过来的输入订单日期并调用直接引用输入参数的查询。为实际调用查询的存储过程（第二个存储过程）生成计划时，优化器在优化时就可以知道参数的值了。

运行代码清单7-3中的代码并实现这个解决方案。

代码清单7-3  使用根存储过程

IF OBJECT_ID('dbo.usp_GetOrdersQuery') IS NOT NULL
DROP PROC dbo.usp_GetOrdersQuery;
GO
CREATE PROC dbo.usp_GetOrdersQuery
@odate AS DATETIME
AS
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate;
GO
ALTER PROC dbo.usp_GetOrders
@d AS INT = 0
AS
DECLARE @odate AS DATETIME;
SET @odate = DATEADD(day, -@d, CONVERT(VARCHAR(8), GETDATE(), 112));
EXEC dbo.usp_GetOrdersQuery @odate;
GO

调用usp_GetOrders存储过程。

EXEC dbo.usp_GetOrders;

你将得到该输入的最佳计划，该计划与前面表7-1中显示的计划相似，产生的I/O成本只有4次逻辑读取。

不要忘了我在上一节中描述的关于重用执行计划的问题。虽然得到了适合该输入的高效的执行计划，但这并不表示你希望在后面的调用中重用该计划。这完全取决于该输入是否具有代表性。我在前面对输入不具代表性这种情况提了一些建议，你应该遵循这些建议。

最后，SQL Server 2005还为你提供了一个新工具来解决这个问题，即查询提示OPTIMIZE FOR。该提示允许你为SQL Server提供一个字面值，用于表示具有代表性的变量的选择性。例如，如果你知道变量通常是一个高选择性的值，可以提供一个字符串‘99991231’, 就像这样：

ALTER PROC dbo.usp_GetOrders
@d AS INT = 0
AS
DECLARE @odate AS DATETIME;
SET @odate = DATEADD(day, -@d, CONVERT(VARCHAR(8), GETDATE(), 112));
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate
OPTION(OPTIMIZE FOR(@odate = '99991231'));
GO

运行这个存储过程：

EXEC dbo.usp_GetOrders;

你会得到适合高选择性OrderDate的最佳计划，与前面图7-1显示的计划类似，产生的I/O成本只有4次逻辑读取。
如果在查询筛选器使用输入参数之前更改了它们的值，你可能还会面临类似的问题。例如，假设你定义了变量@odat，并为它赋默认值为NULL。在查询筛选器使用该参数之前，你使用了下面的代码。

SET @odate = COALESCE(@odate, '19000101');

该查询使用OrderDate >= @odate条件筛选订单。当优化该查询时，优化器不知道@odate已经被修改，还会用原始值（NULL）优化这个查询。你将面临和前面描述的一样的问题，同样，应该用类似的逻辑解决该问题。

更多信息 关于该主题的更多信息，请参考Arun Marathe所著的白皮书"Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005"，你可以通过该网址访问：http://www.microsoft.com/technet/prodtechnol/ sql/2005/recomp.mspx。

完成后，运行下面的代码进行清理。

DELETE FROM dbo.Orders WHERE OrderID > 11077;
GO
IF OBJECT_ID('dbo.usp_GetOrders') IS NOT NULL
DROP PROC dbo.usp_GetOrders;
GO
IF OBJECT_ID('dbo.usp_CustCities') IS NOT NULL
DROP PROC dbo.usp_CustCities;
GO
IF OBJECT_ID('dbo.usp_GetOrdersQuery') IS NOT NULL
DROP PROC dbo.usp_GetOrdersQuery;
GO