sql语句大全之 统计信息

欢迎来到性能调优培训的第4个月。这个月全是关于SQL Server里的统计信息，还有它们如何帮助查询优化器生成足够好的执行计划。统计信息主要是被查询优化器用来估计查询返回的行数。它只是个估计，没别的。

统计信息概述

SQL Server使用在统计信息对象里称作直方图（Histogram）的东西，它描述了对于所给列最大200步长（Steps）的数据分布情况。最大的局限性之一，对于SQL Server里的统计信息是200步长的局限性（使用过滤统计信息可以超过这个步长，这在SQL Server 2008里就引入了）。

另外的局限性是统计信息的自动更新（Auto Update）机制：对于大于500行的表，如果500+20%的列值发生改变，统计信息才会更新。这就意味着，一旦表增长，你的统计信息的自动更新频率将越少（每次触发自动更新需要更多的记录修改）。

假设你有100000条记录的表，这个情况下，如果修改了20500（20%+500)的数据，统计信息才会自动更新。如果你有1000000条记录的表，你需要修改200500（20%+500)的数据，统计信息才会自动更新。这里用到的算法是指数的，不是线性的。在SQL Server里有2371的跟踪标志（trace flag）也会影响这个行为。

当你的执行计划里保航书签查找时，这个行为就会是巨大的问题。正如你知道的，基于当前的统计信息，如果查询的估计行数是非常少的，查询优化器才会选择书签查找运算符。如果你的统计信息过期，你的执行计划还是有效的话，SQL Server就会盲目重用缓存计划，你的页读取就会暴涨。我们来看看这个问题的具体例子。

失真的统计信息（Stale Statistics）

下面的脚本会创建有1500条记录的表，在column2列有平均的数据分布。另外我们在column2列上定义非聚集索引。

CREATE TABLE Table1
(
   Column1 INT IDENTITY,
   Column2 INT
)
GO

-- Insert 1500 records into Table1
SELECT TOP 1500 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1

INSERT INTO Table1 (Column2)
SELECT n FROM #nums

DROP TABLE #nums
GO 

CREATE NONCLUSTERED INDEX idx_Table1_Colum2 ON Table1(Column2)
GO

当你对表进行简单的SELECT * 查询时，你会得到带有书签查找运算符的执行计划：

SELECT * FROM dbo.Table1 WHERE Column2='9'

从索引查找（Non Clustered）运算符可以看到，SQL Server估计行数是1（估计行数（Estimated Number of Rows）属性），实际上SQL Server也处理1条记录（实际行数（Actual Number of Rows）属性）。这就是说，我们这里用到的统计信息是准确的，查询本身产生3个逻辑读。

我们现在的表有1500条记录，因此当20% + 500条记录发生改变时，SQL Server会自动更新非聚集索引的统计信息。算一下，我们需要修改800条数据（1500 * 20% + 500)。

接下来我们对表做如下处理：我们对SQL Server做一点动作，只插入799条新记录。但799条记录的第2列值都是2。这就是说我们完全改变第2列的平均数据分布。统计信息会认为只有1条第2列值为2的记录返回，但实际上却有800条记录返回（1条已存在的，799条新插入的）：

SELECT TOP 799 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1

INSERT INTO Table1 (Column2)
SELECT 2 FROM #nums

DROP TABLE #nums
GO

现在我们来执行下列查询语句，找第2列值为2的记录，并打开执行计划显示和IO统计。

SET STATISTICS IO ON
SELECT * FROM dbo.Table1 WHERE Column2 ='2'

 SQL Server重用了有书签查找的执行计划。这就是说执行计划里的书签查找执行了1500次——一次性对所有记录！这会耗费大量的逻辑读——SQL Server这里报告了806个页读取。

从图中可以看到，实际行数（Actual Number of Rows）现在已经远远超过了估计行数（Estimated Number of Rows）。

SQL Server里失真的统计信息就会带来这样的问题。

小结

今天的性能调优培训我给你简单介绍了SQL Server里的统计信息。如你所见，失真的统计信息，对于缓存的，重用的执行计划会带来严重的性能问题。

我希望现在你已经能很好的理解SQL Server里的统计信息，当它们过期是，会给你的执行计划带来副作用。下周我会进一步讨论统计信息，还有在SQL Server内部它们是怎样的。请继续关注。

围观PPT：

0817_13统计信息.rar