有关SQL Server分布统计的问题

最近常被一些人问到关于SQL Server中统计的问题，这些问题是：

• 是否需要关注数据库中统计的多少？
• 统计对象使用多个空间

就执行SQL语句来说，查询优化器使用索引或列的分布统计信息来选择最佳的策略或计划；如果缺少统计信息或统计信息过期，性能会受到影响。在回答这些问题前，需要介绍一些关于分布统计的背景知识：什么是分布统计、其重要性、如何创建、更新、显示或查询。

 

分布统计的定义

在介绍之前，不得不推荐的一些关于Holger Schmeling写的一系列关于统计的文章，强烈建议仔细阅读，我认为是最有价值的。

简单地说，统计（包含索引或列中的数据分布的一种映射或直方图）用于选取最好的查询执行计划；当系统正常时，你不必担心这些，这么说的原因是他们是自动创建的，随着数据的变化也保持自动更新，如果查询优化器‘编译’查询时，如果没有找到可用的统计来判断表或索引中的数据分布状态，数据库会在需要时自动创建一个，例如当你在WHERE子句中使用列过滤或在某列上进行DISTINCT操作时。

下面我们一起来看一下统计对象的直方图（Histogram），直方图主要衡量数据集中每一个非重复值的发生次数，查询优化器根据统计对象的第一个键列的值计算出一个直方图，通常一个直方图最多有200个值。

查看统计的详细信息，可以使用如下的命令：

   1: DBCC SHOW_STATISTICS (Tab1, Stats_MyStatOnCol1)

执行结果如下：

 

从上面的输出结果，注意到数据分成三个部分：Header、密度向量、Historgram。

在Header部分，由一系列有用的值（这些也可以结合STA_HEADER参数使用DBCC SHOW_STATISTICS来获取）下面介绍在Header中包含的信息：

名称	描述
Updated	记录更新统计的时间，也可以使用STATS_DATE函数获取
Rows	记录表或索引视图中总的行数
Rows Sampled	采样的行数，如果此条数小于总的记录数，显示的直方图和密度值则基于采样行数
Steps	直方图中的步值，每一个step表示一个范围值
Density	由1/非重复值（第一键列）算出
Average key Length	统计对象中键值的平均字节数
String Index	表示统计对象包含字符汇总统计以提高基数估计的性能，如WHERE ProductName like ‘%bike%'
Filter Expression	NULL表示非filtered统计，有关filtered谓词，参阅Filtered Index Design Guidelines,有关filtered统计，参阅Using Statistics to Improve Quer performance.
Unfiltered Rows	应用Filter表达式前的表的行数，如果Filter Expression为空，则该值与Rows保持一致

 

对于密度向量，这里不进行介绍，查询优化器一般不使用此部分信息。

下面介绍直方图

 

下面对输出结果进行解释：

RANGE_HI_KEY: 每一个直方图中的键值，以第4行RANGE_HI_KEY为什么111的为例，代表的意思是范围从108（107-第3行）到111.

RANGE_ROWS：大于上一个RANGE_HI_KEY键值（107）并且小于当前RANGE_HI_KEY键值（111）的行数，例如：从108到110之间（不包括111），满足的行数有160行。

EQ_ROWS：等于RANGE_HI_KEY值的行数，如等于111的行有64个。

DISTINCT_RANGE_ROWS: 范围间内非重复行数，例如：从108到110之间，共有3条，即108、109、110。

AVG_RANGE_ROWS: 范围平均行数（RANGE_ROWS/DISTINCT_RANGE_ROWS），例如：第4行为例，其AVG行数为53.33333（160/3）。

 

要理解直方图的使用，我们通过一个简单的表作为示例，在该示例表上创建一些统计来分析查询优化器是如何使用这些统计的；我们首先创建一个表，接着在该表上创建统计，脚本如下：

   1: use tempdb

   2: GO

   3: IF OBJECT_ID('Itens') IS NOT NULL

   4:   DROP TABLE Itens

   5: GO

   6: CREATE TABLE dbo.Itens(Quantidade int NULL)

   7: GO

   8: CREATE STATISTICS Stats_Quantidade ON Itens(Quantidade)

   9: GO

  10: UPDATE STATISTICS Itens WITH ROWCOUNT = 50000, PAGECOUNT = 180

  11: GO

  12: UPDATE STATISTICS Itens Stats_Quantidade WITH STATS_STREAM = 0x01000000010...

由于这里使用了STATS_STREAM来设置统计，可以点击下载脚本来创建。

下面来看下表的直方图：

   1: DBCC SHOW_STATISTICS (Itens, Stats_Quantidade) WITH HISTOGRAM

接下来通过一些查询来看下执行计划。

示例一：（EQ_ROWS)

 

   1: SELECT * FROM Itens

   2: WHERE Quantidade = 107

   3: OPTION (RECOMPILE)

从执行计划可以看出，SQL Server根据WHERE子句的条件值107估计的行数为60，该数值对应直方图中RANGE_HI_KEY为107的EQ_ROWS。

示例二：（RANGE_ROWS+EQ_ROWS）

   1: SELECT * FROM Itens

   2: WHERE Quantidade <=107

   3: OPTION (RECOMPILE)

从执行计划中看出，查询优化器根据直方图中的行数进行条件筛选，对Quantidate<=107的行进行汇总：

说明：总行数=0+56+171+59+88+60=434，此数字与执行计划中“估计行数”一致。

 

示例三：（AVG_RANGE_ROWS）

假如，我们对条件值进行变化，如下：

   1: SELECT * FROM Itens

   2: WHERE Quantidade =108

   3: OPTION (RECOMPILE)

注意执行计划中红色圈注的部分，由于这次条件筛选的值108不在直方图中，故查询优化器使用AVG_RANGE_ROWS列来估计返回的行数，在本例中，在108到110之间的平均值为53.3333。

 

示例四：（RANGE_ROWS+EQ_ROWS+AVG_RANGE_ROWS）

   1: SELECT * FROM Itens

   2: WHERE Quantidade <=112

   3: OPTION (RECOMPILE)

本例与上面的示例二类似，不同的是，由于直方图中并不包含112这个值，故SQL Server需要把AVG_RANGE_ROWS（50.66667）添加至总和中，即：

说明：估计的行数=0+56+171+59+88+60+160+64+50.66667=708.667,此值与执行计划中的“估计行数”一致。

 

示例五：（RANGE_ROWS+EQ_ROWS+SPECIAL_AVG_RANGE_ROWS)

   1: SELECT * FROM Itens

   2: WHERE Quantidade <=113

   3: OPTION (RECOMPILE)

本例的查询与示例四很类似，不同的是，这次过滤的条件值为113，这对查询优化器来说是很容易判断要估计的行数，只再需对50.66667添加两次,原因是112和113都不在直方图中），故得出以下格式：

0+56+171+59+88+60+160+64+50.66667+50.66667=759.33334。

不过，查询优化器估计的值为744.857，那问题是它如何得到此值呢？

答案是：查询优化器汇总了两次43.42857，那查询优化器又是如何得到43.42857？

回答这个问题，查询优化器没有使用AVG_RANGE_ROWS列的值，而使用新的计算公式，即：

special_avg_range_rows = range_rows / ( distinct_range_rows + 1 )，根据直方图中第5行来计算：

304/(6+1) = 43.42857.

由此得出估计的行数公式：

0+56+171+59+88+60+160+64+43.42857+43.42857=744.85714

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/355374/viewspace-719120/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/355374/viewspace-719120/