Microsoft KB Archive/290414

= FIX: Index Tuning Wizard Fails to Remove Hypothetical Clustered Indexes =

Article ID: 290414

Article Last Modified on 4/23/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q290414



BUG #: 235118 (SHILOH_BUGS)



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



RESOLUTION
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



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
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

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB290414

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.