统计--过滤(筛选)索引的统计信息过期问题测试2

在上次的文章统计--过滤(筛选)索引的统计信息过期问题测试中，遗留一个问题，当更新行数满足统计自动更新条件是，什么样的查询会触发自动更新呢？

受林勇桦的指点，找到以下2个跟踪标志：

9204 – 打印出被加载的统计信息（With this enabled, we get a report of statistics objects which are considered ‘interesting’ by the query optimizer when compiling, or recompiling the query in question. For potentially useful statistics, just the header is loaded.）

9292 – 打印出从元数据中得到的统计信息的头信息（With this enabled, we see the ‘interesting’ statistics which end up being fully loaded and used to PRoduce cardinality and distribution estimates for some plan alternative or other. Again, this only happens when a plan is compiled or recompiled – not when a plan is retrieved from cache.）

使用这两个标志，我们可以执行时知道使用了那些统计信息

准备测试数据

--创建表,并插入5000行数据SELECT TOP(5000) IDENTITY(INT,1,1) AS ID,* INTO TB001FROM SYS.all_columnsGO--创建聚簇索引CREATE CLUSTERED INDEX IDX_IDON TB001(ID)GO--创建过滤索引CREATE INDEX IDX_COLUMNIDON TB001(object_id)WHERE Column_id<3GO--再导入25000行数据INSERT INTO TB001SELECT TOP(5000) * FROM SYS.all_columnsGO 5--更新统计信息UPDATE STATISTICS TB001GOINSERT INTO TB001SELECT TOP(5000) * FROM SYS.all_columnsGO 4

开启跟踪标志并执行查询

DBCC FREEPROCCACHE()GODBCC TRACEON(3604, 9292, 9204)GO--执行使用过滤索引但不能触发自动更新的查询SELECT COUNT(1) FROM TB001WHERE Column_id<3

Stats header loaded: DbName: TestDB01, ObjName: TB001, IndexId: 3, ColumnName: column_id, EmptyTable: FALSEStats loaded: DbName: TestDB01, ObjName: TB001, IndexId: 3, ColumnName: column_id, EmptyTable: FALSE

我们发现以上查询加载了IndexID:3的统计信息（并不存在IndexId=3的索引），该统计信息在查询时被自动生成

开始第二轮测试：

DBCC FREEPROCCACHE()GODBCC TRACEON(3604, 9292, 9204)GO--执行使用过滤索引且能触发自动更新的查询SELECT TOP(1) object_id,COUNT(1)FROM TB001WHERE Column_id<3GROUP BY object_id ORDER BY COUNT(1) DESC

Stats header loaded: DbName: TestDB01, ObjName: TB001, IndexId: 3, ColumnName: column_id, EmptyTable: FALSEStats loaded: DbName: TestDB01, ObjName: TB001, IndexId: 3, ColumnName: column_id, EmptyTable: FALSEStats header loaded: DbName: TestDB01, ObjName: TB001, IndexId: 4, ColumnName: object_id, EmptyTable: FALSEStats loaded: DbName: TestDB01, ObjName: TB001, IndexId: 4, ColumnName: object_id, EmptyTable: FALSEFiltered stats header loaded: DbName: TestDB01, ObjName: TB001, IndexId: 2, ColumnName: object_id, Expr: ([Column_id]<(3)),  EmptyTable: FALSEFiltered stats loaded: DbName: TestDB01, ObjName: TB001, IndexId: 2, ColumnName: object_id, Expr: ([Column_id]<(3)),  EmptyTable: FALSE

查询同样自动生成了一个IndexID=4的统计信息，由于查询中使用到column_id字段，因此IndexID=4的统计信息也被访问到，同时被访问加载到的信息还有过滤索引，由于该统计已过期，因此触发了统计信息自动更新。

--===================================================

结论：

1. 在第一次执行的情况下，查询使用到索引不一定代表查询需要使用到统计信息。

2. 在默认自动更新条件下，查询不会触发其不访问到统计信息的自动更新。

--=================================================

惯例有妹子

科普下：此女叫铃木爱理（すずき あいり），无种，勿求！