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:
- Disable an index
- Setup a maintenance plan to rebuild all indexes
- Run the job
- 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.
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!