Blitz Result: Indexes Disabled
If you need to load a lot of data quickly, you can disable indexes in order to improve performance. After the data load finishes, enable the indexes again, and SQL Server will rebuild them behind the scenes. This technique works best in large data warehouse environments where entire dimension tables might be reloaded from scratch every night. Disabling indexes is safer than dropping and recreating them because scripting indexes is hard – especially when SQL keeps adding new index capabilities like Column Store.
Unfortunately, sometimes our load processes crash before enabling the indexes again, or sometimes we manually load data and we forget to enable the indexes again.
This part of our SQL Server sp_Blitz script checks sys.indexes looking for indexes with is_disabled = 1.
To Fix the Problem
You can run this:
ALTER INDEX IX_MyIndexName ON dbo.MyTableName REBUILD
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 and look for overlapping indexes.
After this change, when it’s done right (by identifying unused or overlapping indexes), you should see improved performance of SELECT queries that leverage the previously disabled index.