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.

Default Object Name Prefixes

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

Distinguishing between Hypothetical and Real Application-Generated Objects

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.