Identify and remove unused indexes in SQL Server
Introduction:
Indexes means order information about data, with indexes we can retrieve data from the database more quickly than otherwise. Index seeks, scans and lookups operators are used to access SQL Server indexes.
Issue:
However, Against this great benefit of the indexes, they occupy space on hard drives and can slow down the data modification operations (update, insert, delete) performance. Therefore, we have to find unused indexes to reduce performance issue.
Finding unused index and remove:
There is Dynamic Management Views (DMV) which will displays essential information about index usage.
DMV: select * from sys.dm_db_index_usage_stats.
Above DMV is useful to identifying unused SQL Server indexes. From result of above index, we should look out on three operators count. That is Seek and scan and lookups counts.
So now that unused SQL Server indexes are identified and listed, it can be determined which indexes can be dropped safely, but again that has to be done very carefully.
