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.
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!
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….
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.
I didn’t see it under the Rebuild Index Task (Maintenance Plan) document directly but it is inferred because the task uses the ALTER INDEX statement and that document states “REBUILD enables a disabled index.” under the REBUILD argument paragraph. But still doesn’t explain why a maintenance plan would include the disabled indexes by default.
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/rebuild-index-task-maintenance-plan
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
But that’s the thing – I don’t see it as being inferred. Yes, when you specifically run ALTER INDEX DisabledIndexNameGoesHere REBUILD it enables the index, but that’s not what I want to do when my job runs to defragment indexes. What I didn’t test was whether or not the new fragmentation thresholds in the plan in 2016+ if it ignores the disabled index since it’s not fragmented. I’d assume the issue doesn’t occur there, but who knows. And now that my blog post is written, I don’t have to touch maintenance plans for a while. hehe
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.
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.