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?

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

  • Is Betteridge’s Law True?

    Maybe

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

    • Adrian – interesting! Where do you see that option in Maintenance Plans?

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

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

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