Hello, 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? Thanks pchee
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) 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 Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
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?
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 Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I'm not sure about that. Inhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q311826, 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 Moderator SQL-Server-Performance.com 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.
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.
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 Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
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?
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.
