6

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!

4
  • Where are you verifying that the index is "online"? – Aaron Bertrand Jan 27 '14 at 19:47
  • In looking at the tree, there is no indicator it is disabled, nor does it indicate it has been disabled in any way. I swear it used to show a little down arrow on the index icon in the tree. It literally looks exactly the same as the other indexes I left untouched so how am I to know which is enabled and which is disabled? – jbwebtech Jan 27 '14 at 19:54
  • * the 'explorer' tree inside SSMS I should add. – jbwebtech Jan 27 '14 at 19:54
  • @PaulWhite I've addressed the last sentence in your comment in my answer. :-) – Aaron Bertrand Jan 29 '14 at 18:21
9

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
2
  • Hot dog! Those both worked, wow, thank you! Okay but humor me... shouldn't the GUI indicate it is disabled? – jbwebtech Jan 27 '14 at 20:02
  • 1
    @jbwebtech Maybe. The GUI doesn't change every single icon to indicate some property has been set or not, and I suspect that the number of people who disable indexes - and want to verify such in the UI - is a very small set of the SQL Server customer base. If you think this is important, by all means, file a suggestion at connect.microsoft.com/sql - just don't hold your breath, as they have made very minimal investments into new features in SSMS over the past, oh, 6 years. – Aaron Bertrand Jan 27 '14 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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