Question: I was reviewing a list of sql indexes from a database and spotted some indexes prefixed with “_dta_index”. What are they ? How did they get here? And how can I clean them up.
Answer: These are hypothetical indexes created by the Database Tuning Advisor (DTA).
To find the indexes use the following the query
Use MyDB GO select * from sys.indexes where is_hypothetical = 1
If DTA is allowed to complete gracefully , the DTA cleans up the indexes. If the DTA crashes such as shutting down unexpectedly then the hypothetical indexes remain in place – under these circumstances , the hypothetical indexes will remain in the metadata files.
There’s a number of good reasons to keep these hypothetical indexes out of the database. Hypothetical indexes add a management overhead . Maintaining strict controls around the usage of DTA – particularly on a Production system is important.
You may attempt to delete the hypothetical indexes and find you cannot . There are potentially a number of reasons , such as :
1) Filegroup is read only
2) Permission issues – for a DROP INDEX , ALTER permission is required.