In order to improve your applications and your databases, they will need to change over time. The structure of the database changes, the structure of the tables change, the data in the tables change, the application changes, the queries against the data change. Indexes that once helped performance now just bloat your database and cause extra work for inserts, updates, and deletes.
When index needs change and you want to test how removing an index will affect performance, you have two options – you can disable or drop the index. Which is better?
Disabling an Index
To disable in index, issue an ALTER INDEX command.
ALTER INDEX IX_IndexName ON Schema.TableName DISABLE;
What happens when you do this? The metadata about the index is retained, as are the usage statistics in sys.dm_db_index_usage_stats. The index is not usable by the query optimizer, however. If you have an index hint in a query specifying the index, the query will fail. Also, foreign key constraints are disabled.
Disabling Nonclustered Indexes vs. Disabling Clustered Indexes
Disabling a nonclustered index will deallocate the index pages – the space is freed in the database.
Disabling a clustered index has additional effects. The data in the table still exists, but will be inaccessible for anything other than a drop or rebuild operation. All related nonclustered indexes and views are also unavailable. Foreign key constraints that reference the table are disabled. Queries against the table will fail.
I want it back! How to re-enable a disabled index
If you want to re-enable the index, you need to rebuild it – there is no ALTER INDEX…ENABLE command. The rebuild works like this:
ALTER INDEX IX_IndexName ON Schema.TableName REBUILD;
When you rebuild an index, the usage stats will be reset in sys.dm_db_index_usage_stats.
Dropping an Index
To drop a clustered or nonclustered index, issue a DROP INDEX command.
DROP INDEX IndexName ON Schema.TableName;
When you do this, the metadata, statistics, and index pages are removed. If you drop a clustered index, the table will become a heap.
Once an index has been dropped, it can’t be rebuilt – it must be created again. That means that if you are going to drop an existing index, make sure you script the CREATE statement before you pull the trigger.
Do you want to maintain statistics?
The biggest difference between disabling and dropping a nonclustered index is whether the index definition stays around. Make sure you carefully weigh your options before performing either action, and always have a way to recreate the index available.
LEARN MORE ABOUT INDEXES
Read all about it: We’ve got tons of free articles. Check out our top articles on indexes.
Free tools: Get our free tool to diagnose index insanity, sp_BlitzIndex®
Online video training: Check out our online course on How to Tune Indexes in SQL Server
In person advanced training: Learn from us in person in our Advanced Querying and Indexing course