8

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.

6
  • How about a screenshot of what a hypothetical looks like? May 22, 2013 at 1:50
  • Hi Jason. Run SELECT * FROM sys.indexes WHERE is_hypothetical = 1
    – PseudoToad
    May 22, 2013 at 18:53
  • DROP INDEX requires ALTER permission on the table. Can you issue a benign ALTER TABLE statement to test your permissions? (I know. I know. You're a sysadmin. Do it anyway.) May 29, 2013 at 0:56
  • 1
    I'm assuming you are seeing these indexes in SSMS correct? If so try refreshing your connection and see if the indexes are still there. They may have already been deleted. Also the DROP INDEX is the correct syntax for both clustered and non clustered indexes. DROP STATISTICS is specifically for statistics which are different. Jun 20, 2013 at 19:41
  • Yea... Im in SSMS and I've tried all the above multiple times. CheckDB is clean. I even tried doing it through o-SQL and SQLCMD hoping that using a different connection stack would break it free. I am throwing in the towel and living with it. At some point I'll drop and recreate the tables. That will definitely fix it!
    – PseudoToad
    Jun 21, 2013 at 2:31

3 Answers 3

4

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

1
  • Hi Sam. Unfortunately this brings me back to the start. The script shows me the hypothetical indexes and give me the correct drop statement which I've been using all along. The problem is that the drop statement simply doesn't work. I am of the belief that this is a bug in the engine and have pretty much given up on it. At some point, I'll take the tables out of production then drop/recreate everything from scratch. I know that will fix it!
    – PseudoToad
    Oct 8, 2013 at 12:30
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.

3
  • Thanks Mike. All of these are hypothetical so are not going to fall into any of these categories. Aside from this these are all non-clustered hypothetical indexes which are treated as a statistics for dropping. Ultimately they are not "real indexes".
    – PseudoToad
    May 22, 2013 at 19:01
  • Do you think running dbcc checktable is called for? May 22, 2013 at 19:28
  • Full checker is clean
    – PseudoToad
    Jun 21, 2013 at 2:44
1

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.

1
  • Just tried this - open DTA, connect to server, delete all previous sessions. still have dta index and stats in the database. Oct 1, 2018 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.