Are Your Indexes Being Used Effectively?

You could see that the values for INDEX ID from 1…4 has incremented by 1 to 21 from the previous value of 20 and for INDEX ID 5 the value has remained unchanged. In this way you can identify which indexes are really helpful and which are very rarely used. If you are interested to know when each of the above indexes were last used then you can use the query below:

Before dropping an index it would be ideal to disable the index on the table and see is there is any performance hit when the index is disabled. Disabling a non clustered index prevents users from accessing the particular non clustered index defined on the underlying table. However, the index definition remains in metadata and index statistics are also kept on nonclustered indexes. You need to keep in mind that if you disable the clustered index by any chance then you will not be able to access the data in the underlying table until the index is dropped or rebuilt.

Finally after the analysis when it is discovered that the queries are not using the IX_Employee_ManagerID index then you can execute the below TSQL code to drop the index on the HumanResources.Employee table.

Conclusion
Using the sys.dm_db_index_usage_stats Dynamic Management View, you can easily identify indexes which are used often by queries and also ndexes which are rarely used. Once you have identified the indexes, the best approach is to disable the indexes for some time and see is there is any performance degradation once the indexes are disabled. If there is no performance degradation then you can go ahead and drop the index there by saving disk space and improving performace during Insert, Update and Delete operations.