在上次的文章統計--過濾(篩選)索引的統計信息過期問題測試中，遺留一個問題，當更新行數滿足統計自動更新條件是，什么樣的查詢會觸發自動更新呢？

受林勇樺的指點，找到以下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. 在默認自動更新條件下，查詢不會觸發其不訪問到統計信息的自動更新。

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

慣例有妹子

科普下：此女叫鈴木愛理（すずき あいり），無種，勿求！