FIX: Index Tuning Wizard Fails to Remove Hypothetical Clustered Indexes

This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 235118 (SHILOH_BUGS)
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 nameFROM SYSINDEXESWHERE 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.
More information
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: 01/16/2015 21:46:52 - Revision: 5.0

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB290414