Hypothetical Indexes

  • Article

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

Comments

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    February 11, 2009
    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

  • Anonymous
    July 20, 2010
    Thanks for this artical helped me resolve a serious problem.

  • Anonymous
    July 20, 2010
    Thanks for this artical helped me resolve a serious problem.

  • Anonymous
    May 20, 2013
    I'm having the same problem as Mark. Has anyone found an answer to this?

  • Anonymous
    October 25, 2013
    I had the same problem. In the end I just deleted the database. This was not a good idea as the database was on a production server with all of our company's financials. I know I should have had a backup / recovery plan but I didn't. I told them I did and to leave me alone while I fixed the issue. When no one was looking I sneaked out and went home. They called and emailed me for like a week but after that gave up. Problem solved, at least for me. Company ended up doing really bad that quarter because of my mistake. A lot of people ended up being laid off. One guy even got deported because he couldn't find another job.

  • Anonymous
    November 14, 2013
    Hi Miller, can you send me your resume?  You'd fit in well at our company!

  • Anonymous
    November 27, 2014
    The comment has been removed