To Fix the Problemįirst, do some research. We check sys.indexes looking for indexes with is_disabled = 1. (Maintenance plans that rebuild indexes do this, shockingly!)
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. Unfortunately, sometimes our load processes crash before enabling the indexes again, or sometimes we manually load data and we forget to rebuild them. Disabling nonclustered indexes is safer than dropping and recreating them because scripting indexes is hard. This technique works best in large data warehouse environments where entire dimension tables might be reloaded from scratch every night. After the data load finishes, enable the nonclustered indexes again by rebuilding them.
If you need to load a lot of data quickly, you can disable nonclustered indexes in order to improve performance.
