Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 21 '13 at 11:39

This question came from our site for professional and enthusiast programmers.

    
How about a screenshot of what a hypothetical looks like? –  Jason Cumberland May 22 '13 at 1:50
    
Hi Jason. Run SELECT * FROM sys.indexes WHERE is_hypothetical = 1 –  Gizmo May 22 '13 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.) –  Mike Sherrill 'Cat Recall' May 29 '13 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. –  Kenneth Fisher Jun 20 '13 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! –  Gizmo Jun 21 '13 at 2:31

3 Answers 3

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

share|improve this answer
    
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! –  Gizmo Oct 8 '13 at 12:30

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.

share|improve this answer
    
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". –  Gizmo May 22 '13 at 19:01
    
Do you think running dbcc checktable is called for? –  Mike Sherrill 'Cat Recall' May 22 '13 at 19:28
    
Full checker is clean –  Gizmo Jun 21 '13 at 2:44

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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