Microsoft SQL Server 2005中查询优化器使用的统计信息三(zt)

Microsoft SQL Server 2005中查询优化器使用的统计信息三(zt)

SQL Server 2005在数据库、表、索引或者统计信息对象级别维护自动更新统计信息的设置。当某人使用sp_autostats命令修改了某张表上所有对象的该项设置时，SQL Server通过单独修改表上每个统计信息对象和索引的设置来实现。不存在直接记录整张表自动更新统计信息设置为ON或OFF的元数据。

下表显示结合不同的数据库、表、索引设置的效果：

数据库设置	索引或统计信息对象设置	该对象自动更新统计信息的有效性
ON	ON	ON
ON	OFF	OFF
OFF	ON	OFF
OFF	OFF	OFF

在数据库级别设置的OFF是无法通过在统计信息对象级别将自动更新统计信息选项设置为ON来改写的。

自动更新统计信息始终通过对表或索引按默认的抽样比率进行抽样来完成。要想显式地设置抽样比率，运行CREATE或者UPDATE STATISTICS。

更新统计信息包含在与创建统计信息相同的SQL Profiler事件中。

字符串摘要统计信息

SQL Server 2005包含了专利技术用于评估LIKE条件的选择性。该技术为字符型列的子串的频率分布创建统计摘要（字符串摘要），包括数据类型为text，ntext，char，varchar和nvarchar的列。使用字符串摘要，SQL Server能够精确估算LIKE条件的选择性，且LIKE条件的字符串匹配模式中可以包含以任意方式组合的任何通配符。例如，SQL Server可以估算以下形式的谓词的选择性：

Column LIKE 'string%'

Column LIKE '%string'

Column LIKE '%string%'

Column LIKE 'string'

Column LIKE 'str_ing'

Column LIKE 'str[abc]ing'

Column LIKE '%abc%xy'

如果在LIKE模式中存在用户指定的逃逸字符（例如，LIKE模式 ESCAPE逃逸字符），那么SQL Server 2005将猜测选择性。

SQL Server 2005在此处对SQL Server 2000进行了改进，SQL Server 2000对于LIKE模式中使用的任何通配符（除非用于字符串尾部的%）都通过猜测来估算选择性，因此估算的准确性十分有限。

如果统计对象中还包含了字符串摘要，那么DBCC SHOW_STATISTICS返回的第一个结果集的String Index字段的值显示为YES，但字符串摘要的内容并不显示出来。字符串摘要中包含了许多无法通过直方图查看的其他信息。

如果字符串长度超过了80个字符，那么会在创建字符串摘要之前从字符串中提取前40个和后40个字符，然后进行连接。因此无法对那些在字符串被忽略的部分出现的子串进行准确的频率评估。

在计算列上的统计信息

SQL Server 2005支持在计算列上创建、更新和使用统计信息，即使查询中包含的不是计算列的名称，而是计算列的表达式。SQL Server 2000则只能对那些在查询中使用名称的计算列自动创建、更新和使用统计信息。

如果您在SQL Server 2005中执行下面的Transact-SQL脚本，就可以观察到为AdventureWorks数据库的Sales.SalesOrderHeader.TotalDue列自动创建的计算列统计信息：

USE AdventureWorks

GO

-- 删除Sales.SalesOrderHeader上所有的统计信息对象

DECLARE c CURSOR FOR

SELECT name FROM sys.stats

WHERE object_id = object_id('Sales.SalesOrderHeader')

AND auto_created <> 0 AND user_created <> 0

DECLARE @name NVARCHAR(255)

OPEN c

FETCH next FROM c INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC ('drop statistics Sales.SalesOrderHeader.' + @name)

FETCH NEXT FROM c INTO @name

END

CLOSE c

DEALLOCATE c

-- 使用与TotalDue计算列等价的表达式

--((isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))

-- 查询Sales.SalesOrderHeader表

SELECT *

FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderID

AND (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) > 200000.00

ORDER BY TotalDue DESC

-- 列出已创建的统计信息对象。观察即使未在查询中引用TotalDue列，但

-- SQL Server依然为该列创建了统计信息

sp_helpstats 'Sales.SalesOrderHeader'

SQL Server 2005不支持在包含了CLR （公共语言运行时）表达式的non-persisted计算列上创建统计信息，例如包含了一个CLR用户定义函数的列。要想在CLR计算列上创建统计信息，列必须被标记为PERSISTED。

在CLR用户定义类型的列上的统计信息

如果用户定义类型支持二进制排序，那么SQL Server 2005支持在CLR用户定义数据类型的列上创建、更新和使用统计信息，不支持二进制排序的用户定义类型上是不支持统计信息的。在类型定义中，如果在SqlUserDefinedType属性中将IsByteOrdered设置为true，那么类型就是支持二进制排序的。 某种类型支持二进制排序意味着该类型从语义上来讲正确的顺序就是标准的二进制排序顺序。

统计信息和索引视图

通常索引视图并不需要统计信息。这是因为在索引视图被引入查询计划之前，所有底层表和索引的统计信息已经被附加到了查询计划中。但是也有一个例外：如果在FROM子句中使用NOEXPAND提示直接引用了视图，那么将使用统计信息。注意：如果在没有索引的视图上使用NOEXPAND提示将导致错误，也不会创建执行计划。

由于索引视图统计信息受限制的使用方式，因此使用sp_createstats不会在索引视图上创建统计信息，使用sp_updatestats也不会更新索引视图上的统计信息。auto update和auto create statistics特性可以在索引视图上工作。但是和前面一样，请注意只有在查询中通过NOEXPAND提示使用索引视图，并且自动创建/更新统计信息选项被开启时，查询优化器才需要并随后创建统计信息您也可以在索引视图列上以手动方式执行CREATE STATISTICS，或者使用UPDATE STATISTICS手动地更新某一列或索引的统计信息。

管理统计信息的最佳实践

在SQL Server中使用统计信息的目标就是让查询优化器获得良好的cardinality estimates，这样就可以找到好的查询执行计划，同时将任何与统计信息收集有关的开销或延迟控制在合理的范围内。下面我们将列出在SQL Server中管理统计信息的最佳实践，最重要的排在第一位。

使用自动创建和自动更新统计信息

对于绝大多数SQL Server安装而言，最重要的最佳实践就是在整个数据库范围内使用自动创建和自动更新统计信息。默认情况下自动创建和自动更新统计信息是启用的。如果您发现了糟糕的执行计划并且怀疑是由于缺失统计信息或过时的统计信息引起的，请验证自动创建和自动更新统计信息被启用了。

如果需要，有选择性的使用FULLSCAN统计信息

如果您正在使用自动创建和自动更新统计信息，由于不准确或非最新的统计信息您获得了一个糟糕的执行计划，请您完成下面的步骤：

· 继续启用自动创建和自动更新统计信息，然后

· 仅仅对于那些不准确或非最新的统计信息，使用 CREATE STATISTICS … WITH FULLSCAN, NORECOMPUTE 以及一个批处理作业，负责做 UPDATE STATISTICS … WITH FULLSCAN, NORECOMPUTE periodically.

统计信息更新的频率取决于您的应用程序，可能还需要一些经验进行定夺。一个不错的fullscan统计信息更新频率的参照点是：如果您正在关注的表更新频率较高，那么每天晚上运行一次fullscan统计信息更新。如果表的更新频率较低，那么每周运行一次fullscan统计信息更新。

避免在查询中使用局部变量

如果在查询谓词中使用局部变量而不是参数或者文字常量，那么会降低优化器对谓词选择性的估算质量，甚至猜测谓词的选择性。在查询中使用参数或文字常量取代局部变量，查询优化器通常将选取一个更好的执行计划。例如，考虑下面使用一个局部变量的查询：

declare @StartOrderDate datetime

set @StartOrderDate = '20040731'

select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId

AND h.OrderDate >= @StartOrderDate

优化器估算Sales.SalesOrderHeader表中满足h.OrderDate >= @StartOrderDate条件的行数是9439.5，即表大小的30%。您可以使用该查询的图形化执行计划并右键单击Sales.SalesOrderHeader执行计划节点来查看基数估算。由于准备该白皮书时使用的是SQL Server 2005预览版，因此选中了使用合并连接的执行计划（以下报告是基于相同的SQL Server 2005版本，根据您的SQL Server版本及可用内存等配置的不同，您的结果可能会有所不同）。现在，考虑一个等价但不使用局部变量的查询：

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId

AND h.OrderDate >= '20040731'

在查询的图形化显示计划中（右键单击filter运算符），谓词“h.OrderDate >= '20040731'”的选择性是0.13%，那么结果集的基数估算是40。由于提高了估算的准确性，因此嵌套循环连接而不是合并连接的执行计划被选中。

即使在查询中使用了局部变量，那么在等值判定谓词中估算选择性也好于猜测选择性。“@local_variable = column_name”形式的查询条件的选择性是使用column_name列直方图的平均数据分布频率来估算的。因此，举例来说，如果column_name列的值全都是唯一值，那么使用1/（列中唯一值数目）来估算选择性就是准确的。

要消除对局部变量的使用，请考虑：（1）重写查询，使用文字常量取代变量（2）通过带参数的sp_executesql取代局部变量的使用（3）使用带参数的存储过程替换局部变量的使用。通过EXEC执行动态的SQL语句也可以消除局部变量的使用，但这样做通常会导致更高的编译开销。

考虑限制使用多语句的TVFs和表变量

多语句的表值函数（TVFs）没有统计信息。查询优化器必须猜测函数运算结果规模的大小。类似的，表变量也没有统计信息，对于它们的基数优化器也必须采用猜测的方式。如果是因为这种猜测而导致一个糟糕的执行计划，那么考虑使用一张标准表或者临时表作为TVF运算结果的临时存储，或者作为表变量的替换。这样将允许查询优化器做出更佳的基数评估。

不可折叠表达式和内置的单值函数可能导致猜测

SQL Server在编译时只能计算那些包含常量的表达式。这称为constant folding。在估算选择性时可折叠的表达式将作为文字常量处理，而不可折叠的表达式将导致猜测。例如，考虑下面的Transact-SQL脚本。该脚本加工一个含有200行的UserLog。半数行的UserName值不相同的而另外半数行的UserName值是相同的，导致了数据的扭曲分布。

IF object_id('UserLog') IS NOT NULL

DROP TABLE UserLog

GO

CREATE TABLE UserLog (UserName NVARCHAR(255), Action NVARCHAR(1000))

DECLARE @i INT

SET @i = 1

SET nocount ON

WHILE @i <= 100

BEGIN

INSERT UserLog VALUES(suser_sname(), 'login')

INSERT UserLog VALUES(newid(), 'login')

SET @i = @i + 1

END

内置的suser_sname() 函数为当前的Windows用户返回domain_nameuser_name，而newid() 用于返回唯一的用户名。现在我们运行两个等价的查询。下面显示的第一个查询包含了对UserName = suser_sname()的预测。查询优化器不得不去猜测结果的基数，猜测的值为1.98（由于SET STATISTICS XML ON，您可以在生成的XML显示执行计划的EstimateRows属性中看到这个值）。由于实际的基数是100，因此猜测的值有很大的偏差。

GO

SET STATISTICS XML ON

GO

SELECT * FROM UserLog WHERE UserName = suser_sname()

GO

SET STATISTICS XML OFF

GO

第二个查询通过sp_executesql执行了一个参数化查询。suser_sname()值作为参数传递给查询，而不是作为表达式出现在查询中。

SET STATISTICS XML ON

GO

DECLARE @UserName NVARCHAR(255)

SET @UserName = suser_sname()

EXEC sp_executesql N'SELECT * FROM UserLog WHERE UserName = @n',

N'@n nvarchar(255)', @UserName

GO

SET STATISTICS XML OFF

GO

这一次查询优化器对于UserName = @n的选择性使用了50%的准确评估。如果您查看生成的XML执行计划，您将发现EstimateRows是100，是完全正确的。 如果在一个更庞大的数据集上执行一个更长更复杂的查询，那么这种错误可能导致一个糟糕的执行计划被选中。如果您的应用程序存在这个问题，考虑使用上面举例阐述的技术。使用sp_executesql或者包含了存在问题查询的存储过程，然后将不可折叠表达式预先计算好的结果作为参数传递给存储过程。这样就可以解决您的问题并因此获得良好的基数估算。

在包含多列查询条件的查询中使用多列统计信息

当查询中含有多列查询条件时，如果您怀疑查询优化器没有为查询生成最佳执行计划，考虑使用多列统计信息。创建多列索引时自动产生多列统计信息，因此如果已经存在了一个多列索引并且该索引支持多列查询条件，那么就无需显示地创建多列统计信息了。自动创建统计信息只创建单列的统计信息，永远不会创建多列统计信息。因此如果您需要多列统计信息，要么手动创建它们，要么创建一个多列索引。

考虑存取AdventureWorks.Person.Contact表的查询，它包含了如下的查询条件：

FirstName = 'Catherine' AND LastName = 'Abel'

创建下面的统计信息对象，查询将获得更加精确的选择性估算：

CREATE STATISTICS LastFirst ON Person.Contact(LastName,FirstName)

该统计信息对象对于在LastName和FirstName进行条件判断，以及单独在LastName列上作条件判断的查询将很有益。通常，一个多列统计对象任何前缀列上的谓词选择性均可以使用该统计信息对象进行估算。

要想使一个统计对象完全支持多列的查询条件，那么多列统计信息对象的列前缀必须包含查询条件中所有的列。例如在列（a,b,c）上创建的多列统计信息对象只能够部分地支持a=1 AND c=1的查询条件。SQL Server将使用直方图估算a=1的选择性但不会使用c的密度信息，因为查询条件中缺失了b。在（a,c）或者（a,c,b）上创建的多列统计信息将支持查询条件a=1 AND c=1，并且可以使用密度信息来提高选择性估算的准确性。

警惕会导致SQL Server猜测选择性的情形

SQL Server在几种情形下会猜测选择性。通常猜测是合理的，如果数据的规模很小，或者猜测不会导致糟糕的执行计划，那么猜测都不成问题。然而，SQL Server猜测查询谓词的选择性有时会导致非最佳的执行计划。如果某个查询的性能并非如您所愿，并且您怀疑一个非最佳的执行计划被选中，请查看查询和执行计划中是否有迹象表明选择性是通过猜测而不是根据统计信息估算的。在很多情况下您可以通过稍微修改查询或应用程序就能够避免猜测。下表列出了可以导致猜测的一些情形，以及可能的解决办法：

· 缺失统计信息： 检查是否启用了自动创建统计信息或者确保使用CREATE STATISTICS或 sp_createstats手动创建了统计信息。检查数据库是否为只读的，如果是，应防止自动创建统计信息生效并工作。

· 使用局部变量： 在查询条件中（该问题已在前面阐述）

· 非常量-可折叠的表达式：在查询条件中（例如：T.x+1 = 0，suser_sname() = T.UserName）。 重写查询以避免出现表达式，或者在查询执行前求出表达式的值，再将结果作为参数（而不是局部变量）传递给查询。就T.x+1 = 0而言，将表达式重写为T.x = -1，这样不仅结果相同而且还允许进行精确的评估而不是猜测。

· 复杂表达式： 例如 "Price + Tax >100" 或 "Price * (1+TaxRate) > 100"。如果在这种情况下您遇到了低于期望的查询性能，考虑使用同样的表达式创建一个计算列，并且在计算列上创建统计信息或索引。如果存在计算列的话，自动创建统计信息也同样会为计算列创建统计信息，这样如果启用了“自动创建统计信息”，您就无需手动为计算列创建统计信息了。

避免在查询中使用参数值之前就在SP中修改存储过程的参数值

为获得最佳的查询性能，在某些情况下应避免在存储过程体中为参数分配新的值，然后在查询中使用该参数值。存储过程以及其包含的所有查询最初将使用首次传递进来的查询参数进行编译。这有时也称为参数嗅探。 考虑下面的存储过程，它用于获取在某个指定日期或之后的销售值，如果将NULL作为参数传递给存储过程，则获取最后三个月的销售值：

CREATE PROCEDURE GetRecentSales (@date datetime) WITH RECOMPILE AS

BEGIN

IF @date IS NULL

SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE)

FROM Sales.SalesOrderHeader))

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderID

AND h.OrderDate > @date

END

如果使用NULL调用该存储过程，最后的SELECT语句将根据@date = NULL进行优化。由于不存在OrderDate为NULL的行，因此当在SalesOrderHeader上应用该过滤条件时，对结果的基数估算非常低（1行）。可是在存储过程运行时日期却并不为NULL，而是最后的OrderDate之前的三个月。 满足条件的SalesOrderHeader记录真正有5,736行。当将NULL传递给GetRecentSales时，查询优化器为该查询选择了嵌套循环连接的执行方式，而优化的执行计划则是使用合并连接方式。您可以使用下面的脚本查看选中的执行计划，以及估算的和实际的基数：

SET STATISTICS PROFILE ON

GO

EXEC GetRecentSales NULL

GO

SET STATISTICS PROFILE OFF

GO

注意在前面的GetRecentSales存储过程上指定的WITH RECOMPILE并没有避免基数估算的错误。为了确保该示例中的查询能够根据正确的参数值进行优化，并因此获得良好的选择性估算，方法之一就是按以下方式修改存储过程，将其分为几部分：

CREATE PROCEDURE GetRecentSales (@date datetime) AS

BEGIN

IF @date IS NULL

SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE)

FROM Sales.SalesOrderHeader))

EXEC GetRecentSalesHelper @date

END

CREATE PROCEDURE GetRecentSalesHelper (@date datetime) WITH RECOMPILE AS

BEGIN

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderID

AND h.OrderDate > @date -- @date is unchanged from compile time,

-- so a good plan is obtained.

END

考虑对降序排序的键进行更频繁地统计信息收集

键值降序排序的列，例如IDENTITY列或者表示现实生活中时间戳的datetime列，由于频繁地插入数据会导致表中的统计信息不准确，因为新插入的值都位于统计信息直方图之外。如果您的应用程序看似使用了不适当的查询计划为那些使用键值降序排序的列作为查询条件的查询，考虑使用批处理作业更频繁地更新这些列上的统计信息。多久运行一次批处理作业运行取决于您的应用程序。考虑每天或每周运行一次，如果您的应用需要，也可以更频繁。

使用异步更新统计信息如果同步更新统计信息导致不希望的延迟

如果您有一个大型数据库且进行OLTP处理，并且启用了AUTO_UPDATE_STATISTICS，那么有些通常只需零点几秒就可以运行完成的事务将由于统计信息自动更新而耗费几秒甚至更长时间才能运行结束。如果您希望避免这种明显延迟出现的可能性，请启用 AUTO_UPDATE_STATISTICS_ASYNC。对于那些长时间运行的工作负载而言，一个更好的执行计划要比编译时不经常出现的延迟更为重要。在这种情况下，请使用异步而不是同步方式自动更新统计信息。

总结

SQL Server 2005中包含许多关于统计信息管理能力的增强。最重要的是，在大多数情况下您可以借助自动创建和自动更新统计信息以确保良好的执行计划。 当使用默认抽样比率的自动统计功能无法满足需求时，您还可以显式地控制统计信息的抽样比率、创建和更新时间。如果您发现存在非最佳的执行计划是与统计信息或代价评估有关的，请考虑使用该白皮书中描述的最佳实践。

请参阅

[Mar04] Arun Marathe, SQL Server 2005中批编译，重新编译和计划缓存的问题 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx, July 2004.

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

转载于:http://blog.itpub.net/756652/viewspace-242526/