Indexes Disabled

If you need to load a lot of data quickly, you can disable nonclustered indexes in order to improve performance.  After the data load finishes, enable the nonclustered indexes again by rebuilding them.

This technique works best in large data warehouse environments where entire dimension tables might be reloaded from scratch every night. Disabling nonclustered indexes is safer than dropping and recreating them because scripting indexes is hard.

Unfortunately, sometimes our load processes crash before enabling the indexes again, or sometimes we manually load data and we forget to rebuild them.

Sometimes, we even disable indexes on purpose, but that can be a bad idea too: someone can come along behind you, run an index rebuild script without checking for whether you disabled an index, and the index is enabled again. (Maintenance plans that rebuild indexes do this, shockingly!)

We check sys.indexes looking for indexes with is_disabled = 1.

To Fix the Problem

First, do some research. Do you still need these nonclustered indexes? They might be duplicates, or no longer useful.

• Review index health on the table with sp_BlitzIndex®
• For each index, decide whether to bring the non-clustered index back with a rebuild, or whether you should drop it

After this change, if SQL Server needs this index, you should see improved query performance.

---

Re-enabling Indexes Using Index Rebuilds

You can run bring disabled nonclustered indexes back into a normal state just by rebuilding them, with T-SQL like this:

But there’s a danger: SQL Server will immediately rebuild that index, and if we’re talking about a big table, it might take some time.  Do this judiciously, and consider doing it during a maintenance window.

The next risk is that now inserts, updates, and deletes on this table will be slower than they’ve been before – because from this point forward, SQL Server will be updating this index with each new/changed/removed record.  That’s why as long as you’re doing index work, you should review all indexes on this table.

How to Drop Indexes You No Longer Need

If you no longer need the index, get rid of that baggage. Here’s sample T-SQL syntax to drop an index: