We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources. If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer. Those updates run longer because they have to maintain those unused indexes. If your application is not using an index, then there is no value in having that index.
Periodically you should review your index usage statistics to determine how your indexes are being used. You can do that by using the sys.dm_db_index_usage_stats dynamic management view (DMV). This view will show you if your indexes are being used in a seek, scan, or lookup operation, and whether or not the indexes have been updated. Remember DMVs only track information since SQL Server started. Therefore, you need to consider running this DMV after SQL Server has been up for a reasonable time in order to get an accurate picture of how your indexes have been used.
By using the sys.dm_db_index_usage_stats you can identify those indexes that have never been used. In the following code there are two SELECT statements with a UNION clause between them. The first SELECT statement identifies those indexes that have never been used but have been update in your databases. The second SELECT statement identifies those indexes that have never been used and have not been updated. By “updated” I mean the index has either been maintained due to an INSERT, UPDATE or a DELETE operation. You should consider running this query periodically to identify those indexes that are providing your application no value but are costing you compute and disk space resources. Once you know what indexes are not being used, then you can determine whether or not you even need those indexes.
-- indexes that have been updated and not used select SCHEMA_NAME(o.schema_id) as [schema_name], OBJECT_NAME(s.object_id) table_name, i.name index_name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, 'yes' Index_updated_but_not_used from sys.dm_db_index_usage_stats s join sys.objects o on s.object_id = o.object_id join sys.indexes i on s.index_id = i.index_id and s.object_id = i.object_id where (s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0) and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 UNION -- indexes that have not been updated or used SELECT SCHEMA_NAME(o.schema_id) as [schema_name], OBJECT_NAME(o.object_id) table_name, i.name index_name, 0 as user_seeks, 0 as user_scans, 0 as user_lookups, 0 as user_updates, 'no' as Index_updated_but_not_used FROM sys.indexes i JOIN sys.objects o on i.object_id = o.object_id WHERE i.index_id NOT IN ( SELECT s.index_id FROM sys.dm_db_index_usage_stats s WHERE s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID(DB_NAME())) and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 order by Index_updated_but_not_used desc;