发表于:2008.03.30 10:26
分类: SQLSERVER
出处：http://zhouwf0726.itpub.net/post/9689/458560
---------------------------------------------------------------
-- 在firstname和lastname列上创建统计信息
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO
-- 显示表上现在有三个统计信息对象
sp_helpstats N'Person.Contact', 'ALL'
GO
结果：
统计信息名称 | 统计信息键 |
_WA_Sys_00000002_1B29035F | LastName |
FirstLast | FirstName, LastName |
Phone | Phone |
-- 显示LastName列的统计信息
DBCC SHOW_STATISTICS (N'Person.Contact', LastName)
GO
结果：
统计对象的标题信息：
名称 | 上次更新时间 | 行数 | 抽样行数 | 步数 | 密度 | 平均键长度 | 字符串索引 | ||
_WA_Sys_ | Mar 25 2005 | 5 | 5 | 4 | 0 | 13.6 | YES | ||
列集的前缀以及相关的密度和长度：
所有密度 | 平均长度 | 列 |
0.25 | 13.6 | LastName |
直方图步数：
RANGE_HI_ | RANGE_ | EQ_ROWS | DISTINCT_ | AVG_ |
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.
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)
结果（仅第二个结果集）
列集的前缀以及相关的密度和长度：
所有密度 | 平均长度 | 列 |
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
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有三个可能的值：
名称 | 值 | 定义 |