FIX: Index Tuning Wizard Fails to Remove Hypothetical Clustered Indexes (290414)

SYMPTOMS

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

RESOLUTION

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

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this behavior:

Find the hypothetical clustered indexes by executing the following query:
```
SELECT name
FROM SYSINDEXES
WHERE name LIKE 'hind%'
					
```
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.

STATUS

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

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.

For information on a related issue, see the following article in the Microsoft Knowledge Base:

293177 BUG: Hypothetical Clustered Index From Index Tuning Wizard May Cause Recompile Loop