server sql 统计信息 过时_SQL Server 是如何统计信息的

SQL Server基于开销(Cost)评估执行计划，选择开销最小的作为“最优化”的执行计划，由于SQL Server根据索引及其统计信息来计算开销，所以，对查询优化来说，索引和统计数据是非常重要的，查询优化器(Query Optimizer)使用统计信息对查询的开销进行评估(Estimate)，选择开销小的查询计划，作为最终的、“最优的”的执行计划。SQL Server自动为索引列或查询的数据列创建统计信息，统计信息包括三部分：头部(Header)，密度向量(Density Vector) 和 分布直方图(Distribution Histogram)。

统计信息是数据分布的反馈，SQL Server根据数据更新的数量和特定的规则自动更新统计信息，一般情况下，表的数据量越大，SQL Server更新统计信息需要的数据更新量越大，随着数据的更新，有些表的数据不会及时更新，以至于统计信息过时，不能真实反映数据的分布情况，用户可以通过命令手动更新统计信息，但是更新统计信息需要扫描数据表，这可能是一个非常耗时的IO密集型操作，用户需要权衡性能的提升和资源的消耗。

一，查看统计信息

统计信息不是实时更新的，如果统计信息过期，查询优化器(Query optimizer)可能不能生成高质量的查询计划，必须有必要的调度程序，自动更新统计数据。数据库管理员(DBA)可以使用DBCC SHOW_STATISTICS 能够查看表或索引视图(Indexed view)的统计信息，以及最后一次更新统计信息的日期，如果统计信息过期，可以使用UPDATE STATISTICS命令手动更新统计信息，以使查询优化器依据正确的统计信息生成高效的查询计划。但是，并不是统计信息更新的越频繁越好，更新统计信息是IO密集型的操作，还会导致现有的查询计划的重新编译，建议不要太频繁地更新统计信息，在改进查询计划和查询计划的重新编译之间权衡开销，找到一个平衡点。DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

WITH STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

target 参数是：索引的名称，统计对象的名称，或者列名。如果target是索引名称，或统计对象的名称，那么该命令返回关于target的统计信息。如果target是数据列，那么该命令会自动在该列上创建统计，返回关于该列的统计信息。

1，统计对象

在SSMS中打开Table的属性，展开“Statistics”，这就是跟该表有关的统计对象：

查看统计对象 [cix_dt_test_idcode]的统计信息：dbcc show_statistics('dbo.dt_test',[cix_dt_test_idcode])

命令返回的统计信息包含三部分，分别是 头部信息，密度向量和分布直方图：

2，头部数据

第一个表是Header表，Name字段是统计对象的名称，

头部数据返回的字段说明：Updated字段：是统计信息最后一次更新的时间，通过该字段，可以判断统计信息是否过期。

Rows字段：是统计信息更新时，表或索引视图(Indexed View)中的数据行数量，注意，该字段不会实时反应数据表的总行数。

Rows Sampled字段：用于计算统计信息时的样本数据的总行数，如果 Rows Sampled < Rows，显示的直方图和密度结果是根据抽样数据进行估计的。

Steps字段：是分布直方图中的梯级数。每个梯级都跨越一个列值范围，直方图梯级是根据统计信息中的第一个键列定义的，最大梯级数为 200。

3，密度向量

第二个表是密度向量(Density Vector)，用于对键列(Key Column)执行密度分析，密度的计算公式非常简单：1和唯一值的比例，即 density= 1/(Distinct Value的个数)

密度向量的总行数跟索引键的数量有关，每一行都是索引键的前缀组合，而唯一值是前缀组合列的无重复值。例如，如果统计对象包含索引键列(A，B，C)，密度向量为3行，第一行是(A)的密度，唯一值是列A的无重复值；第二行是(A，B)的密度，唯一值是列A和B的无重复值；第三行是(A，B，C)的密度，唯一值是列A，B和C的无重复值。

示例中索引列是(id，code)，索引列的密度是计算(id)，(id，code)的密度，密度向量表中，All Density字段是统计对象计算的密度。

第一行的密度是0.001，由于列id的唯一值数量是1000，因此，1/1000=0.001--Distinct Count=1000select count( distinct id)from dbo.dt_test

试想，如果列ID的重复值比较多，(ID，Code)组合的重复值比较少，那么(ID)的All Density的值大于(ID，Code)的密度，通过Density Vector可以看出数据重复率的趋势。

密度向量始终是从索引列的第一列开始统计，如果筛选子句(where，on)中没有包含索引的第一列，那么查询优化器不会使用索引，因此，索引列的顺序非常重要。

4，分布直方图

第三个表是分布直方图(Distribution Histogram)，使用参数target的第一个索引键列(key column)来统计数据的分布，统计的数据是第一个索引列中非重复值的出现频率。如果统计的对象是复合索引，那么只统计索引列第一列的值的分布情况，忽略其他索引列。

本例的索引列是(ID，Code)，那么统计的是ID 值的分布直方图：

分布直方图返回的数据列说明：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。

例如，当前范围中有(1)，(2)，(3)，(1)，(2)五个数据行，最大值是(3)，且只有一个，因此，RANGE_HI_KEY=(3)，EQ_Rows=1，除去最大值，共有4行数据，唯一值是2个，因此Range_Rows=4，DISTINCT_RANGE_ROWS=2，由于唯一值的数量不是0，因此，AVG_RANGE_ROWS=4/2。

二，验证分布直方图数据

下图是统计对象 cix_dt_test_idcode 的分布直方图：

第一条记录是数据表的最小值，也是该范围的最大值，数据只有一条：

直方图第一行：RANGE_HI_KEY=0，　EQ_Rows=1 ，Range_Rows=0，DISTINCT_RANGE_ROWS=0，AVG_RANGE_ROWS=1

第二条记录，范围的最大值是7，范围的最小值是1，是大于第一条记录(0)的最小值；从1到7共有7条记录，除去最大值7之外，共有6行数据，所以，Range_Rows=6；这6行数据都不重复，因此DISTINCT_RANGE_ROWS=6；由于DISTINCT_RANGE_ROWS>0，因此 AVG_RANGE_ROWS=Range_Rows/DISTINCT_RANGE_ROWS=6/6=1。

直方图第二行：RANGE_HI_KEY=7，EQ_Rows=1，Range_Rows=6，DISTINCT_RANGE_ROWS=6，AVG_RANGE_ROWS=1

三，更新统计信息

SQL Server 查询优化器使用这些统计信息来计算开销，选择最优的执行计划。查询优化器选择索引的一个标准是：索引列的选择性高，也就是说，该列的重复值少，重复率可以从直方图的Avg_Range_Rows和密度向量的All Desity字段中获取。

1，查看统计信息最后一次更新的时间

系统根据特定的规则更新统计信息，但是，随着数据的少量更新，数据表的统计信息不会实时更新，STATS_DATE 函数用于返回表或索引视图上统计信息的最后一次更新的日期：STATS_DATE ( object_id , stats_id )

参数stats_id是统计对象的ID，可以通过sys.stats来查看统计对象及其ID，系统视图：sys.stats_columns显式统计对象和基础表(或索引视图)的数据列之间的关系：

select

object_name(s.object_id) object_name,

s.name as statistics_name,

sc.stats_column_id,

col_name(sc.object_id, sc.column_id) as column_name,

stats_date(s.object_id,s.stats_id) as stats_last_updated_date

from sys.stats as s

inner join sys.stats_columns as sc

on s.stats_id = sc.stats_id

and s.object_id = sc.object_id

where s.object_id=object_id('table_name','U')

order by s.name;

用户有时需要手动更新统计信息，这可以通过UPDATE STATISTICS命令来实现：update statistics dbo.dt_test [cix_dt_test_idcode]

在计算统计信息时，有多种扫描数据表的方式：FULLSCAN：扫描所有的数据行，开销最大，计算的统计信息最精确；

SAMPLE number { PERCENT | ROWS }：取样本，只扫描样本数据；

RESAMPLE：使用最新的样本数据计算统计信息，可能会导致全表扫描；

SQL Server查询优化器根据统计来评估开销，生成最优的执行计划。 选择适当的扫面方式，能够及时更新统计数据，使用最小的工作负载，实现性能的最大提升。UPDATE STATISTICS schema_name . table_name  { statistics_name | index_name }

WITH FULLSCAN | SAMPLE number PERCENT| RESAMPLE