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

最新推荐文章于 2019-07-15 17:14:37 发布

congnen9588

最新推荐文章于 2019-07-15 17:14:37 发布

阅读量86

点赞数

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

-- 在firstname和lastname列上创建统计信息

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

-- 显示表上现在有三个统计信息对象

sp_helpstats N'Person.Contact', 'ALL'

结果：

统计信息名称	统计信息键
_WA_Sys_00000002_1B29035F	LastName
FirstLast	FirstName, LastName
Phone	Phone

-- 显示LastName列的统计信息

DBCC SHOW_STATISTICS (N'Person.Contact', LastName)

结果：

统计对象的标题信息：

名称	上次更新时间		行数		抽样行数	步数	密度	平均键长度	字符串索引
_WA_Sys_ 00000002_ 1B29035F	Mar 25 2005 11:21AM	5		5		4	0	13.6	YES

列集的前缀以及相关的密度和长度：

所有密度	平均长度	列
0.25	13.6	LastName

直方图步数：

RANGE_HI_ KEY	RANGE_ ROWS	EQ_ROWS	DISTINCT_ RANGE_ROWS	AVG_ RANGE_ROWS
Andersen	0	2	0	1
Smith	0	1	0	1
Williams	0	1	0	1
Zhang	0	1	0	1

-- If you take the name of the statistics object displayed by

-- the command above and subsitute it in as the second argument of

-- DBCC SHOW_STATISTICS you can form a command like the following one

--(the exact name of the automatically created statistics object

-- will typically be different for you).

DBCC SHOW_STATISTICS (N'Person.Contact', _WA_Sys_00000002_2D7CBDC4)

-- Executing the above command illustrates that you can show statistics by

-- column name or statistics object name.

-- The following displays multi-column statistics. Notice the two

-- different density groups for the second rowset in the output.

DBCC SHOW_STATISTICS (N'Person.Contact', FirstLast)

结果（仅第二个结果集）

列集的前缀以及相关的密度和长度：

所有密度	平均长度	列
0.3333333	11.6	FirstName
0.25	25.2	FirstName, LastName

如果希望看到一张大型表的完整生成的直方图，运行下面的命令：

USE AdventureWorks

-- 清理以前运行脚本产生的对象

IF EXISTS (SELECT * FROM sys.stats

WHERE object_id = object_id('Sales.SalesOrderHeader')

AND name = 'TotalDue')

DROP STATISTICS Sales.SalesOrderHeader.TotalDue

CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)

DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', TotalDue)

使用SQL Server 2005创建统计信息

您可以在SQL Server 2005中通过以下描述的几种不同的方式创建统计信息。

· 当AUTO_CREATE_STATISTICS开启时（这是默认设置），查询优化器对SELECT, INSERT, UPDATE和DELETE语句进行优化时自动创建单列的统计信息。

· SQL Server 2005中有两个基本语句可以显式地生成上面描述的统计信息：CREATE INDEX首先生成索引，然后再为构成索引键的那些复合列（但并不包含其它列）生成一组统计信息。CREATE STATISTICS为指定的一列或复合列生成统计信息。

· 此来还有几种其它方法可以创建统计信息或索引。但从根本上来说都是使用上面的两个命令。使用sp_createstats为当前数据库中所有用户表的所有符合条件的列创建统计信息（除了XML列）。如果列上几经具有了统计信息对象，则不再为该列创建新的统计信息对象。

· 使用dbcc dbreindex重建指定数据库中某张表上的一个或多个索引。

· 在“Management Studio”中展开Table对象下面的文件夹，右键单击Statistics文件夹，选择New Statistics。

· 使用数据库优化向导（DTA）创建索引。

这里是在 AdventureWorks.Person.Contact表上使用CREATE STATISTICS命令的示例：

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

WITH SAMPLE 50 PERCENT

通常，使用默认抽样比率的统计信息足以生成一个好的执行计划。但是，也存在增加抽样规模使统计信息更利于查询优化的情况，例如某个列上抽样值并非随机的。如果数据是排序的或者聚簇的，那么就可能产生非随机抽样。创建索引或者将数据加载到已经排序或聚簇的堆中都可能导致数据排序或者聚簇。最常用的大规模抽样就是fullscan，因为它能够带来最准确的统计信息。使用更大规模抽样的统计信息其代价就是创建统计信息所耗费的时间。

上面的例子创建了一个2列的统计信息对象。在这个例子中，由于表太小了，因此忽略了SAMPLE 50 PERCENT而是执行一次完全扫描。抽样主要是为了避免大量的数据扫描，只有包含了1,024或更多页面（8 MB）的表或索引才会进行抽样。

在SQL Server 2005中，当创建索引时会自动创建统计信息。当编译查询时，SQL Server也会自动创建单列统计信息。优化器为那些需要估算密度或数据分布的列自动创建统计信息。以下是该规则的几个例外：当(1)数据库是只读的(2) 太多的显著的查询编译正在进行中(3) 列的数据类型不支持自动创建统计信息，此时SQL Server 不会自动创建统计信息。

通过执行下面的语句可以在数据库级别禁用自动创建统计信息功能

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

同样地，执行下面的语句在数据库级别启用自动创建统计信息功能

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON

建议您将该选项设置为ON。只有当您需要解决某些性能问题，例如需要为某些表指定不同于默认值的抽样比率时，才禁用该选项。

默认情况下，当执行CREATE STATISTICS命令或者自动创建统计信息时，是通过对数据集抽样的方式来创建统计信息。CREATE INDEX总是扫描整个数据集，因此最初创建的索引统计信息是没有抽样的（相当于fullscan）。CREATE STATISTICS命令允许您设置抽样规模，在WITH子句中要么指定fullscan，要么指定扫描数据或行数的百分比。后者作为近似值来处理。也可以在UPDATE STATISTICS命令中指定WITH RESAMPLE来继承原有的抽样规模。这一点对于那些在某些列有索引（最初通过fullscan统计创建的）而其他一些列上只有统计信息（最初通过SAMPLE统计创建的）的表来说是十分重要的。在UPDATE STATISTICS上使用resample选项将为索引维持fullscan统计，为其余的列维持抽样统计。

对于小型表至少需要抽样8MB的数据。如果一张表开始时很小，您使用默认的抽样比率进行抽样，此后表的大小增长超过了8MB，那么当您使用resample选项更新统计信息时，您得到的同样还是fullscan。如果您希望默认的抽样比率随着表的规模而变化，则应该避免使用resample。

resample抽样比率是在前一次统计信息计算时根据已抽样行数和表中总行数的函数进行计算的。由于真实的抽样比率可能因抽样随机性的特点而变化，因此对于非完全扫描抽样来说，resample只是近似于上一次的抽样比率。如果希望反复一致地进行抽样，在使用UPDATE STATISTICS之前显式地指定相同的抽样比率而不是使用resample。

dbcc show_statistics命令在Rows Sampled heading下面显示抽样规模。自动创建的统计信息，或者自动更新的统计信息（将在下一部分描述）总是进行默认的抽样。默认的抽样比率是一个表规模的慢速增长函数，这样即使是十分大型的表也可以相对快速地收集统计信息。

当创建和更新统计信息时，查询优化其必须选择一种存取路径来收集统计信息。存取路径可以包含堆、聚簇索引，或者非聚簇索引。对于抽样的统计信息，优化器尽量避免那些对统计信息首列进行物理排序的存取路径，这样做有助于提供更随机化的抽样，也因此导致更加精确的统计信息。对于那些没有对统计信息健值作排序的存取路径（如果存在这样的存取路径），则选择其中成本最低的一种，这就是最精确的索引或者堆。对于fullscan统计，由于存取路径的排序顺序对于统计信息的准确性无关紧要，因此成本最低的存取路径将被选中。

SQL Server Profiler可以监视自动的统计信息创建。Auto Stats事件属于Performance跟踪事件组. 当定义跟踪时，还要同时选中Auto Stats的IntegerData，Success和ObjectID列。一旦捕获到AutoStats事件，Integer Data包含了在指定表上创建或者更新的统计信息的数目， Object ID为表的ID，TextData（默认包含在跟踪定义中）包含了创建或者更新统计信息的列名，再加上Updated: 或者Created: 前缀。Success包含了Auto Stats操作成功或者失败的标记。需要特别指出的是，Success有三个可能的值：

名称	值	定义
FAILED	0	由于某种原因（除了THROTTLED，见下面），例如：数据库是只读的，自动创建或者更新统计信息失败。
SUCCESS	1	自动创建或者更新统计信息成功。
THROTTLED	2	由于当前有太多的优化正在进行中，自动创建或者更新统计信息失败。

有时您可能还会观察到AutoStats事件但并没有与之相关的统计信息创建或更新操作发生。该事件的产生是由于auto update statistics选项被关闭，或者由于查询引用的表上产生了大量的变更，由于查询结构和外健约束的存在从而导致优化器从查询计划中移除所有对该表的引用。

DROP STATISTICS用于删除统计信息，但是不可以删除作为创建索引的副产品而自动生成的统计信息。这种统计信息只有当删除索引时才会被删除。

在SQL Server 2005中维护统计信息

当您在表中进行了一系列的插入，删除和更新后，统计信息可能无法反映出特定列或索引的真实数据分布情况。如果SQL Server查询优化器需要表中某一列的统计信息，该表在上次创建或更新统计信息后经历了大量的更新活动，那么SQL Server就会通过抽样列值的方式自动更新统计信息。（使用auto update statistics）。统计信息的自动更新是由查询优化器触发的，或者通过执行一个编译好的执行计划而被触发，并且只更新那些在查询中被引用的列的子集。如果AUTO_UPDATE_STATISTCS_ASYNC为OFF，那么在编译查询之前更新统计信息，如果AUTO_UPDATE_STATISTCS_ASYNC为ON，则异步更新统计信息。

当一个查询首次被编译时，如果查询优化器需要一个特定的统计信息对象并且该对象存在，该统计信息对象将被更新如果统计信息对象已经过时。当执行一个查询且该查询的执行计划在缓存中，那么执行计划所依赖的统计信息将被检查已确认是否已经过时。如果是，执行计划将从缓存中移除，统计信息在重新编译查询时被更新。如果执行计划所依赖的统计信息改变了，那么执行计划也将从缓存中移除。

SQL Server 2005根据列更新计数器（colmodctrs）的变化决定是否更新统计信息。

以下情况可以认定一个统计信息对象已经过时：

· 如果在一张普通表上定义统计信息，那么该统计信息将过时，如果：

1. 表的大小从0行增长到>0行。

2. 当收集统计信息时，表中行数为500行或更少，此后统计信息对象中首列的colmodctr的变更超过了500。

3. 当收集统计信息时，表中含有500行以上的记录，此后统计信息对象中首列的colmodctr变更超过了500 + 收集统计信息时表中行数的20%。

· 如果在一张临时表上定义统计信息，那么在前面描述的情况下统计信息也将过时。

表变量根本没有统计信息。

可以在不同的级别上关闭前面描述的auto update statistics特性。

· 在数据库级别，使用下面的语句关闭自动更新统计信息：

ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF

· 在表级别，使用UPDATE STATISTICS命令或者CREATE STATISTICS命令的NORECOMPUTE选项关闭自动更新统计信息。