Checking for unused indices across your database should also be a part of housekeeping. Usually i would schedule checking for unused indices on quarterly basis. Just to minimize surprises on a database suddenly timing out. The DMV for checking on usage and statistics is sys.dm_db_index_usage_stats. Here’s a script that i use most of the time and is on my quarterly maintenance plan then i just save the result to a table.
SELECT object_name(i.object_id) as tableName, i.name as indexName, s.object_id, s.user_seeks, s.user_scans
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id
AND i.index_id = s.index_id AND s.database_id = DB_ID()
WHERE objectproperty(i.object_id,’IsUserTable’) = 1
AND i.index_id > 0
AND i.is_Primary_Key = 0
AND i.is_unique_constraint = 0
AND i.is_unique = 0
The column for user_lookups and user_updates is also available in the DMV. You may add these columns for your additional checking. From the results, you can use your best judgement on which index is not used at all and you can proceed to drop. To drop an index from a table execute as below
DROP INDEX [indexnamehere] ON [tablenamehere]
From above result, check If the column user_seeks is high, this means index needs tuning up. Ideally, you would want to maintain around 5-10 indices per table. When you do drop an index it is also ideal to monitor performance of the database to see if there was any negative effect via the SQL Profiler. Just to make sure.