Jaa

Identifying Hypothetical Indexes

  • Artikkeli

If you have ever interupted Database Tuning Advisor prior to a full completion of a workload evaluation, you may notice that your tables have indexes with naming conventions like "_dta_index_tablename_NNN".  You can see these hypothetical indexes in sys.indexes, however you will not see them in sys.dm_db_partition_stats because they do not consume disk space in the traditional sense.  Hypothetical indexes have column-level statistics used by DTA, but they cannot actually be used for data access by your queries.  If not being used by DTA, you'll want to clean these up.

The following is a query you can use to identify which indexes in the database are hypothetical.  It uses the INDEXPROPERTY function and the IsHypothetical property:

SELECT

object_name(object_id), name indexname

FROM sys.indexes WHERE INDEXPROPERTY(object_id, name, 'IsHypothetical') = 1

ORDER BY object_name(object_id), name

Cheers!

Comments