Microsoft KB Archive/293177
Article ID: 293177
Article Last Modified on 10/31/2003
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q293177
BUG #: 101414 (SQLBUG_70)
BUG #: 352798 (SHILOH)
After you run the Index Tuning Wizard, especially in a scenario where the Index Tuning Wizard is not allowed to run to completion, SQL Server may encounter a problem in which it will repeatedly recompile a plan.
If the query that triggered the recompile is a statement inside a batch, the SQL Profiler repeatedly reports a SQL:StmtStarting event for the same statement, with no intermediate SQL:StmtCompleted event. If the statement is inside a stored procedure, the SQL Profiler shows an alternating pattern of SP:StmtStarting and SP:Recompile events. Additionally, in the case of a stored procedure you may also see blocking on [COMPILE] locks for the stored procedure as other users attempt to run the procedure.
As part of the tuning process, the Index Tuning Wizard creates hypothetical indexes (statistics) on the tables that are being tuned. If the Index Tuning Wizard is not allowed to run to completion, these statistics may not be dropped at the end of the tuning process. Because the hypothetical indexes are intended to exist only for the duration of an Index Tuning Wizard session, these statistics are not maintained in the same way as those statistics from an index, CREATE STATISTICS statement or auto create statistics option.
After the initial recompile is triggered, the optimizer is using some of the information from these hypothetical indexes, which is out of date, and incorrectly determines that a recompile is needed again. During the ensuing recompiles, the information from the hypothetical indexes is never refreshed, and so the optimizer remains in a recompile loop.
The hypothetical indexes created by the Index Tuning Wizard start with a name of "hind_%" and should not exist after the tuning has finished; they should all be removed. You can run the following script from the SQL Server Query Analyzer to remove any such indexes that may exist. You must log in by using an account that has either sysadmin or db_owner permissions, or is the owner of the object on which these statistics were created. For example:
DECLARE @strSQL nvarchar(1024) DECLARE @objid int DECLARE @indid tinyint DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name OPEN ITW_Stats FETCH NEXT FROM ITW_Stats INTO @objid, @indid WHILE (@@FETCH_STATUS <> -1) BEGIN SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']' FROM sysindexes i join sysobjects o on i.id = o.id WHERE i.id = @objid and i.indid = @indid AND (INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR (INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0))) EXEC(@strSQL) FETCH NEXT FROM ITW_Stats INTO @objid, @indid END CLOSE ITW_Stats DEALLOCATE ITW_Stats
Microsoft has confirmed this to be a problem in SQL Server 7.0 Service Pack 2 or later.
Microsoft has confirmed this to be a problem in SQL Server 2000.
Prior to SQL Server 7.0 Service Pack 2, if this situation occurred, the statement or procedure might recompile a few times; however, it would not enter this infinite recompile loop. With SQL Server 7.0 Service Pack 2 or later, the statement continues to recompile until a timeout occurs or some other schema modification happens on the table.
Keywords: kbbug kbpending KB293177