有关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

查询统计

现在我们来粗略地看下直方图的工作方式，重要的是要了解如何查询表中的统计个数，幸运的是可以使用以下命令来查询：

   1: SELECT Schema_name(sys.objects.schema_id) + '.' + Object_Name(sys.stats.object_id) AS Table_Name,

   2:        sys.columns.name AS Column_Name,

   3:        sys.stats.Name AS Stats_Name

   4:   FROM sys.stats

   5:  INNER JOIN sys.stats_columns

   6:     ON stats.object_id = stats_columns.object_id

   7:    AND stats.stats_id = stats_columns.stats_id

   8:  INNER JOIN sys.columns

   9:    ON stats_columns.object_id = columns.object_id

  10:   AND stats_columns.column_id = columns.column_id

  11:  INNER JOIN sys.objects

  12:     ON stats.object_id = objects.object_id

  13:   LEFT OUTER JOIN sys.indexes

  14:     ON sys.stats.Name = sys.indexes.Name

  15:  WHERE sys.objects.type = 'U'

  16:    --AND sys.objects.name = 'Tab1'

  17:  ORDER BY Table_Name

  18: GO

查询结果：

创建统计

在对你的数据库运行上面的查询时看到的统计对象是自动创建的，当然，你也可以通过运行以下命令来手动创建：

   1: CREATE STATISTICS Stats_MyStatOnCol1 ON t1(i) WITH FULLSCAN

上面的这条命令将对表t1的i列上创建一个统计对象，同时使用WITH FULLSCAN参数来扫描整个表，而不是采样部分行，主要是为了得到更为精确的统计直方图。

不过，FULL SCAN的开销也是比较大的，但是要比采样扫描更为精确。

采样扫描

默认，SQL SERVER会根据现有的统计对象是否过期来进行创建或更新统计；当检测到与当前数据不匹配时，SQL SERVER会采样表中的数据进行重建统计，默认的采样频率是根据表的大小进行缓慢增加。

当使用采样创建统计时，SQL SERVER会从IAM链中随机选取一些页面，一旦某个页面选定后，页面中的这些数据就作为采样的数据源，这偶尔对导致一些不正确的数据统计，为此，你可能使用FULLSCAN来重建统计。

注意：有关IAM链的信息可以从这个链接http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-IAM-pages-IAM-chains-and-allocation-units.aspx获取详细的内容，另外，使用下面的信息来检测标记过期统计的方法，当统计的列发生更新时，SQL SERVER会根据实例中设置的“自动更新统计”来保持数据最新，其工作的方法如下：

• 当表的行数小于6条，并且该表存储在TEMPDB数据库中，每发生6次修改会触发自动更新
• 当表的行数大于6条，并且小于等于500，每发生500次修改会触发自动更新
• 当表的行数大于500条，表中的（500+20%）的数据发生变化会触发自动列新
• 对于表变量来说，并不会触发自动更新

 

全扫描

在有些表中，经常看到由于某些列发生变化，与这些列相关的统计也会自动更新，但是若发现创建的统计是基于采样的数据，那你可能需要手动运行

UPDATE STATISTICS WITH FULLSCAN来更新统计，如下命令：

   1: UPDATE STATISTICS Tab1 Stats_MyStatOnCol1 WITH FULLSCAN

 

说到这里，你可能会问：有必要关注数据库中统计所占用的空间吗？

答案取决于多种因素，通常不用过多关注统计所占用的空间，但是也不总是这样；如果你遇到一个含有非常长的列的表，或许你应该调查一下服务器是否因花费过多的时间和资源来更新这些统计的必要性。

在维护期间重建索引和更新统计，对于一个系统中数据量比较大的表来说，可能删除那些未使用的统计，这样有助于提高重建和统计更新的速度，不过并没有一种方法来检查统计对象是否使用，很难找出哪些统计对象未被使用。

 

测试

下面来通过一个示例来介绍，脚本创建一个示例表Tab1（26列），然后插入1万行：

   1: CREATE TABLE Tab1 (ID Int IDENTITY(1,1) PRIMARY KEY, 

   2:                    Col1 VarChar(200) DEFAULT NEWID(),

   3:                    Col2 VarChar(200) DEFAULT NEWID(),

   4:                    Col3 VarChar(200) DEFAULT NEWID(),

   5:                    Col4 VarChar(200) DEFAULT NEWID(),

   6:                    Col5 VarChar(200) DEFAULT NEWID(),

   7:                    Col6 VarChar(200) DEFAULT NEWID(),

   8:                    Col7 VarChar(200) DEFAULT NEWID(),

   9:                    Col8 VarChar(200) DEFAULT NEWID(),

  10:                    Col9 VarChar(200) DEFAULT NEWID(),

  11:                    Col10 VarChar(200) DEFAULT NEWID(),

  12:                    Col11 VarChar(200) DEFAULT NEWID(),

  13:                    Col12 VarChar(200) DEFAULT NEWID(),

  14:                    Col13 VarChar(200) DEFAULT NEWID(),

  15:                    Col14 VarChar(200) DEFAULT NEWID(),

  16:                    Col15 VarChar(200) DEFAULT NEWID(),

  17:                    Col16 VarChar(200) DEFAULT NEWID(),

  18:                    Col17 VarChar(200) DEFAULT NEWID(),

  19:                    Col18 VarChar(200) DEFAULT NEWID(),

  20:                    Col19 VarChar(200) DEFAULT NEWID(),

  21:                    Col20 VarChar(200) DEFAULT NEWID(),

  22:                    Col21 VarChar(200) DEFAULT NEWID(),

  23:                    Col22 VarChar(200) DEFAULT NEWID(),

  24:                    Col23 VarChar(200) DEFAULT NEWID(),

  25:                    Col24 VarChar(200) DEFAULT NEWID(),

  26:                    Col25 VarChar(200) DEFAULT NEWID())

  27: GO

  28: INSERT INTO Tab1 DEFAULT VALUES

  29: GO 10000

接着，我们来运行DBCC来执行索引重建，注意该表没有任何统计，你可以通过PROFILER来检查。

   1: DBCC DBREINDEX (Tab1)

   2: GO

现在我们假设你在表上为每一列创建一个统计，这意味着重建会触发统计的更新，通常你不必太在意，但是要清楚如何管理就行。

要为每一列创建统计对象，可以使用sp_createstats存储过程。

EXEC sp_createstats;
GO

执行上面的存储过程后，将为表Tab1的26个字段分别创建一个统计，然后再执行重建操作。

DBCC DBREINDEX (Tab1)
GO

从上面的输出可以看出，有大量的更新统计（SELECT StatsMan…）执行。

 

接下来我们来看一下统计对象占用多少的容量。

统计对象所占用的空间容量实际上很小，不会对系统性能造成影响，如果你持怀疑态度，想调查统计对象实际占用了多少容量，运行以下查询:

   1: IF OBJECT_ID('tempdb.dbo.#TMP') IS NOT NULL

   2:   DROP TABLE #TMP

   3: GO

   4: CREATE TABLE #TMP (ID Int Identity(1,1) PRIMARY KEY, 

   5:                    Table_Name      VarChar(200),

   6:                    Column_Name     VarChar(200),

   7:                    Stats_Name      VarChar(200),

   8:                    ColStats_Stream VarBinary(MAX),

   9:                    ColRows         BigInt,

  10:                    ColData_Pages   BigInt)

  11: GO

  12: DECLARE @Tab TABLE (ROWID       Int IDENTITY(1,1) PRIMARY KEY,

  13:                     Table_Name  VarChar(200),

  14:                     Column_Name VarChar(200),

  15:                     Stats_Name  VarChar(200))

  16:  

  17: DECLARE @i           Int = 0,

  18:         @Table_Name  VarChar(200) = '',

  19:         @Column_Name VarChar(200) = '',

  20:         @Stats_Name  VarChar(200) = ''

  21:  

  22: INSERT INTO @Tab (Table_Name, Column_Name, Stats_Name)

  23: SELECT Schema_name(sys.objects.schema_id) + '.' + Object_Name(sys.stats.object_id) AS Table_Name,

  24:        sys.columns.name AS Column_Name,

  25:        sys.stats.Name AS Stats_Name

  26:   FROM sys.stats

  27:  INNER JOIN sys.stats_columns

  28:     ON stats.object_id = stats_columns.object_id

  29:    AND stats.stats_id = stats_columns.stats_id

  30:  INNER JOIN sys.columns

  31:     ON stats_columns.object_id = columns.object_id

  32:    AND stats_columns.column_id = columns.column_id

  33:  INNER JOIN sys.objects

  34:     ON stats.object_id = objects.object_id

  35:   LEFT OUTER JOIN sys.indexes

  36:     ON sys.stats.Name = sys.indexes.Name

  37:  WHERE sys.objects.type = 'U'

  38:  ORDER BY Table_Name

  39:  

  40: SELECT TOP 1 @i = ROWID, 

  41:        @Table_Name = Table_Name,

  42:        @Column_Name = Column_Name,

  43:        @Stats_Name = Stats_Name

  44:   FROM @Tab

  45:  WHERE ROWID > @I

  46: WHILE @@RowCount > 0

  47: BEGIN

  48:   --PRINT 'UPDATE STATISTICS "' + @Table_Name + '" "'+@Stats_Name+'" WITH FULLSCAN'

  49:   --EXEC ('UPDATE STATISTICS "' + @Table_Name + '" "'+@Stats_Name+'" WITH FULLSCAN')

  50:   INSERT INTO #TMP(ColStats_Stream, ColRows, ColData_Pages)

  51:   EXEC ('DBCC SHOW_STATISTICS ("' + @Table_Name + '", "'+@Stats_Name+'") WITH STATS_STREAM')

  52:   ;WITH CTE_Temp AS (SELECT TOP (@@RowCount) * FROM #TMP ORDER BY ID DESC)

  53:   UPDATE CTE_Temp

  54:      SET Table_Name = @Table_Name, 

  55:          Column_Name = @Column_Name, 

  56:          Stats_Name = @Stats_Name

  57:   SELECT TOP 1 @i = ROWID, 

  58:          @Table_Name = Table_Name,

  59:          @Column_Name = Column_Name,

  60:          @Stats_Name = Stats_Name

  61:     FROM @Tab

  62:    WHERE ROWID > @I

  63: END

  64: GO

  65: SELECT SUM(DATALENGTH(ColStats_Stream) / 1024.) AS [Size KB]

  66:   FROM #TMP

  67: GO

  68: SELECT Table_Name,

  69:        Column_Name,

  70:        Stats_Name,

  71:        ColStats_Stream,

  72:        DATALENGTH(ColStats_Stream) / 1024. AS [Size KB]

  73:   FROM #TMP

  74:  ORDER BY [Size KB] DESC

 

 

从Size KB列可以知道每一个统计对象使用的字节数。