SQL Server Performance

Hypothetical indexes

Discussion in 'Performance Tuning for DBAs' started by pchee373, Jul 13, 2005.

  1. pchee373 New Member


    I was running a sp_helpindex on my tables and received a lot of nonclustered, hypothetical, auto created indexes. From what I've read so far it was probably created by index tuning wizard. Can someone explain to me what exactly they are and are they a drain on resources?

  2. satya Moderator

    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.

    One of the KBA refers this code to remove them@

    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)
    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)))
    FETCH NEXT FROM ITW_Stats INTO @objid, @indid
    CLOSE ITW_Stats

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. pchee373 New Member

    It's gone. Thanks
  4. beano New Member

    I'm confused why hypothetical indexes are not removed by the Index Wizard. When I start up the Index Tuning Wizard, and select my tables, I always select the option to save the recommendations to a sql file, instead of directly applying it to the db that I'm working on. This way I can review the changes, before applying them directly.

    That said, the sql file generated contains the CREATE STATISTICS [hind_1259867555_8A_1A] statements, but no explicit DROP STATISTICS statement. I've seen articles about how if the Index Tuning Wizard is interrupted while applying updates, it will forget to drop these indexes. However, in my case, I did not interrupt the process. Shouldn't these statistics help query performance?
  5. satya Moderator

    You approach is good in such a way to choose specific indexes for table(s).

    The index tuning wizard creates them and there is no feature in SQL server to remove indexes automatically as it is used by optimizer at any time, so if you can see there is no use with them then drop them straight away as referred above.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. Luis Martin Moderator

    I'm not sure about that.

    Microsoft said about hypothetical clusters indexes begins with hind_ not about statistics begins whit hind_.

    Personally I have no problems (in my clients) with that.
    I find hypothetical, from time to time, indexes in my clients begins with hind_ and begins with _WA but, in my oppinion, not all hind_ created by ITW are hypothetical indexes.

    If all were hypothetical, well in SQL 2005 they (Microsoft) does not fix the problem, because ITW 2005, also create statistics like ITW 2000.

    Luis Martin

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    All postings are provided “AS IS” with no warranties for accuracy.

  7. indshri Member

    I believe hind_ indexes are only created by index tuning wizard whereas _WA gets created if auto create statistics option is turned on. And these hind_ has no use once ITW is done with its work.
  8. satya Moderator

    Sometimes these _WA indexes have a bit negative affect on the performance, as it proved at my end when they are deleted the performance of a particular query was better than before.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. jarad New Member

    IF I drop my database and use a script to create the database and indexes then do I have to create the statistics again?
    By default Sql Server create and update statistics is that good enough?
  10. Luis Martin Moderator

    If you create a database with data and indexes, you have to update statistics with full.Also if you backup some database and backup in other server, you have to update statistics again.

Share This Page