Hypothetical Indexes

Hypothetical Indexes are created by Index tuning wizard when it checks queries & tables for columns to suggest on indexes. Usually when the Index Tuning Wizard exits, it deletes all the hypothetical indexes if it cleanly finishes but if it does not then these indexes will be left over.

When checking output of INDEXPROPERTY(table_ID, index, IsHypothetical) , you can see whether an index is hypothetical or not. Hypothetical indexes may hinder in generating an optimized plan for query execution. For a query or a stored procedure execution, after an initial recompile is triggered, the optimizer uses some of the information from these hypothetical indexes, which is out of date, and hence incorrectly determines that a recompile is needed again. During the ensuing recompiles, the information from the hypothetical indexes is never refreshed, and so the optimizer remains in a recompile loop.

Hence, dropping hypothetical indexes is a better choice.

Clustered hypothetical indexes can be dropped with DROP INDEX statement.
Nonclustered hypothetical indexes can be dropped with DROP STATISTICS statement