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
Thanks Anurag,
Before reading this, i was wondered,from where this index came from. Now i have an clear cut idea,how and when it was occured.
Thanks for your useful information.
Regards,
Madhava Rao Bitra
Thanks for this artical helped me resolve a serious problem.
I had quite a few of these left over hypothetical indexes. They all had a type of NONCLUSTERED.
However, when I attempted to execute a DROP STATISTICS on them, I received the error "Cannot DROP the index '<index name>' because it is not a statistics collection." Executing a DROP INDEX worked.
This seems to contradict the last sentence of the original post. Is there something else that must be taken into account when deciding which type of DROP to use?
- Mark
I'm having the same problem as Mark. Has anyone found an answer to this?