Microsoft SQL Server 2005中查询优化器使用的统计信息一(zt)-CSDN博客

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

作者： Eric N. Hanson

投稿者：Lubor Kollar

摘要： Microsoft® SQL Server™ 2005收集存储在数据库中关于索引和列数据的统计信息。 SQL Server查询优化器使用这些统计信息来选择用于数据检索和更新操作的最有效执行计划。该白皮书说明了统计信息收集的信息类型，存储位置，以及用于创建、更新和删除统计信息的命令。默认SQL Server 2005在必要时也会自动创建和更新统计信息。该白皮书同时概括介绍了如何在不同的级别（列、表和数据库）上更改与统计信息相关的默认设置。

内容列表

SQL Server 2005中的统计数据. 1

统计特性概述. 1

SQL Server 2005中的统计信息有哪些新特性？. 1

定义. 3

由SQL Server 2005收集的统计信息. 3

创建和显示统计信息：示例. 5

使用SQL Server 2005创建统计信息. 9

在SQL Server 2005中维护统计信息. 10

字符串摘要统计信息. 12

在计算列上的统计信息. 12

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

统计信息和索引视图. 14

管理统计信息的最佳实践. 14

总结. 19

请参阅. 19

SQL Server 2005中的统计数据

Microsoft® SQL Server™ 2005收集关于单列的统计信息（单列统计信息），或者一组列的统计信息（多列统计信息）。查询优化器使用统计信息来评估表达式的选择性，并由此来评估一个查询中间结果和最终结果的规模大小。好的统计信息允许优化器精确地判断不同执行计划的成本，并选择一个高质量的执行计划。一个统计对象的所有相关信息存储在sysindexes表中一行的多个列中，统计信息中二进制大对象（statblob）保存在一张内部表中。此外，可以在新的元数据视图sys.stats和sys.indexes中找到关于统计对象的有关信息。

统计特性概述

SQL Server 2005在维护统计信息方面具有许多特性。最为重要的一点就是能够自动创建和更新统计信息。在SQL Server 2005和SQL Server 2000中默认将启用该功能。大约98%的SQL Server 2000安装将启用该功能，这也是最佳实践。对于大多数的数据库和应用程序而言，开发人员和管理员可以借助自动创建和更新统计信息的功能以提供全面且准确的关于数据的统计信息。这允许SQL Server 2005查询优化器生成一致且有效的查询计划，同时使开发和管理的成本维持在较低的水平。如果您希望进一步控制统计信息的创建和更新以获得最佳的执行计划并管理由于统计信息收集而产生的开销，您可以使用手动创建和更新统计信息的功能。

对于大吞吐量联机事务处理应用环境而言，一个重要的新特性就是以异步方式自动更新统计信息。这种方式可以显著提高OLAP环境中的查询响应时间。

SQL Server 2005中的统计功能允许您：

· 使用默认的抽样比率隐式地创建和更新统计信息。（如果开启了自动创建和更新统计信息功能，那么在SELECT，INSERT，DELETE和UPDATE命令的查询条件如WHERE和UPDATE子句中使用某一列时，将根据需要自动创建或者更新统计信息）

· 使用期望的抽样比率手动创建和更新统计信息、删除统计信息（CREATE STATISTICS, UPDATE STATISTICS, DROP STATISTICS, CREATE INDEX, DROP INDEX）。

· 为数据库中所有表的所有列批量创建统计信息（sp_createstats）。

· 手动更新数据库中所有已存在的统计信息（sp_updatestats）。

· 列出数据库或表中所有统计信息对象（sp_helpstats，目录视图sys.stats, sys.stats_columns）。

· 显示统计信息对象的描述信息（DBCC SHOW_STATISTICS）。

· 在整个数据库级别或者单张表或单个统计对象级别启用或禁用自动创建和更新统计信息功能（ALTER DATABASE的AUTO_CREATE_STATISTICS和AUTO_UPDATE_STATISTICS选项，sp_autostats以及 CREATE STATISTICS和UPDATE STATISTICS的NORECOMPUTE选项）。

· 启用或禁用异步自动更新统计信息（ALTER DATABASE的 AUTO_UPDATE_STATISTICS_ASYNC选项）。

此外，SQL Server Management Studio允许您通过Object Explorer视图以图形化方式浏览和管理统计信息对象。Object Explorer在每个表对象下方的文件夹中列出所有的统计对象。

SQL Server 2005中的统计信息有哪些新特性？

SQL Server 2005包含许多新的统计特性，这些新特性允许查询优化器为各种类型的查询选择更佳的执行计划，或者用于增强统计信息的管理功能。这些增强包括：

· 字符串摘要统计：为字符类型的列维护的关于字符串子串频率分布的信息。这些信息可以帮助查询优化器更好地评估那些使用LIKE操作符的查询条件的选择性。

· 异步自动更新统计信息：SQL Server 2005中ALTER DATABASE命令提供了新的AUTO_UPDATE_STATISTICS_ASYNC选项。默认该选项是关闭的。如果启用该选项，SQL Server 2005将在后台进行自动统计信息更新，这样查询就不会由于统计信息更新而被阻塞，而是继续使用旧的统计信息进行处理。这种方式可以显著提高某些工作负载的响应时间。

· 计算列上的统计信息： 可以在计算列上手动或自动地创建和更新统计信息（SQL Server 2000仅部分支持该功能，且相关的文档也不多）

· 大对象支持：可以将数据类型为ntext，text和image，也包括新数据类型nvarchar(max), varchar(max)以及varbinary(max) 的列指定为统计列。

· 增强的统计信息加载框架：同SQL Server 2000相比，查询优化器在内部对统计信息的加载进行了增强。如今优化器将加载所有必须加载的统计信息，从而提高了优化的质量和性能。

· 在计算列上自动创建统计信息功能的增强：如果查询中包含了与某个计算列表达式等价的表达式，那么SQL Server 2005将在需要时自动在计算列上创建统计信息。

· 最小抽样规模： 在收集统计信息时最少抽样8 M数据。

· 统计信息数目限度的增强：每张表上允许创建统计信息的列数增加到了2,000。此外还允许同时存在249个索引统计，即每张表上最多允许2,249个统计信息。

· 增强的DBCC SHOW_STATISTICS输出：如今DBCC SHOW_STATISTICS将显示统计对象的名称，从而避免了二义性。

· 基于列的更新计数器的自动更新统计信息：SQL Server 2000根据表中更新的行数来决定何时更新统计信息。如今是在列的级别上跟踪数据变更的，这样就可以避免在那些变更还不够充分的列上自动更新统计信息。

· 内部表上的统计信息：可以在sys.internal_tables列出的所有内部表上创建统计信息，包括XML和全文索引、服务代理队列，以及查询通知表。

· DBCC SHOW_STATISTICS单一结果集输出：DBCC SHOW_STATISTICS支持将标题、密度信息以及直方图作为单个结果集输出。这样更便于通过编程的方式自动处理DBCC SHOW_STATISTICS的输出结果。

· 统计信息最多可支持32列：在一个统计对象中允许的列数由16列增加到32列。

· 分区表统计信息：分区表是SQL Server 2005的新特性，并且完全支持统计信息功能。SQL Server为每张表（而不是每个分区）维护直方图信息。

· 以并行方式进行fullscan统计信息收集：使用fullscan方式收集统计信息时，可以通过并行方式在无分区表或分区表上创建单个统计信息对象。

· 当缺失统计信息时增强的重新编译和创建统计信息功能：在自动创建统计信息失败的情况下，如果以后生成执行计划时统计信息是缺失的，那么优化器将自动创建统计信息并重新编译执行计划；因此不会持续出现统计信息缺失的情况。更多详细信息请阅读由Marathe发表的白皮书SQL Server 2005中批编译、重新编译，以及计划缓存的问题。

· 增强的对空表重新编译的逻辑和更新统计信息功能：表中行数从0变化到大于0将导致查询被重新编译以及统计信息被更新。更多详细信息请阅读由Marathe发表的白皮书SQL Server 2005中批编译、重新编译，以及计划缓存的问题。

· 更加清晰和一致的统计信息直方图显示：由于保存直方图之前总是先将直方图按比例进行缩放，因此增强了DBCC SHOW_STATISTICS显示直方图的效果。

· 推导的数据关联约束：通过启用 DATE_CORRELATION_OPTIMIZATION数据库设置，SQL Server可以维护通过外键连接的两张表的datetime列之间的关联信息。某些查询可以使用这些信息对隐含的条件进行判断。尽管这些信息并不直接用于查询优化器进行选择性评估或成本估算，因此从严格意义上来讲，这些信息并不是统计信息，但却与统计信息密切相关，因为它们可以作为辅助信息以帮助优化器获得更好的查询计划。

· sp_updatestats: 在SQL Server 2005中，sp_updatestats根据sys.sysindexes兼容视图中记录的rowmodctr信息仅对那些需要更新的统计信息进行更新。对于那些运行在90或更高兼容级别下的数据库，sp_updatestats将保持特定索引或统计信息对象的自动UPDATE STATISTICS配置选项。

此外统计信息的行为还有其它一些细微的变化。特别的，sys.sysindexes表statblob列的值始终为NULL，statblob本身存储在一张内部目录表中。

定义

我们先定义以下这些与SQL Server 2005统计信息相关的术语：

· statblob：二进制大对象统计信息。该对象存储在一个内部目录视图sys.sysobjvalues中。

· 字符串摘要：对字符串列的子串的频率分布进行汇总的一种统计信息，用于帮助评估LIKE谓词的选择性。字符串列的摘要信息存储在statblob中。

· sysindexes：sys.sysindexes目录视图，包含了表和索引的相关信息。

· 谓词：结果为true或者false的判定条件。谓词出现在数据库查询的WHERE或者JOIN子句中。

· 选择性：满足谓词的行数占输入数据集行数的比例。更复杂的选择性评估可用于估算连接、DISTINCT以及其它操作产生的行数。例如，SQL Server 2005估算AdventureWorks数据库的“Sales.SalesOrderHeader.OrderID = 43659”谓词的选择性是1/31465 = 0.00003178。

· 基数评估：估算结果集的大小。例如：如果表T包含100,000行记录，查询包含了形如T.a=10的选择谓词，直方图显示T.a=10的选择性是10%，那么查询要考虑的表T中满足条件的行数，即基数评估就是10% * 100,000 = 10,000。

· LOB: 大对象（text, ntext, image, varchar(max), nvarchar(max), varbinary(max) 类型的值）。

由SQL Server 2005收集的统计信息

SQL Server 2005在表级别维护以下信息。这些信息并不属于统计信息对象，而是SQL Server 2005在某些情况下用来进行查询成本估算的。

· 表或索引的行数（sys.sysindexes表的rows列）

· 表或索引占用的页面数（sys.sysindexes表的dpages列）

SQL Server 2005收集关于表中列的下述统计信息，并存储在一个统计信息对象中（statblob）：

· 统计信息收集的时间。

· 用于生成直方图和密度信息的行数（将在下面讲解）。

· 平均键的长度。

· 单列直方图，包含了步数。

· 字符串摘要（如果某一列含有字符串信息）。如果一个统计信息对象包含了字符串摘要，那么DBCC SHOW_STATISTICS输出就会包含“String Index”列，并且该列的值为YES。

一个直方图是给定列的最多200个值的集合。给定列的所有值或抽样值会被排序；排序后的序列最多被划分为199个间隔，以便获得最有效的统计信息。间隔的大小通常是不等的。下面的值或足以派生这些值的信息被存储在直方图的每一步中。

RANGE_HI_KEY

表示直方图步数上界的键值。

RANGE_ROWS

指定区间中包含的行数（这些行小于RANGE_HI_KEY，但大于上一个较小的RANGE_HI_KEY）。

EQ_ROWS

指定与RANGE_HI_KEY完全相等的行数。

AVG_RANGE_ROWS

区间中每个唯一值的平均行数。

DISTINCT_RANGE_ROWS

区间中唯一键值的数目（不包括RANGE_HI_KEY本身以及之前的值）；

SQL Server 2005只能为单列，或者多列统计信息对象中的第一列创建直方图。

SQL Server 2005通过三步从已排序的一组列值中创建直方图：

· 初始化直方图：在第一步，依次处理以排序集中第一个值作为起点的每个值，最多收集200个RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS以及DISTINCT_RANGE_ROWS（在这一步中，RANGE_ROWS和DISTINCT_RANGE_ROWS始终为零）。当所有输入都被用尽，或者已经找到了200个值，第一步就结束了。

· 使用桶合并进行扫描：按照排序方式处理统计信息中首列的其余列值；每个值要么被添加到最后一个区间，要么被添加到一个新区间（由于输入的值已经排序，因此允许这样做）。如果创建了一个新区间，现有相邻的一对区间将被合并到一个区间。选取相邻的一对区间是为了将信息损失的程度降到最低。整个过程当所有区间合并完成后步数依然是200。该方法是基于maxdiff直方图的一种变形方法。

· 合并直方图：在第三步，如果信息损失的数量不十分明显，就有更多的区间被合并。因此，即使列中唯一值的数目超过了200，那么直方图的步数也可能少于200。

如果通过抽样来生成直方图，那么 RANGE_ROWS、EQ_ROWS、DISTINCT_RANGE_ROWS 和 AVG_RANGE_ROWS的值将为估计值，因此它们不必都是整数。

密度是给定的一列或组合列上关于重复项数目的信息，其计算公式为1/（唯一值数目）。当在一个等值判断表达式中使用了某一列时，就会使用从直方图导出的密度信息来估算满足条件的行数。还可以使用直方图来估算非等值选择判断、连接以及其它操作的选择性。

除了包含用于显示统计信息收集时间的时间戳、表中的行数、为生成直方图而抽样的行数、密度信息和平均的键长度、直方图本身以外，在单列统计信息中还包含了统计信息列集中组成前缀的每一组列的All density值。这些信息显示在DBCC SHOW_STATISTICS输出的第二个结果集中。All density 1/（前缀列集中唯一值得数目）。下一部分将给出相关的一个示例。

注意：dbcc show_statistics返回结果的首行中包含的密度值是所有抽样值的密度而不是RANGE_HI_KEY的密度。RANGE_HI_KEY通常是数据分布中出现频率较高的值。因此该命令所显示的密度值为那些出现频率不高的列值提供了潜在的有价值的信息。

在组合列上创建的多列统计信息包括：统计信息定义中首列的直方图，首列的密度值，以及每个前缀组合列（单独包括首列）的密度值。每组多列统计信息（一个直方图加上两个或多个密度值）都存储在一个statblob中，再加上上次更新统计信息的时间戳、用于生成统计信息的抽样行数、直方图的步数和平均键长度。只包含首列的字符串摘要，如果首列中含有字符型数据。

使用sp_helpindex和sp_helpstats来显示特定表上所有可用的统计信息。sp_helpindex列出表上所有的索引，而sp_helpstats列出表上所有的统计信息。每个索引都含有索引列的统计信息。使用CREATE STATISTICS命令创建的统计信息与在同一列上使用CREATE INDEX命令创建的统计信息是等价的。唯一不同的是CREATE STATISTICS命令默认使用抽样而CREATE INDEX则使用fullscan收集统计信息，因为创建索引时无论如何都必须处理所有的行。