Hypothetical Indexes

-August 2009+
SMTWTFS
2627282930311
2345678
9101112131415
16171819202122
23242526272829
303112345

Statistics

  • Entries (3)
  • Comments (0)

Categories

Posted by Philip Leitch Friday, August 7, 2009 1:38:00 AM Categories: Transact SQL
Rate this Content 0 Votes

When the Index Tuning Wizard runs, it creates what are called hypothetical indexes in the sysindexes table. The names of these indexes start with "hind_%". These tables are used by the Index Tuning Wizard to help determine if new indexes should be added to your tables.
 
Normally, these hypothetical tables should be deleted when the Index Wizard is completed, but if the Index Wizard is interrupted before it is completed, it may leave these hypothetical indexes in the sysindexes table.
 
In some cases, the existence of these tables can lead to an unusual performance problem. What can happen is that some stored procedures may be forced to recompile every time they run, even if they should not be recompiled.
 
The best way to ensure that you don't have any unnecessary "hind_%" tables in your sysindexes table is to run this script (I think it is originally sourced from Microsoft):
 
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

 
Copyright 2009 Philip Leitch