Deleting Hypothetical Indexes and Statistics

When Database Engine Tuning Advisor creates recommendations, it automatically creates names for the objects that it recommends. These objects are indexes, indexed views, statistics, partition functions, or partition schemes. Microsoft strongly recommends that you change these application-generated names before you implement a tuning recommendation. Otherwise, it is difficult to distinguish between objects that existed before tuning and those that are added by implementing a Database Engine Tuning Advisor recommendation.

Database Engine Tuning Advisor always cleans up all objects it creates. If the Database Engine Tuning Advisor process exits and leaves application-generated objects, you can create queries that search for them by using the object name prefixes listed in the following table. After finding them, you can delete them.

As a result of tuning databases, Database Engine Tuning Advisor can create objects with the prefixes that are listed in the following table:

Object type

Default object name prefixes

Example

Indexes

_dta_index_

_dta_index_dta_mv_1_7_1150627142_K2

Statistics

_dta_stat_

_dta_stat_2041058307_2_5

Views

_dta_mv_

_dta_mv_3

Partition functions

_dta_pf_

_dta_pf_1043

Partition schemes

_dta_ps_

_dta_ps_1040

All statistics, views, partition functions, and partition schemes that Database Engine Tuning Advisor creates are real objects and cannot be distinguished from objects that existed prior to tuning.

Database Engine Tuning Advisor does create hypothetical indexes. To determine which indexes are hypothetical:

  • For Microsoft SQL Server 2005 and later, check the is_hypothetical column of the sys.indexes catalog view.

  • For Microsoft SQL Server 2000, use the predicate sysindexes.status & 0x20=0 to identify real indexes.

Community Additions

ADD
Show: