Do Disabled Indexes Affect Missing Index Recommendations?

Last Updated 6 years ago

I’m so glad you asked! Let’s take a look. Open up the Stack Overflow database, turn on actual execution plans, and run a query that will cause SQL Server to beg and plead for an index:

SELECT *
FROM dbo.Users
WHERE DisplayName = 'Hovercraft Full Of Eels';

SELECT *

FROM dbo.Users

WHERE DisplayName = 'Hovercraft Full Of Eels';

And we get a missing index recommendation in the plan:

Now create the index, and try the query again:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Users] ([DisplayName]);
GO
SELECT *
FROM dbo.Users
WHERE DisplayName = 'Hovercraft Full Of Eels';
GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Users] ([DisplayName]);

SELECT *

FROM dbo.Users

WHERE DisplayName = 'Hovercraft Full Of Eels';

Oh, don’t act like you haven’t done that. And sure enough, SQL Server uses the index:

Not everybody likes their name or their initials

Now disable that index, and run the query again, looking to see if there’s a missing index recommendation in the plan:

ALTER INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Users] DISABLE;
GO
SELECT *
FROM dbo.Users
WHERE DisplayName = 'Hovercraft Full Of Eels';
GO

ALTER INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Users] DISABLE;

SELECT *

FROM dbo.Users

WHERE DisplayName = 'Hovercraft Full Of Eels';

And let’s see if SQL Server obeys Betteridge’s law:

It does! SQL Server asks for the index. Sure, the index exists – it even has the same name – but it’s currently disabled.

The missing index DMVs even keep track, as we can see with sp_BlitzIndex:

Love that. Good work, SQL Server. You can take the afternoon off.

Maintenance Plans Enable Your Disabled Indexes

Sizing A New Server? Start With Maintenance.

8 Comments. Leave new

Ken
December 21, 2017 1:12 pm

And interestingly, if you disable a clustered index, the index ‘s type_desc will still marked as “CLUSTERED” rather than “HEAP”, which also make sense in a way, but certainly it is a heap table, from the point when the clustered index is disabled?

Reply
- Wes
  January 22, 2018 3:03 pm
  
  I don’t think disabling the clustered index turns it into a heap. If I recall, disabling the clustered index removes the ability to read/write to the table until the clustered index is available again. However, dropping the clustered index definitely does turn it into a heap, even if their are non-clustered indexes present.
  
  Reply
  - Erik Darling
    January 22, 2018 5:00 pm
    
    Wes — righto!
    
    Reply
Michael Acas
December 21, 2017 4:28 pm

Is Betteridge’s Law True?

Maybe

Reply
Adrian Engelbrecht
December 22, 2017 3:40 pm

Disabled indexes are enabled when they are rebuilt. So if you want an index to remain disabled, exclude it from regular rebuilds that will enable it again.

Reply
- Erik Darling
  December 22, 2017 5:13 pm
  
  Adrian – interesting! Where do you see that option in Maintenance Plans?
  
  Reply
- alen teplitsky
  January 22, 2018 7:50 am
  
  Is that in newer versions? My last job I had a lot of disabled indexes and had a daily email for which ones were disabled and don’t remember them being enabled after maintenance. But that was with SQL 2005 and 2012 and using scripts instead of maintenance plans.
  
  Reply
Adrian Engelbrecht
December 27, 2017 5:46 am

There is no exclude option in maintenance plans. Olga Hallengren has some good manual jobs that might be more selective.

Reply