SQL Server统计分析——《SQL Server2008查询性能优化》笔记

索引对于一个查询请求的重要性不言而喻。但是优化器是如何选择有效的索引，这就需要对表内的数据分布情况进行统计，根据此统计信息让优化器做出正确的决策。在SQL Server中，这一信息是通过索引键上统计的形式维护的。通过统计信息，优化器可以估计出返回结果集或者中间结果集所花费的时间，从而确定出高效的操作。只要数据库的统计设置处于自动更新统计状态，优化器就能动态的确定出高效的处理策略。

1、  索引列上的统计

更新统计信息需要消耗CPU周期，当出现以下情况时，SQL Server将更新统计信息

1）、当没有任何数据的表添加一行数据时

2）、当少于500行的表增加500行或者更多行时

3）、当多于500行的表增加500+表中原始行数*20%的行数时

下面创建实例脚本：

if(select OBJECT_ID('t1')) is not null
drop table dbo.t1
go
create table dbo.t1(c1 int,c2 int identity);
select top 1500 identity(int ,1,1) as n
into #nums
from master.dbo.syscolumns sc1,
     master.dbo.syscolumns sc2;
 insert into dbo.t1(c1)
 select n from  #nums;
 drop table #nums;
 create nonclustered index i1 on dbo.t1(c1)

(表t1建在AdventureWorks库内)

执行脚本后，索引i1上的统计信息如下：

                     （表名-->统计信息-->i1右键属性-->详细信息）

如果在索引列上执行一个非常有选择性的过滤条件来检索一行数据，那么优化器将选择非聚簇索引进行查找，

select * from t1 where c1=2

执行计划：

为了理解小数据量的修改在统计更新上的影响我们可以添加一行数据进行测试，

insert into t1(c1) values(2)

而后查看统计信息如下：

可以看到和最初的统计信息相同。因为表t1的数据量是1500行，按照更新统计信息的要求需要增加500+1500*20%大约800行（现在的数据量是1501行）的数据才满足条件。运行如下脚本

select top 800 identity(int ,1,1) as n
into #nums
from master.dbo.syscolumns sc1,
     master.dbo.syscolumns sc2;
 insert into dbo.t1(c1)
 select 2 from  #nums;
 drop table #nums
select * from t1 where c1=2

执行select语句的执行计划：

因为索引C1上已经有802行的C1=2的数据（非聚簇索引查询小量数据效率较高，大数据量用聚簇索引或者表效率较高），所以优化器根据统计信息，采取表扫描策略。

此时的统计信息：

如果关闭自动更新统计，那么优化器将根据过时的统计信息，做出相应的处理策略。为了理解这种过时统计的危害，按以下步骤测试：

（1）重建前述测试表以及非聚簇索引

（2） 关闭自动更新统计

alter database AdventureWorks set auto_update_statistics off

（3） 添加800行数据

（4）执行

select * from t1 where c1=2

此时的执行计划：

关闭自动更新统计后，优化器采取索引查找而非表扫描。

测试结束，重新开启自动更新统计，

alter database AdventureWorks set auto_update_statistics on

2、  分析统计信息

统计是一组存储为柱状图（hsitogram）的信息，柱状图描述数据在不同分类中的频度信息。SQL Server存储的柱状图包括多达200行的列或索引键（多列索引键的第一列）的数据分布采样。在两个连续采样值之间的索引键值范围上的信息被称为step。一个step提供以下信息：

1）  给定step的最高值RANGE_HI_KEY

2）  等于RANGE_HI_KEY的行数EQ_ROWS

3）  前一个最高值和当前最高值之间的范围RANGE_ROWS

4）  范围中不同行的数量DISTINCT_RANGE_ROWS，如果范围中所有值都是唯一的，那么RANGE_ROWS等于DISTINCT_RANGE_ROWS

5）  范围内一个键值的平均行数AVG_RANGE_ROWS

6）  统计密度DENSITY=1/列中不同值的数量（如select 1.0/count(distinct c1) from t1）

一个索引键值（RANGE_HI_KEY）的EQ_ROWS值帮助优化器决定在一个查询请求中如何以及是否使用该索引。为了理解优化器根据匹配行数据量决定数据检索策略，运行以下测试脚本

if(select OBJECT_ID('t1')) is not null
drop table dbo.t1
go
create table dbo.t1(c1 int,c2 int identity);
insert into dbo.t1(c1) values(1);
select top 10000 identity(int ,1,1) as n
into #nums
from master.dbo.syscolumns sc1,
     master.dbo.syscolumns sc2;
 insert into dbo.t1(c1)
 select 2 from  #nums;
 drop table #nums;
 create nonclustered index i1 on dbo.t1(c1)

用如下命令查看统计信息:

dbcc show_statistics(t1,i1)

执行结果：

现在执行

select * from dbo.t1 where c1=1 --表中只有1行，根据统计信息，优化器决定用索引查找

执行计划图：

执行另一语句：

select * from dbo.t1 where c1=2 --表中有10000行，根据统计信息，优化器决定用扫描，没有聚簇索引，所以采取表扫描

执行计划图：