你的位置:首页 > 数据库

[数据库]第13/24周 统计信息


欢迎来到性能调优培训的第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列上定义非聚集索引。

 1 CREATE TABLE Table1 2 ( 3  Column1 INT IDENTITY, 4  Column2 INT 5 ) 6 GO 7  8 -- Insert 1500 records into Table1 9 SELECT TOP 1500 IDENTITY(INT, 1, 1) AS n INTO #Nums10 FROM11 master.dbo.syscolumns sc112 13 INSERT INTO Table1 (Column2)14 SELECT n FROM #nums15 16 DROP TABLE #nums17 GO 18 19 CREATE NONCLUSTERED INDEX idx_Table1_Colum2 ON Table1(Column2)20 GO

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

1 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条新插入的):

1 SELECT TOP 799 IDENTITY(INT, 1, 1) AS n INTO #Nums2 FROM3 master.dbo.syscolumns sc14 5 INSERT INTO Table1 (Column2)6 SELECT 2 FROM #nums7 8 DROP TABLE #nums9 GO

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

1 SET STATISTICS IO ON2 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内部它们是怎样的。请继续关注。