Statistics-filtering index statistical information expiration Test 2

In the previous article statistics-filtering index statistics expiration test, there is a problem left over. When the number of updated rows meets the automatic Statistics update condition, what kind of queries will trigger automatic updates?

Under the guidance of Lin Yong Hua, find the following two tracking signs:

9204-print the loaded statistics (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-print the header of the statistical information obtained from the metadata (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 .)

With these two flags, we can know which statistics are used during execution.

Prepare test data

-- Create a table and insert 5000 rows of data select top (5000) IDENTITY (INT,) as id, * INTO TB001FROM SYS. all_columnsGO -- CREATE cluster index create clustered index IDX_IDON TB001 (ID) GO -- CREATE filter index create index IDX_COLUMNIDON TB001 (object_id) WHERE Column_id <3GO -- then import 25000 rows of data insert into TB001SELECT TOP (5000) * from sys. all_columnsGO 5 -- update statistics TB001GOINSERT INTO TB001SELECT TOP (5000) * from sys. all_columnsGO 4

Enable tracking flag and execute query

Dbcc freeproccache () godbcc traceon (3604,929 2, 9204) GO -- run the select count (1) FROM TB001WHERE Column_id query that uses a filter index but does not trigger an automatic update <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

We found that the above query is loaded with the statistics of IndexID: 3 (no index with IndexId = 3 exists), which is automatically generated during the query.

Start the second round of testing:

Dbcc freeproccache () godbcc traceon (3604,929 2, 9204) GO -- execute select top (1) object_id, COUNT (1) queries that use a filter index and can trigger automatic updates) FROM TB001WHERE Column_id <3 group 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: 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

An IndexID = 4 statistical information is automatically generated for the query. Because the column_id field is used in the query, the statistics of IndexID = 4 are also accessed, at the same time, the accessed and loaded information also has a filtering index. Because the statistics have expired, automatic Statistics update is triggered.

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

Conclusion:

1. In the first execution, when an index is used for a query, it does not necessarily mean that statistics are required for the query.

2. Under the default automatic update condition, the query triggers automatic updates that do not access statistics.

 

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

By convention

Popular Science: This female is called Suzuki Aili. No kind is available. Do not ask!

 

Statistics-filtering index statistical information expiration Test 2