[转载]MS SQL 统计信息浅析上篇 - 潇湘隐者 - 博客园

[转载]MS SQL 统计信息浅析上篇 – 潇湘隐者 – 博客园.

统计信息概念

    统计信息是一些对象，这些对象包含在表或索引视图中一列或多列中的数据分布有关的统计信息。数据库查询优化器使用这些统计信息来估计查询结果中的基数或行 数。 通过这些基数估计，查询优化器可以生成高质量的执行计划。 例如，查询优化器可以使用基数估计选择索引查找运算符而不是耗费更多资源的索引扫描运算符，从而提高查询性能。[参考MSDN]

    其实如果你以前没有接触过统计信息，你可以将其看做是数据库为了得到最优的执行计划，统计数据库里面表、索引等对象的一些数据，例如表的记录数、所有列的 平均长度、直方图….等一些优化器需要用到的数据信息。SQL查询优化器是一个基于成本的优化器，类似于ORACLE里面的CBO，那么优化器如果要 得到成本最低的执行计划，就需要收集获取其生成执行计划的参考依据（统计信息数据）

如果你对这些概念性的东西比较模糊的话，那么为了让你形象的认识一下统计信息，请看下图：

统计信息参数

     数据库的统计信息相关参数有三个: 自动创建统计信息（Auto Create Statistics）、自动更新统计信息（Auto Update Statistics）、自动异步更新统计信息（Auto Update Statistics Asynchronously），它们都是数据库级别的。

自动创建统计信息（Auto Create Statistics）

该参数指定数据库是否自动创建缺少的优化统计信息。如果设置为True， 则将在优化过程中自动生成优化查询需要但缺少的所有统计信息。当开启自动创建统计信息（Auto Create Statistics）选项时，查询优化器会对在谓词中使用到的列，如果这些列的统计信息不可用或缺少时，则会单独对每列创建统计信息。这些统计信息对创 建一个查询计划非常必要。它们创建于那些现有统计对象中不存在直方图的列上，名字包括列名和对象ID的十六进制格 式：_WA_Sys_<column_name>_<XXXX>。这些统计信息用于查询优化器决定使用何种优化后的执行计划。

自动更新统计信息（Auto Update Statistics）

该参数指定数据库是否自动更新过期的优化统计信息。如果设置为True，则将在优化过程中自动生成优化查询需要但已过期的所有统计信息。否则不自动更新统计信息。

自动异步更新统计信息（Auto Update Statistics Asynchronously）

如果设置为 True，则启动过期统计信息的自动更新，查询在编译前不会等待统计信息被更新。后续查询将使用可用的已更新统计信息。如果设置为 False，则启动过期统计信息的自动更新的查询将等待，直到更新的统计信息可在查询优化计划中使用。将该选项设置为 True 不会产生任何影响，除非“自动更新统计信息”也设置为 True

关于这三个参数，一般建议开启自动创建统计信息、自动更新统计信息选项，关闭自动异步更新统计信息。

    1：对于自动创建统计信息选项，因为统计信息对查询优化器至关重要，没有统计信息，也就失去了它基于成本的优化器的意义，成为无土之木、无源之水。

    2：对于自动更新统计信息选项，有时候过期的统计信息会导致严重的性能问题，我都碰到过好几起因为过时统计信息导致SQL查询性能问题的案例，就像错误的 地图、错误的导航、错误的指路会让你偏离目的地那样，过时的统计信息往往导致查询优化器选择了次优的执行计划，产生糟糕的性能问题，所以这个参数必须开 启。

    3：对于自动异步更新统计信息选项，这个选项在OLTP环境下很有用，但在数据仓库中有负面影响。至于是否开启，我建议是关闭。默认也是关闭的。不知道这种观念是否正确。

下面是MSDN给予的使用同步更新统计信息\异步更新统计信息的参考意见：

————————————————————————————————————————

在以下情况下应考虑使用同步统计信息：

· 您执行会更改数据分布的操作，例如截断表或对很大百分比的行执行大容量更新。如果您在完成该操作后未更新统计信息，则使用同步统计信息将确保对更改的数据执行查询前统计信息是最新的。

在以下情况下，考虑使用异步统计信息来实现可预测性更高的查询响应时间：

· 您的应用程序频繁执行相同的查询、类似的查询或类似的缓存查询计划。与同步统计信息更新相比，使用异步统计信息更新时您的查询响应时间可能具有更高的可预 测性，因为查询优化器可以执行传入的查询而不必等待最新的统计信息。这避免延迟某些查询，而不延迟其他查询。有关查找类似查询的详细信息，请参阅使用查询和查询计划哈希值查找和优化类似查询。

· 您的应用程序遇到了客户端请求超时，这些超时是由于一个或多个查询正在等待更新后的统计信息所导致的。在某些情况下，等待同步统计信息可能会导致应用程序因过长超时而失败。

————————————————————————————————————————

那么先看一下如何通过SQL来查看这三个参数的设置值：

那么这三个参数的值保存在哪里呢？其实只要你稍微花一点心思去研究一下，就会发现 其实它是保存在系统表[sys].[sysdbreg]里面，[sys].[sysdbreg]是内部表，默认情况下不可查看，一般你可以通过系统视图 sys.database查看和研究其值的出处。

修改统计信息参数

方法1：

—-关闭数据库DBMonitor自动创建统计信息功能

USE [master]

GO

ALTER DATABASE [DBMonitor] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT

GO

–开启数据库DBMonitor自动创建统计信息功能

USE [master]

GO

ALTER DATABASE [DBMonitor] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT

GO

–关闭数据库DBMonitor自动更新统计信息功能

USE [master]

GO

ALTER DATABASE [DBMonitor] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT

GO

–启用数据库DBMonitor自动更新统计信息功能

USE [master]

GO

ALTER DATABASE [DBMonitor] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT

GO

–关闭数据库DBMonitor自动异步更新统计信息功能

ALTER DATABASE [DBMonitor] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT

GO

–启用数据库DBMonitor自动异步更新统计信息功能

ALTER DATABASE [DBMonitor] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

GO

方法2：使用SP_DBOPTION来启用或禁用。

SP_DBOPTION DBMonitor, ‘auto update statistics’, ‘ON’;

SP_DBOPTION DBMonitor, ‘auto update statistics’, ‘OFF;

方法3：图形化方法启用或禁用

对应的图像化操作：选择所要修改的数据库，单击右键选项”属性“，选择左侧的”选项“，则能看到这三个参数

在 AUTO_UPDATE_STATISTICS 为 ON 时，您可以覆盖数据库范围的统计信息更新行为，并且根据您的应用程序的要求为单独的表、索引或列将自动统计信息更新设为关闭。在 AUTO_UPDATE_STATISTICS 为 ON 时，您可以通过以下方式为表、索引或列禁用和重新启用自动统计信息更新：

· 使用 SP_AUTOSTATS 系统存储过程。这可以禁用或重新启用表或索引的统计信息更新。

· 对于 UPDATE STATISTICS 语句指定 NORECOMPUTE 选项。若要重新启用统计信息更新，请重新运行 UPDATE STATISTICS，但不使用

· NORECOMPUTE 选项。

· 对于 CREATE STATISTICS 语句指定 NORECOMPUTE 选项。若要重新启用统计信息更新，请使用 DROP STATISTICS 删除统计信息，然后运行 CREATE STATISTICS 但不使用 NORECOMPUTE 选项。

· 对 CREATE INDEX 语句指定 STATISTICS_NORECOMPUTE 选项。若要重新启用统计信息更新，您可以运行 ALTER INDEX 且 STATISTICS_NORECOMPUTE = OFF。

· 在 AUTO_UPDATE_STATISTICS 为 OFF 时，不能为单独的表、索引或列将自动更新设为打开。重新启用自动统计信息更新将还原AUTO_UPDATE_STATISTICS 选项指定的行为。如果 AUTO_UPDATE_STATISTICS 选项为OFF，统计信息更新将不会发生。

创建统计信息

如何创建统计信息呢，如果选项自动创建统计信息（Auto Create Statistics）开启了，那么数据库会自动创建某些统计信息，另外你也可以通过CREATE STATISTICS 语句手动创建某些统计信息。先看下面的例子：

USE [DBMonitor]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]’) AND type in (N’U’))

DROP TABLE [dbo].[Test]

GO

SELECT * INTO Test FROM sys.objects;

此时你会看到表Test根本没有统计信息，那么我们在表Test上创建一个索引IDX_TEST_OBJECT_ID

CREATE INDEX IDX_TEST_OBJECT_ID ON dbo.Test(object_id);

此时，你刷新一下表Test的统计信息，你会发现多了一个名为“IDX_TEST_OBJECT_ID”的统计信息,你新建多少条索引，在统计信息下就会创建与索引同名的统计信息。

SELECT DISTINCT TYPE FROM dbo.Test ;

执行上面面脚本后，我刷新统计信息，会发现多了一个名为“_WA_Sys_00000006_023D5A04”的统计信息，截图如下：

那么自动创建统计信息的规律或规则是啥呢？或者理解为：什么时候数据库创建统计信息，其实创建统计信息的规则如下：

    1：在索引创建时，SQL SERVER 会自动地在索引所在的列上创建统计信息

    2：当SQL SERVER想要使用某些列上的统计信息，发现没有时，SQL SERVER会自动创建统计信息（前提是要开启自动创建统计信息）。例如上面统计DISTINCT TYPE。

    3：手工使用CREATE STATISTICS之类的语句手工创建需要的统计信息。

关于CREATE STATISTICS的语法，大家可以参考MSDN，这里不做阐述了。需要注意的是统计信息可以通过全表扫描或随机抽样应读取的数据百分比或指定的数据行数，收集统计信息

更新统计信息

随着数据库的DML操作，数据的变更会导致统计信息过期，那么这时就需要更新统计信息。通常数据库通过两种方式更新统计信息：

    1：如果开启了自动更新统计信息（Auto Update Statistics）或自动异步更新统计信息选项，那么数据库会自动更新统计信息。

    2：手动更新统计信息

更新统计信息确保查询使用最新的统计信息编译。不过，更新统计信息会导致查询重新编译。我们建议不要太频繁地更新统计信息，因为需要在改进查询计划和重新编译查询所用时间之间权衡性能。此类特定的性能权衡取决于您的应用程序。

那么数据库什么时候、什么条件下才会更新统计信息呢？

1、 在一个空表中有数据的改动。

2、 当统计信息创建时，表的行数只有500或以下，且后来统计对象中的引导列的更改次数大于500.

3、 当表的统计信息收集时，超过了500行，且统计对象的引导列后来更改次数超过500+表总行数的20%时。

4、 在Tempdb中的表，少于6行且最少有6行被更改

注意：数据库不会为表变量收集统计信息，所以数据量比较大时尽量不要使用表变量。

一般建议不要太频繁地更新统计信息，因为需要在改进查询计划和重新编译查询所用时间之间权衡性能。 这种特定的性能权衡取决于您的应用程序。

手工更新统计信息，一般使用 UPDATE STATISTICS 或存储过程 sp_updatestats 来更新统计信息。

sp_autostats：显示或更改特定索引或统计信息的自动 UPDATE STATISTICS 设置，或者显示和更改当前数据库中指定表或索引视图的所有索引和统计信息的自动 UPDATE STATISTICS 设置

　　显示表的所有索引的当前状态

　　　　USE DBMonitor;

　　　　GO

　　　　EXEC sp_autostats ‘dbo.test’;

　　启用表的所有索引的自动统计信息

　　　　USE DBMonitor;

　　　　GO

　　　　EXEC sp_autostats ‘dbo.test’,’ON’

　　禁用特定索引的自动统计信息

　　　　USE DBMonitor;

　　　　GO

　　　　EXEC sp_autostats ‘dbo.test’,’OFF’, ‘IDX_TEST_OBJECT_ID’;

查看统计信息

如果要了解具体的统计信息内容，那么我们首先要知道如何查看具体的统计信息，统计信息保存在那些系统视图里面，如果能很好的回答这两个问题，那么我想你也就能知道统计信息的具体内容是那些了。关于第二个问题，后面章节部分再做探讨。

查看统计信息，我们先由浅入深，由简单到复杂。

sp_helpstats ：返回指定表中列和索引的统计信息。

USE DBMonitor;

GO

EXEC sp_helpstats ‘test’ ,’ALL’

下面我们看看sys.sp_helpstats的脚本

View Code

2：查看统计信息一般用DBCC SHOW_STATISTICS命令，如下所示

DBCC SHOW_STATISTICS('TEST', 'IDX_TEST_OBJECT_ID'); 

Name    Updated     Rows   Rows Sampled  Steps  Density     Average key length String Index Filter Expression           Unfiltered Rows
------------------------------------------------- ------------------
IDX_TEST_OBJECT_ID   09 24 2013  9:06PM   59                   59                   41     1             4                  NO           NULL   59

(1 行受影响)

All density   Average Length Columns
------------- -------------- ---------------------------------------
0.01694915    4              object_id

(1 行受影响)

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
          0             1             0                    1
          1             1             1                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          1             1             1                    1
          1             1             1                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          1             1             1                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          1             1             1                    1
          0             1             0                    1
          0             1             0                    1
          2             1             2                    1
          2             1             2                    1
          1             1             1                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1
          1             1             1                    1
          1             1             1                    1
          1             1             1                    1
          1             1             1                    1
          0             1             0                    1
          0             1             0                    1
          1             1             1                    1
          0             1             0                    1
          1             1             1                    1
          1             1             1                    1
          1             1             1                    1
          0             1             0                    1
          0             1             0                    1
          0             1             0                    1

(41 行受影响)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

虽然查看统计信息很容易，但是要读懂并能读取一些信息那们就不是那么简单的了。

下表对指定 DENSITY_VECTOR 时结果集中所返回的列进行了说明。

下表对指定 HISTOGRAM 选项时结果集中所返回的列进行了说明。

统计直方图用作在查询执行计划中查询优化器的选择依据

图形化查看

删除统计信息

DROP STATISTICS 删除当前数据库的指定表中的多个集合的统计信息。

    DROP STATISTICS Test._WA_Sys_00000006_023D5A04;

需要注意的是不能用DROP STATISTICS 删除有关索引的统计信息。统计信息的保留时间与索引存在的时间相同，当你删除索引时，对应的统计信息也自动删除。如下所示：

DROP STATISTICS Test.IDX_TEST_OBJECT_ID

消息 3739，级别 11，状态 1，第 1 行

无法对索引 ‘Test.IDX_TEST_OBJECT_ID’ 执行 DROP，因为该索引不是统计信息集合。

参考资料：

  http://technet.microsoft.com/zh-cn/library/ms190397(v=sql.105).aspx

http://technet.microsoft.com/zh-cn/library/ms187348.aspx

http://blog.csdn.net/dba_huangzj/article/details/8041267

http://blog.csdn.net/zhaowenzhong/article/details/6276878