依赖自动统计对性能的影响

 

依赖自动统计对性能的影响

from: http://www.cnblogs.com/tom-fu/archive/2008/09/06/1285816.html

 

    在使用索引对数据进行查询时，优化器考虑是执行索引扫描还是索引查找的依据是根据此索引相关的统计信息。但统计的步长不能超过200(DBCC SHOW_STATISTICS返回的第三部分结果)，这在数据量很大的表中，使得统计信息的精度变得越来越不准确。当然，这个影响不会很致命，发生的机会也很少。关键是统计信息得不到及时更新的话，就会使优化器选择错误的执行计划了。

    

    如果我们在事件探查器中发现很多的MISSING_COLUMN_STATISTICS事件时，可以把异步自动更新统计这个选项设置为True。以防止因为查询等待统计信息完成而超时，但这时的查询肯定会因为统计信息不准而选择错误的执行计划了。那么统计信息是在什么时候会被自动更新的呢？

    在兼容视图sys.sysindexes中的rowmodctr列中记录了自上次更新统计之后发生变化的记录数，每个统计信息对会对应一行。对就记录数小于500的来说，当变化超过500时，才会触发自动更新。对于更大的表，更新发生在500+20%*表的记录数被修改后（增删改都计算在内）。基于这个更新的比例，如果我们只依赖于自动更新统计时，就会造成优化器选择错误的执行计划了。

     下面我们通过一个例子来演示一下这种过于延迟的自动更新操作带来的查询性能的损失。Northwind数据库的Orders表的OrderDate建立一个非唯一非聚集索引。

-- 更新统计信息后观察相关的统计值及相应的视图信息 
UPDATE   STATISTICS  dbo.Orders  WITH  FULLSCAN
DBCC  SHOW_STATISTICS( ' dbo.Orders ' ,OrderDate)
-- rowmodctr这时为0，表示更新统计后没有记录被修改
SELECT  id, [ name ] ,dpages,rowcnt,rowmodctr  FROM  sys.sysindexes  WHERE  id = OBJECT_ID (N ' dbo.Orders ' )
-- 新创建一个订单并观察现在的统计信息，这时系统并没有更新统计
INSERT   INTO  dbo.Orders(OrderDate)  VALUES ( ' 2008-09-12 ' )
-- 这时我们看到统计信息中并没有'2008-09-12'的记录
DBCC  SHOW_STATISTICS( ' dbo.Orders ' ,OrderDate)
-- rowmodctr字段变为1
SELECT  id, [ name ] ,dpages,rowcnt,rowmodctr  FROM  sys.sysindexes  WHERE  id = OBJECT_ID (N ' dbo.Orders ' )
-- 现在只有一条符合条件的记录，使用索引查找高效。OPTION(RECOMPILE)不保存缓存计划，防止后面的查询重用此计划。
SELECT   *   FROM  dbo.Orders  WHERE  OrderDate = ' 2008-09-12 '   OPTION (RECOMPILE)
-- 不管新增或修改500个订单的定购日期，只运行任一操作以防止超过自动更新被触发时的阀值
-- UPDATE TOP(500) dbo.Orders SET OrderDate='2008-09-12' WHERE OrderDate<>'2008-09-12'
DECLARE   @i   int
SET   @i = 500
WHILE ( @i > 0 )
BEGIN
     INSERT   INTO  dbo.Orders(OrderDate)  VALUES ( ' 2008-09-12 ' )
     SET   @i = @i - 1
END
-- 此时统计信息中照样不包括'2008-09-12'，
DBCC  SHOW_STATISTICS( ' dbo.Orders ' ,OrderDate)
--  OrderDate的rowmodctr字段变为501，如果你是更新OrderDate的话，只有对应的OrderDate行变为501
SELECT  id, [ name ] ,dpages,rowcnt,rowmodctr  FROM  sys.sysindexes  WHERE  id = OBJECT_ID (N ' dbo.Orders ' )

EXEC  SP_SPACEUSED  [ dbo.Orders ] ,true
GO
-- 此时已有很多符合条件的记录，但因统计信息没有及时更新导致错误的选择继续使用索引查找，从而造成过高的逻辑I/O
SELECT   *   FROM  dbo.Orders  WHERE  OrderDate = ' 2008-09-12 '   OPTION (RECOMPILE)
-- 更新统计信息 
UPDATE   STATISTICS  dbo.Orders  WITH  FULLSCAN
-- 此时正确的选择表扫描
SELECT   *   FROM  dbo.Orders  WHERE  OrderDate = ' 2008-09-12 '
-- rowmodctr全被重置为0
SELECT  id, [ name ] ,rowcnt,rowmodctr  FROM  sys.sysindexes  WHERE  id = OBJECT_ID (N ' dbo.Orders ' )
-- 统计信息中包含了最新订单日期'2008-09-12'
DBCC  SHOW_STATISTICS( ' dbo.Orders ' ,OrderDate)

从上面的实验结果看，适度的对一些经常被修改的表手工进行统计更新可以防止优化器生成错误的执行计划。