tangdf

-- Identifying Unused Indexes in SQL Server 
SELECT   OBJECT_NAME(s.[object_id]) AS [Table Name] ,
         i.name AS [Index Name] ,
         i.fill_factor ,
         user_updates AS [Total Writes] ,
         user_seeks + user_scans + user_lookups AS [Total Reads] ,
         user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM     sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
         INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id]
                                                        AND i.index_id = s.index_id

tangdf

SELECT   DISTINCT CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage] ,
         migs.last_user_seek ,
         mid.[statement] AS [Database.Schema.Table] ,
         mid.equality_columns ,
         mid.inequality_columns ,
         mid.included_columns ,
         migs.unique_compiles ,
         migs.user_seeks ,
         migs.avg_total_user_cost ,
         migs.avg_user_impact ,