Do Disabled Indexes Affect Missing Index Recommendations?

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:

And we get a missing index recommendation in the plan:

The side of the milk carton

Now create the index, and try the query again:

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:

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

Does MongoDB poop in the woods?

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:

sp_BlitzIndex @TableName = ‘Users’

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

Previous Post
Maintenance Plans Enable Your Disabled Indexes
Next Post
Sizing A New Server? Start With Maintenance.

8 Comments. Leave new

  • 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
    • 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
  • 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
    • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}