SQLServer统计信息理解 - 数据库编程

DetailID) as T --按不同组统计如下： group by ProductID --266行 group by ProductID, SalesOrderID --121317行 group by ProductID, SalesOrderID, SalesOrderDetailID --121317行 select 1.0/266 as [all density] union all select 1.0/121317 as [all density] union all select 1.0/121317 as [all density]

2 直方图

列名	说明
RANGE_HI_KEY	直方图梯级的上限列值。列值也称为键值。（按ProductID 的范围分布）
RANGE_ROWS	其列值位于直方图梯级内（不包括上限）的行的估算数目。（2个ProductID 值之间有多少行）
EQ_ROWS	其列值等于直方图梯级的上限的行的估算数目。（等于当前行ProductID值的有多少行）
DISTINCT_RANGE_ROWS	非重复列值位于直方图梯级内（不包括上限）的行的估算数目。（2个ProductID 值之间有多少不重复的键值ProductID）
AVG_RANGE_ROWS	重复列值位于直方图梯级内（不包括上限）的平均行数（如果 DISTINCT_RANGE_ROWS > 0，则为 RANGE_ROWS / DISTINCT_RANGE_ROWS）。

统计信息的重要性：

SQLServer中，在执行一个批处理语句时，关系引擎中的查询优化器会先估计生成较优的执行计划，执行执行器才安照此执行计划请求数据。即在生成执行计划期间，sqlserver是根据表中的统计信息进行行数估计，按照脚本语义来确定物理操作步骤生成执行计划，再按照该执行计划访问数据。而对于数据较大的表，按照统计信息估计的行数也常常不准确，这就是使查询使用了不准确的执行计划而比较慢。类似如：“参数嗅探”因传递参数值无法确定而估算错误；使用表变量不会有统计信息也不会估算行数。

--现在以这个表的列统计为例[Sales].[SalesOrderDetail](SpecialOfferID)
DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','_WA_Sys_0000000B_44CA3770')

如果查询日期范围在'2005-07-01'看上图，查询返回的估计行数应该为896.7728(190.2021+706.5707)

SELECT COUNT( ModifiedDate )FROM [Sales].[SalesOrderDetail] WHERE ModifiedDate >'2005-07-01 00:00:00'AND ModifiedDate<='2005-08-01 00:00:00'

估计行数为896.773，与统计信息的直方图的信息一致。其实就是根据直方图统计出来的，如果估计行数不准确，一定是统计信息没有正确的直方图信息，因此需要更新统计信息。有时候即使更新了统计信息，结果还是一样，因为数据量太大，估计数据不完全，看Rows Sampled可知道，因此也可以在更新统计信息时采用全表行数统计，但是这样扫描表数据也耗性能。即便如此，还是有些可能不一样，因为直方图的步长最多200，数据列中相同的和不同的差距太大，200段分布也有参差不齐的数据，不能使用更多步更详细的数据直方图。

?

统计信息的更新设置：

Sqlserver 默认自动维护统计信息，在数据库级别可以设置自动创建和更新统计信息的选项。

?

用脚本设置如下： ALTER DATABASE [databaseName] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT ALTER DATABASE [databaseName] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT ALTER DATABASE [databaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

?

AUTO_CREATE_STATISTICS：

AUTO_CREATE_STATISTICS = ON 时，当将某列作为条件查询时，系统自动为每个条件列创建单列的统计信息。创建索引时也会自动创建相应的统计信息. 查询优化器通过使用 AUTO_CREATE_STATISTICS 选项创建统计信息时，统计信息名称以_WA 开头。

?

AUTO_UPDATE_STATISTICS：

AUTO_UPDATE_STATISTICS = ON 时，查询优化器将确定统计信息何时可能过期，然后在查询使用这些统计信息时更新它们。统计信息将在插入、更新、删除或合并操作更改表或索引视图中的数据分布后过期。查询优化器通过计算自最后统计信息更新后数据修改的次数并且将这一修改次数与某一阈值进行比较，确定统计信息何时可能过期。该阈值基于表中或索引视图中的行数。查询优化器在编译查询和执行缓存查询计划前，检查是否存在过期的统计信息。在编译某一查询前，查询优化器使用查询谓词中的列、表和索引视图确定哪些统计信息可能过期。在执行缓存查询计划前，数据库引擎确认该查询计划引用最新的统计信息。

?

AUTO_UPDATE_STATISTICS_ASYNC：

异步统计信息更新选项AUTO_UPDATE_STATISTICS_ASYNC 将确定查询优化器是使用同步统计信息更新还是异步统计信息更新。默认情况下，异步统计信息更新选项被关闭，并且查询优化器以同步方式更新统计信息。 AUTO_UPDATE_STATISTICS_ASYNC 选项适用于为索引创建的统计信息对象、查询谓词中的单列以及使用 CREATE STATISTICS 语句创建的统计信息。统计信息更新可以是同步（默认设置）或异步的。对于同步统计信息更新，查询将始终用最新的统计信息编译和执行；在统计信息过期时，查询优化器将在编译和执行查询前等待更新的统计信息。对于异步统计信息更新，查询将用现有的统计信息编译，即使现有统计信息已过期。如果在查询编译时统计信息过期，查询优化器可以选择非最优查询计划。在异步更新完成后编译的查询将从使用更新的统计信息中受益。

?

统计信息自动维护更新：

Sqlserver之所以自动维护统计信息，首先设置AUTO_UPDATE_STATISTICS=ON，sqlserver会在符合某条件时自动更新表中的统计信息。其中我们可以看到的，系统表sysindexes的列rowmodctr，它记录自上次更新统计信息后插入、删除、更新行的累计总次数。对于满足统计信息更新的条件，系统会自动更新。

?

SELECT name,rows,rowmodctrFROM sys.sysindexes

?

自动更新统计规则：

?表中行范围rows=0 行增长 rows>0行；

?表中行范围 0500 ；

?表中行范围rows>500 行，只要变化的次数 rowmodctr>500+20%rows；

?临时表行数rows<6，只要变化的次数rowmodctr>6；

?

需要手动更新统计信息：

查询执行时间很长。

在升

SQLServer统计信息理解(二)