Get Rid of Redundant Indexes

Redundant indexes in SQL Server can negatively impact the performance and efficiency of a database.

There are two Scenarios which an Index can be marked as Redundant Index.

  1. Scenario 1: By checking the DMV sys.dm_db_index_usage_stats for usage.
  2. Scenario 2: If one Index is a Subset of Another Index. Then SQL Optimizer will not pick subset index.

In the above-mentioned cases, Index can be marked as Redundant and must be monitored for some period.

Now, follow the below process to remove these Redundant Indexes:

  1. Disable the redundant index: Use the ALTER INDEX command to temporarily disable the redundant index, in order to see if the other index will still be used.
  2. Test query performance: Run a representative set of queries that use the columns in the redundant index to determine if the performance impact of dropping the redundant index is acceptable.
  3. Drop the redundant index: If the performance impact of dropping the redundant index is acceptable, use the DROP INDEX command to remove the redundant index.

For Example, Events_IDX_Event_Num is subset of ix_Events_Event_Num. In this case, Events_IDX_Event_Num can be removed after monitoring the Performance once disabled.