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

SYMPTOMS

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.

CAUSE

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.

WORKAROUND

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

MORE INFORMATION

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.