Maintenance Plans Enable Your Disabled Indexes

One of Brent’s students in the Performance Tuning class, Jason M., told Brent that maintenance plans enable your disabled indexes. What the what?

Steps to reproduce:

  1. Disable an index
  2. Setup a maintenance plan to rebuild all indexes
  3. Run the job
  4. Check if the index is disabled or enabled.

Steps 1 and 2 can be reversed and still reproduce the issue.

I tested it on SQL Server 2014, 2016 and 2017. All 3 behave the same: Rebuild Index Task in a maintenance plan enables disabled indexes.

Bug or Feature?

Not sure if Microsoft thinks this is a bug or a feature, but in my eyes it’s a bug. It should ignore disabled indexes.

We don’t advise using maintenance plans anyway. We recommend using Ola‘s IndexOptimize stored procedure, or another custom solution, for your index maintenance needs.

Why disable an index?

We often tell people to hold off on dropping an index for a little bit and instead disable them. The benefit to this is that you keep the index definition in place, so you don’t have to go find it when you realize your system needs that index. Drop the index at a later date is our advice. But now be warned if we you are using a maintenance plan for to rebuild indexes.

Thanks for reporting this maintenance plan issue, Jason!

Previous Post
Introducing sp_BlitzLock: For Troubleshooting SQL Server Deadlocks
Next Post
Do Disabled Indexes Affect Missing Index Recommendations?

6 Comments. Leave new

  • It’s been a documented part of SQL maintenance plans for years so I was shocked when I read others were unaware of it. Issuing a rebuild on an index enables it. If you disable it and want it back, you also have to perform a rebuild to get it back so you can save a right click and just issue rebuild index….

    Reply
    • Do you have a link for it? I did try to find any mention of it before I decided to write a blog post. I came up empty, but maybe your googling skills are better than mine. 😉

      Yes issuing a rebuild enables it, but a maintenance script should be ignoring disabled indexes. That’s the big point here.

      Reply
  • Maintenance plans – I never was a big fan of them and I don’t use them since ages.
    But whoever wrote the index maintenance part should stay away from writing stuff for sql servers.

    Reply
    • Same. Actually I’ve never used maintenance plans on the server that I support. And I’ve been a DBA for almost 20 years. Past few years have used Ola’s solution, but I did have my own custom solution up until a few years ago.

      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":""}