OPTIMIZE FOR Unknown工作原理

原文：http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/

备注：翻译不当，请指出或参考原文。

OPTIMIZE FOR是SQL Server 2005引入的一个查询提示，它有助于解决参数探测问题，并要求为参数指定一个值。对于参数探测问题的介绍可以查看先前的文章：here。换句话说，OPTIMIZE FOR UNKNOWN也可以在SQL Server 2008中使用，唯一不同的是可以不指定参数值。

避免参数探测问题的传统方法，尤其对于先前版本的SQL Server，通常采用“本地变量”的方式。但是，避免参数探测并并总是好的解决方案，正如先前提到的，从查询优化的角度看，参数探测是好事情，当查询优化器获取到使用统计的参数值来估计查询返回的记录个数，使用histogram可以获得较好的估计，但是，当使用“本地变量”，SQL Server无法使用统计histogram, OPTIMIZE FOR UNKNOWN 的工作机制与之类似。

要更好地理解OPTIMIZE FOR UNKNOWN的工作原理，我们首先看一下参数已知的例子：

CREATE PROCEDURE test (@pid int)
AS
SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = @pid

运行存储过程，并使用参数值为709：

EXEC test @pid = 709

在此情况下，SQL Server可以使用histogram，并估计出满足条件的记录有188行，查询优化器使用这个信息可以判断产生的计划，使用以下的存储过程可以检查使用的统计对象：

DBCC SHOW_STATISTICS('SalesOrderDetail', IX_SalesOrderDetail_ProductID)

执行的结果如下所示，这里仅列出部分信息：

All density    Average Length    Columns
0.003759399    4    ProductID

RANGE_HI_KEY    RANGE_ROWS    EQ_ROWS    DISTINCT_RANGE_ROWS    AVG_RANGE_ROWS
707                0            3083            0                        1
708                0            3007            0                        1
709                0            188             0                        1
710                0            44              0                        1

本例中，SQL Server 使用histogram来找到ProductID为709的值（根据RANGE_HI_KEY），由EQ_ROWS定义来得到估计行数为188行。

接下来，我们修改存储过程，使用“本地变量”：

ALTER PROCEDURE test (@pid int)
AS
DECLARE @lpid INT
SET @lpid = @pid
SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = @lpid

重新运行先前参数值为709的查询：

EXEC test @pid = 709

观察图形执行计划，发现其使用了“表扫描”，如下所示：

在优化阶段，本地变量未知，查询优化器无会使用709的统计信息，实际上，当采用了本地变量的方式，指定的参数值是多少并没有要求，得到的执行计划是相同的，估计的行数都是456.079。

现在我们再次对存储过程进行修改，使用OPTIMIZE FOR UNKNOWN:

ALTER PROCEDURE test (@pid int)
AS
SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = @pid
OPTION (OPTIMIZE FOR UNKNOWN)

你可能注意到其工作方式与“本地变量”相同，得到的估计行数也是456.079,执行计划也是采用“表扫描”。

接下来，让我们一起来看一下SQL Server如何算出456.079的值以及背景信息。

密度（Density）的定义为：1/（不同值的个数），SalesOrderDetail表有的ProductID列有266个，因此其密度=1/266=0.003759399，此值正如先前运行的统计对象显示值。由于SQL Server采用的是一致臆断，因此，并不会使用其histogram,对于任何给定的值来说，其数据分布都是相同的，得到的估计行数为总记录行数乘以密度数，即121317 * 0.003759399=456.079，该值的计算也可以通过总记录个数除以ProductID个数（Distinct）即：121317 / 266 = 456.079。

由以上的分析，使用OPTIMIZE FOR UNKNOWN总是得到相同的执行计划，仅确保了选定的计划满足于查询的多数实例。