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

 

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

内容列表
SQL Server 2005中的统计数据
统计特性概述
SQL Server 2005中的统计信息有哪些新特性？
定义
由SQL Server 2005收集的统计信息
创建和显示统计信息：示例
使用SQL Server 2005创建统计信息
在SQL Server 2005中维护统计信息
字符串摘要统计信息
在计算列上的统计信息       
在CLR用户定义类型的列上的统计信息       
统计信息和索引视图       
管理统计信息的最佳实践       
总结       
请参阅

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个间隔，以便获得最有效的统计信息。间隔的大小通常是不等的。下面的值或足以派生这些值的信息被存储在直方图的每一步中。


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收集统计信息，因为创建索引时无论如何都必须处理所有的行。

 

 

 

创建和显示统计信息：示例
下面的例子说明如何自动和手动的创建统计信息，以及如何列表和显示关于统计信息的相关资料。示例中给出了某些命令但不是所有的命令的运行结果；如果某个SQL Server 2005命令的输出结果对于阐述统计信息的行为很有用，那么该结果就显示出来。您可以自己运行这个示例来查看完整的输出。
USE tempdb
GO
-- 清理以前运行脚本生成的对象
IF object_id(N'Person.Contact','U') IS NOT NULL
        DROP TABLE Person.Contact
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')
        DROP SCHEMA Person
GO
-- 创建示例的schema和表
CREATE SCHEMA Person
GO
CREATE TABLE Person.Contact(
        FirstName nvarchar(60),
        LastName nvarchar(60),
        Phone nvarchar(15),
        Title nvarchar(15)
)
GO
-- 加工表中的行
INSERT INTO Person.Contact
   VALUES(N'James',N'Smith',N'425-555-1234',N'Mr')
INSERT INTO Person.Contact
   VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr')
INSERT INTO Person.Contact
   VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr')
INSERT INTO Person.Contact
   VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr')
INSERT INTO Person.Contact
   VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms')
GO
-- 显示在Person.Contact表上还不存在统计信息
sp_helpstats N'Person.Contact', 'ALL'
GO
-- 在LastName列上隐式地创建统计信息
SELECT * FROM Person.Contact WHERE LastName = N'Andersen'
GO
-- 显示在LastName列上自动创建的统计信息
sp_helpstats N'Person.Contact', 'ALL'
GO

结果：


-- 创建索引，这将同时创建统计信息
CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)
GO
-- 显示创建索引时会同时创建相关的统计信息
sp_helpstats N'Person.Contact', 'ALL'
GO

结果：


-- 在firstname和lastname列上创建统计信息
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO
-- 显示表上现在有三个统计信息对象
sp_helpstats N'Person.Contact', 'ALL'
GO

结果：


-- 显示LastName列的统计信息
DBCC SHOW_STATISTICS (N'Person.Contact', LastName)
GO

结果：
        统计对象的标题信息：



-- 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.
GO
-- 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)

结果（仅第二个结果集）
列集的前缀以及相关的密度和长度：


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

USE AdventureWorks
-- 清理以前运行脚本产生的对象
IF EXISTS (SELECT * FROM sys.stats
           WHERE object_id = object_id('Sales.SalesOrderHeader')
           AND name = 'TotalDue')
        DROP STATISTICS Sales.SalesOrderHeader.TotalDue
GO
CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)
GO
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有三个可能的值：


有时您可能还会观察到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选项关闭自动更新统计信息。
•        使用sp_autostats显示和修改表、索引、或者统计信息对象的自动更新设置。
同样地，使用ALTER DATABASE，UPDATE STATISTICS或者sp_autostats可以重新启用自动更新统计信息。
SQL Server 2005在数据库、表、索引或者统计信息对象级别维护自动更新统计信息的设置。当某人使用sp_autostats命令修改了某张表上所有对象的该项设置时，SQL Server通过单独修改表上每个统计信息对象和索引的设置来实现。不存在直接记录整张表自动更新统计信息设置为ON或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_name/user_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中包含许多关于统计信息管理能力的增强。最重要的是，在大多数情况下您可以借助自动创建和自动更新统计信息以确保良好的执行计划。 当使用默认抽样比率的自动统计功能无法满足需求时，您还可以显式地控制统计信息的抽样比率、创建和更新时间。如果您发现存在非最佳的执行计划是与统计信息或代价评估有关的，请考虑使用该白皮书中描述的最佳实践。

版权说明
该白皮书为初步文档，可能会在所述软件进行最后商业发布之前做完全修改。
该文档所含信息代表微软公司在文档出版时对所论及问题的当前看法。由于微软必须对千变万化的市场情况做出相应反应，因此本文档不应视为微软的任何承诺，且微软不保证所陈述任何信息在产品发布后的准确性。
本白皮书仅供信息参考。微软对本文档中的信息不做任何明示或默示保证。
遵守所有适用的版权法律是用户应尽的责任。下述陈述不限制任何版权，在未获得微软公司明示书面许可的情况下，不得以任何目的复制本文档任何部分或将任何部分保存或引入检索系统、亦不得以任何形式（电子、机械、影印、录制或其他方式）进行传播。
微软在文件所述主题中拥有专利权、专利应用程序、商标、版权或其他知识产权。除非在微软的任何书面许可协议中明示规定，否则对本文档的提供不得视为对任何专利权、商标、版权或其他知识产权许可的提供。
除非特别声明，本文中描述的示例公司、组织、产品、域名、e-mail地址、徽标、人物、地点以及事件均为虚构的，不应与任何实际的公司、组织、产品、域名、e-mail地址、徽标、人物、地点以及事件有任何的联系。
© 2005微软公司版权所有。
Microsoft和ActiveX是微软公司在美国和其他国家的注册商标或者商标。
本文中实际公司和产品的名称可能是其相应所有者的商标。