Dropping Hypothetical Indexes

Question

In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.

I have a database that is full of DTA remnants that I would like to cleanup; however, when I try to drop the object I always receive an error telling me that I cannot drop the object "because it does not exist or you do not have permission". I am a full sysadmin on the server so would expect to have rights to do anything.

I've tried this with both DROP STATS and DROP INDEX statements but both give me the same error.

Has anyone deleted these before and is there a trick I'm missing?

---

Addendum

Poking around in this, I just noticed that if I R-Click on the object, both the 'Script As' and 'DELETE' options are greyed out.

Answer 1

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

Above script is provided by Microsoft for more detail you can visit following link. http://support.microsoft.com/kb/q293177

Answer 2

Books online includes several reasons you might not be able to drop an index.

• The index is associated with a primary key constraint.
• Filegroup is offline or read-only.
• "You cannot drop a clustered index online if the index is disabled on a view, or contains text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or xml columns in the leaf-level data rows."
• You don't have the right permissions: "To execute DROP INDEX, at a minimum, ALTER permission on the table or view is required. This permission is granted by default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles."

There might be other reasons, too.

Answer 3

This is a little simplistic, but if you are finished with the DTA suggestions, try removing the tuning sessions from that server through the DTA interface.