ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OPTIMIZE FOR Unknown工作原理

OPTIMIZE FOR Unknown工作原理

原创 Linux操作系统 作者:bigholy 时间:2011-10-19 10:06:38 0 删除 编辑

原文: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

image

在此情况下,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

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

image

在优化阶段,本地变量未知,查询优化器无会使用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总是得到相同的执行计划,仅确保了选定的计划满足于查询的多数实例。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-709335/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    83565