Hypothetical Indexes

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.

Read More

Last time Index was rebuilt (SQL Server DBA)

Manage Transaction Logs during Index Rebuild (SQL Server DBA)