FIX: Executing MAX or MIN Aggregate Functions on Column with Hypothetical Clustered Index May Result in CPU SPIN

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

SYMPTOMS

Executing the MAX or MIN aggregate functions on a column, which has a hypothetical clustered index that triggers a recompile, may cause a CPU spin to occur.

CAUSE

The statistics for the hypothetical clustered index that is generated by the Index Tuning Wizard is not refreshed. As a result, the sysindexes.rowmodctr column remains unchanged, resulting in an endless recompile loop.

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 drop the hypothetical clustered index as follows
DROP INDEX tablename.hind_c_2046630334_2A_3A
				
Where "hind_c_2046630334_2A_3A" is the name of a hypothetical clustered index created by the Index Tuning Wizard.

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

To determine if you have a hypothetical clustered index created by the Index Tuning Wizard you can execute the sp_help stored procedure on the table and check for an index_name that begins with "hind" and an index_description that contains both "clustered" and "hypothetical" in the result. Following is a simplified sample result:
exec sp_help '<tablename>'
... ...
index_name                index_description         
------------------------  --------------------------
hind_c_2046630334_2A_3A   clustered, hypothetical
... ...
				
NOTE: The issue of the Index Tuning Wizard not removing hypothetical indexes is resolved and is documented in the following article in the Microsoft Knowledge Base:
290414 FIX: Index Tuning Wizard Fails to Remove Hypothetical Index

Properties

Article ID: 299914 - Last Review: November 5, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB299914

Give Feedback

 

Contact us for more help

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