SQL Server 2008R2 – Cannot Disable Non-Clustered Index

indexsql-server-2008-r2

We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them. However when using the GUI in SSMS (right click > disable) OR running ALTER INDEX DISABLE it says Successful but the index is still online. I cannot seem to disable it. Even in AW2008R2 I cannot disable an index. Help!

Best Answer

You are relying on the graphical tree icon in SSMS to indicate that an index is disabled. In my experience, this has never been true for indexes (though it is true for certain other things, like jobs and triggers).

To address Paul's comment:

No, I don't know why they changed this behaviour.

I don't know that they have changed it in the way the asker suggests:

I swear it used to show a little down arrow on the index icon in the tree.

Unless we're going back to 2005 days (I have 2005 instances, but no 2005 Management Studio handy to test). In one way, they've actually improved the behavior; in another, they've made it worse. Let me explain.

In the following screen shot, 2012/2014 is on the left, 2008/2008 R2 is on the right. There is even less visual cue in the older UI about the index being disabled. Notice that the index name in the tree has the same visual and iconic representation in both versions, even though the index is disabled (click to enlarge), and only 2012/2014 has the warning that Paul mentioned:

Godzilla vs. Mothra part 1

So, as I said, I think the visualization has actually improved, even if it is less than perfect - I do tend to agree that there should be a red down arrow or something similar to be consistent with other elements in the UI (have you filed a Connect item yet?).

Now. how has it gotten worse in the newer versions of SSMS? Well, I can't find a way to re-enable the index in the UI. In 2008/2008 R2, there was a "Use index" checkbox on the Options tab, which works - though I'm not sure how intuitive that is. There's also a checkbox for "Rebuild index" which seems to be more appropriate for a button, since it's not an option or a property, but I suppose that is a different discussion (click to enlarge):

Godzilla vs. Mothra part 2

To verify the index is really disabled, I would not use the UI at all, but rather the catalog views:

SELECT i.name, i.is_disabled 
  FROM sys.indexes AS i
  INNER JOIN sys.objects AS o
  ON i.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo' -- or another schema
    AND o.name = N'the table the index belongs to'
    AND i.name = N'the index you disabled';

You can leave out the last AND if you want to see all the indexes for a table. If you want to see all disabled indexes in your database, small change:

SELECT s.name + '.' + o.name, i.name
  FROM sys.indexes AS i
  INNER JOIN sys.objects AS o
  ON i.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE i.is_disabled = 1;

You can also just right-click the index in the tree, Script Index As > Create To > New Query Editor Window, and you should see that - for an index that you have disabled - the index includes a DISABLE script at the end, e.g.:

USE [yourdb]
GO

CREATE INDEX ...
GO

ALTER INDEX [x] ON [dbo].[y] DISABLE
GO
Related Question