I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.
Are you 100% safe to drop these indexes ? or could they be used by the optimizer for say inserts or Deletes and would not register any stats in the DMV ?? if so, how can one tell they are 100% safe to remove?
I have had the server running for 2 months so am certain I have covered our monthly workload cycle.
SELECT o.name , indexname=i.name , i.index_id , reads=user_seeks + user_scans + user_lookups , writes = user_updates , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) , CASE WHEN s.user_updates < 1 THEN 100 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates END AS reads_per_write , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement' FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id INNER JOIN sys.objects o on s.object_id = o.object_id INNER JOIN sys.schemas c on o.schema_id = c.schema_id WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND s.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 2000 ORDER BY name, reads