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

Comments (8)

  1. Anonymous says:

    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

  2. Madhava Rao Bitra says:

    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.


    Madhava Rao Bitra

  3. Mohammed Jameel says:

    Thanks for this artical helped me resolve a serious problem.

  4. Mohammed Jameel says:

    Thanks for this artical helped me resolve a serious problem.

  5. Clayton H says:

    I'm having the same problem as Mark. Has anyone found an answer to this?

  6. Miller Bends says:

    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.

  7. Gotsa Job says:

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

  8. Nour says:

    in this case Execute a Drop statistics statement

Skip to main content