Having an Index on a table will indeed be good for the performance of queries. At the same time having too many indexes on a table will hurt the query performance, increases storage requirement and add additional overhead for maintenance.
This article covers how you can identify unused non-clustered indexes in the database.
SQL Server logs the number of times an index was scanned by the user query and time when it was last scanned. Similarly, seek, lookup and updates operations are logged and they are visible through the dynamic management view (DMV). sys.dm_db_index_usage_stats is the view that shows the index usage information and it will be useful for us to determine the index usage.
The following script will list all the non-clustered indexes from the database except the index with the primary key. It shows record count, record size for each index. Provides the index usage count and the last time index accessed and SQL Server start time.
It is important to know the start time of the SQL server as the information provided by the view is cleaned whenever the server restarted. Refer to BOL for more information about the view and limitations.
The information returned from the usage count is aggregated from all the four operations (scan, seek, lookup and updates)
The data returned from the Last_User_Used_date is the maximum of all the four operations (scan, seek, lookup and updates)
declare @start_time as datetime select @start_time = sqlserver_start_time from sys.dm_os_sys_info; with nonclustered_index_size as ( select t.object_id, i.index_id, t.name as table_name, schema_name(t.schema_id) as table_schema, i.name as index_name, i.type_desc as index_type, pa.data_compression_desc, au.type_desc as storage_type, pa.rows, au.total_pages from sys.tables t join sys.indexes i on i.object_id = t.object_id and i.index_id >= 2 and i.is_primary_key = 0 join sys.partitions pa on t.object_id = pa.object_id and i.index_id = pa.index_id join sys.allocation_units au on au.container_id = case when au.type in (1, 3) then pa.hobt_id else pa.partition_id end ), index_usage_stats as ( select object_id, index_id, Last_User_Used_date, usage_count from sys.dm_db_index_usage_stats cross apply ( select max(n) from ( values (last_user_lookup), (last_user_scan), (last_user_seek), (last_user_update) ) as m(n) ) as a(Last_User_Used_date) cross apply ( select sum(n) from ( values (user_seeks), (user_scans), (user_lookups), (user_updates) ) as m(n) ) as b(usage_count) where database_id = db_id() ) select table_schema, table_name, index_name, index_type, data_compression_desc, storage_type, rows, total_pages, total_pages * 8 as total_kb, usage_count, Last_User_Used_date, @start_time as server_start_time from nonclustered_index_size s left join index_usage_stats u on s.object_id = u.object_id and s.index_id = u.index_id order by s.table_name, s.index_name