SQL Server Statistics

参考阅读了网上各类文章整理。请勿转载。

作用：

Statistics，由柱状图，密度，字符统计（histogram, density, string statistics)?组成。可以被SQL Server的 Query optimizer用来完成基数估算（cardinality estimation)选出一个可靠的执行计划。

让Query optimizer可以知道对于当前Query，是做scan, seek 还是利用Index。 是使用nested loop join 或者hash join等等。

一次基数估算，算的是各种操作符，比如筛选，Join 条件，Group By 等返回的记录数，也可以称之为命中率（selectivity).

Density 是组成的顺序列组合，histogram, string statistics 只能 statistics 的第一个字段，且string statistics 必须是 string 才能创建。

 

Header返回的字段说明：

• Updated字段：是统计信息最后一次更新的时间，通过该字段，可以判断统计信息是否过期。
• Rows字段：是统计信息更新时，表或索引视图（Indexed View）中的数据行数量，注意，该字段不会实时反应数据表的总行数。
• Rows Sampled字段：用于计算统计信息时的样本数据的总行数，如果 Rows Sampled < Rows，显示的直方图和密度结果是根据抽样数据进行估计的。
• Steps字段：是分布直方图中的梯级数。每个梯级都跨越一个列值范围，直方图梯级是根据统计信息中的第一个键列定义的，最大梯级数为 200。

histogram返回的数据列说明：

• RANGE_HI_KEY：直方图梯级的上限列值。列值也称为键值。
• RANGE_ROWS：其列值位于直方图梯级内（不包括上限）的行的估算数目。
• EQ_ROWS：其列值等于直方图梯级的上限的行的估算数目。
• DISTINCT_RANGE_ROWS：非重复列值位于直方图梯级内（不包括上限）的行的估算数目。
• AVG_RANGE_ROWS：重复列值位于直方图梯级内（不包括上限）的平均行数（如果 DISTINCT_RANGE_ROWS > 0，则为 RANGE_ROWS / DISTINCT_RANGE_ROWS）。

在分布直方图中，每一行都是一个范围（Range），

• 字段RANGE_HI_KEY是范围的最大值，范围的最小值大于上一条记录的最大值（RANGE_HI_KEY）。在直方图中，第一条记录是数据表的最小值，只有一条记录。
• 字段Range_Rows表示在当前范围中，不包括最大值（RANGE_HI_KEY）的总行数。
• EQ_Rows字段是当前范围中，等于最大值（RANGE_HI_KEY）的总行数。
• DISTINCT_RANGE_ROWS字段表示在当前范围中，除去RANGE_HI_KEY之外的所有数据行，其唯一值的数量。
• AVG_RANGE_ROWS字段是一个比例，当DISTINCT_RANGE_ROWS=0时，AVG_RANGE_ROWS=1；当DISTINCT_RANGE_ROWS>0时，AVG_RANGE_ROWS=Range_Rows/DISTINCT_RANGE_ROWS。

 

Statistics 是如何被创建的：

1.当一个index被创建的时候，对应的statistics会被自动创建，也随着Index的drop消失。需要特别注意的是：rebuild index 只会更新statistics 不会删除它再重建。

需要注意的是：重建 index 更新 statistics 会产生对表的?FULLSCAN。会造成很多资源的消耗。

2.?Auto Create Statistics 选项被启用的时候(默认为True)，如果你的WHERE子句或者JOIN ON条件中，出现了index字段中非第一位的列（比如: CREATE INDEX xxx ON tablename(a,b,c) 中的b,c列），那么SQL Server将会为b,c 创建基于单列的statistics。

3. 基于列的statistics称为column statistics。使用CREATE STATISTICS?命令可以创建基于单列或者多列的?statistics

 

Statistics 需要人为的去管理吗？

SQL Server会自己管理Statistics。在数据CRUD发生的时候，Statistics 都会被更新。但是有的时候我们任然需要人为的去干涉它。比如一下情况：

1.如果Statistics 被更新的太频繁。

2.Statistics 没有提供足够可用信息给optimizer

 

SQL Server是如何管理Statistics 的？

假设，Auto Create Statistics?选项是开启的，仅到volume threshold达到一定阈值后，SQL Server 才会去更新statistics而不是每一条（次）CRUD。

在 SQL Server 的内部，为每张表的每个字段定义了一个计数器（counter), 叫做?colmodctr。这个计数器用来记录字段更新的次数。

对应Index的首列（前面的a,b,c列），有个简单的公式，可以来判断一下statistics自动更新的阈值：

1). 表的行数从0变到1。

2). 表的行数小于500，Index的首列的colmodctr?每增加500行。

3). 表的行数大于500，Index的首列的colmodctr > 500 + 20%*n

这3种情况statistics 就被判断为过期。n 在这里是指整张表的数据量。colmodctr 通过 DAC(dedicated administrator connection) 连接服务器，查看 sys.sysrscols 就可见了。

 

那么，什么时候去认为的干预statistics的更新呢？

1.当你有巨大数据量的表、数据分配不平衡的分区表、分区表的分区发生大的变化或者不断持续增长的主键。你往往需要人为的去更新statistics。

比如：你有一个超数据量的表，并且每天都会有一定数据量的改变，但是有达不到20%（上面提到的20%+500阈值）。那么当你查询这个表的时候，statistics往往都不是最新的,这样很可能你的查询就会变慢。

于是，为了解决这个问题，可能的方法是设定一个scheduled job,定时使用UPDATE STATISTICS更新statistics。

2.AUTO_UPDATE_STATISTICS 设置为 ON 的时候，原本全量统计的 statistics 可能会被优化器用抽样的方式自动置回去，导致 statistics 并不准确。因此是否自动更新 statistics 也是个值得商榷的事情。

 

 

更新 statistics 的命令：

UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN, COLUMNS? —-更新所有字段的 statistics

UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN, INDEX? —-?更新表所有index 的statistics

UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN, ALL? —- 以上全部

 

查看、评估当前的statistics:

1.SQL Server 2005 以上版本, 使用?sys.stats, 和?sys.stats_columns?, 老版本使用sp_helpstats?，来查看库中有哪些statistics。

2. 使用DBCC SHOW_STATISTICS来查看某一个确定statistics的内容。需要注意的是IX_SalesOrderDetail_ProductID是statistics的名字，不是列的名字，使用列名是查不出来结果的。官方文档中也有具体描述。

USE [AdventureWorks2012];
DBCC SHOW_STATISTICS (“Sales.SalesOrderDetail”, IX_SalesOrderDetail_ProductID);

DBCC SHOW_STATISTICS 返回三个结果集，分别是：header, density vector 和 histogram. 如果只需要看其中之一，可以在命令后面分别加 WITH stat_header, WITH density vector , WITH histogram.

根据Rows?和?Rows_Sampled，就可以看出是不是使用100%的数据来生成的。

density, 代表对于特定的列，如果一个唯一值大概有可能会有多少行数据被返回。Density 越小命中率越高。

使用 density 来做预估，有好的地方，也有不好的地方。

如果你是动态SQL，需要根据某个字段的值来查询结果，那么histogram 是无法被使用的，只能通过density来估算返回的行，这个时候密度不均匀的值段就会出现估计数量偏差很大的问题。详情ref.?【万字详解】SQL 优化引擎内幕 by dbLenis

 

根据你的情况，制定一个更新statistics的策略:

1.首先，你要监控你的数据容量大小：

• sys.partitions?– provide the number of rows in the table
• sys.dm_db_partition_stats?– provides row counts plus page counts, per partition
• sys.dm_db_index_physical_stats?– provides the number of rows and pages, plus information about fragmentation, forwarded rows and more

2.其实，根据数据变化的速率调整手动更新statistics的频率：

通过两个DMV，sys.dm_db_index_operational_stats?和?sys.dm_db_stats_properties? 我们可以很好的监控库中数据变化速率。具体的解释，我会在后续的DMV文章中解释。