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.