-- unused indexes SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName, OBJECT_NAME(I.OBJECT_ID) AS ObjectName, I.NAME AS IndexName FROM sys.indexes I WHERE -- only get indexes for user created tables OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 -- find all indexes that exists but are NOT used AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id -- limit our query only for the current db AND database_id = DB_ID()) AND I.index_id != 0 AND I.is_primary_key = 0 AND I.is_unique_constraint = 0 -- AND I.is_unique = 0 ORDER BY SchemaName, ObjectName, IndexName
-- rarely used indexes SELECT O.name AS object_name, I.name AS index_name, I.index_id AS index_id, DM_IUS.user_seeks AS user_seeks, DM_IUS.user_scans AS user_scans, DM_IUS.user_lookups AS user_lookups, DM_IUS.user_updates AS user_updates, P.table_rows as table_rows, DM_IUS.last_user_seek, DM_IUS.last_user_scan, DM_IUS.last_user_lookup, DM_IUS.last_user_update FROM sys.dm_db_index_usage_stats DM_IUS INNER JOIN sys.indexes I ON I.index_id = DM_IUS.index_id AND DM_IUS.OBJECT_ID = I.OBJECT_ID INNER JOIN sys.objects O ON DM_IUS.OBJECT_ID = O.OBJECT_ID INNER JOIN sys.schemas S ON O.schema_id = S.schema_id INNER JOIN (SELECT SUM(P.rows) table_rows, P.index_id, P.OBJECT_ID FROM sys.partitions P GROUP BY P.index_id, P.OBJECT_ID) P ON P.index_id = DM_IUS.index_id AND DM_IUS.OBJECT_ID = P.OBJECT_ID WHERE OBJECTPROPERTY(DM_IUS.OBJECT_ID,'IsUserTable') = 1 AND DM_IUS.database_id = DB_ID() AND I.type_desc = 'nonclustered' AND I.is_primary_key = 0 AND I.is_unique_constraint = 0 ORDER BY (DM_IUS.user_seeks + DM_IUS.user_scans + DM_IUS.user_lookups) ASC
-- used indexes stats SELECT DB_NAME(C.database_id) AS database_name, OBJECT_NAME(C.object_id, C.database_id) AS table_name, A.avg_user_impact * (A.user_seeks + A.user_scans) avg_estimated_impact, A.avg_total_user_cost * (A.user_seeks + A.user_scans) avg_estimated_total_user_cost, A.unique_compiles, A.user_seeks, A.last_user_seek, DATEPART(d, GETDATE() - A.last_user_seek) as last_user_seek_days_ago, A.user_scans, A.last_user_scan, DATEPART(d, GETDATE() - A.last_user_scan) as last_user_scan_days_ago, A.avg_total_user_cost, A.avg_user_impact, C.equality_columns, C.inequality_columns, C.included_columns FROM sys.dm_db_missing_index_group_stats AS A INNER JOIN sys.dm_db_missing_index_groups AS B ON A.group_handle = B.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS C ON B.index_handle = C.index_handle --WHERE --DATEPART(hh, last_user_seek) >= 6 AND DATEPART(hh, last_user_seek) <= 20 AND -- run in work hours --DATEPART(d, GETDATE() - A.last_user_seek) = 1 -- run today ORDER BY avg_estimated_total_user_cost DESC