Article ID: 293177
Article Last Modified on 10/31/2003
APPLIES TO
- 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)
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
STATUS
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.
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.
Keywords: kbbug kbpending KB293177
