FIX: Index Tuning Wizard Fails to Remove Hypothetical Clustered Indexes

Article translations Article translations
Article ID: 290414 - View products that this article applies to.
This article was previously published under Q290414
BUG #: 235118 (SHILOH_BUGS)
Expand all | Collapse all


After successfully running the Index Tuning Wizard, the hypothetical clustered indexes, whose names start with "hind", may be left behind in the database.


To resolve this problem, obtain the latest service pack for SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack


To work around this behavior:
  1. Find the hypothetical clustered indexes by executing the following query:
    SELECT name
    WHERE name LIKE 'hind%'
  2. Drop the indexes by using the DROP INDEX Transact-SQL command or by right-clicking the table in SQL Enterprise Manager, and then clicking Design Table.


Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.


The query optimizer will not use the hypothetical indexes that are left behind. Therefore, these hypothetical indexes will not cause you to obtain a bad query plan.

However,if you leave the hypothetical indexes behind, the statement or procedure might recompile several times and affect performance. Additionally, leaving the hypothetical indexes may negatively affect the quality of recommendations from later runs of the Index Tuning Wizard because there is a limit to the number of hypothetical indexes that can be created per table. Therefore, you must drop these hypothetical indexes.

For more information about a related issue, click the following article number to view the article in the Microsoft Knowledge Base:
293177 BUG: Hypothetical clustered index from Index Tuning Wizard may cause recompile loop


Article ID: 290414 - Last Review: April 23, 2007 - Revision: 4.2
  • Microsoft SQL Server 2000 Standard Edition
kbbug kbfix kbsqlserv2000sp1fix KB290414

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from