Locate missing indices

Locate missing indices

 SELECT * FROM sys.dm_db_missing_index_groups G INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle INNER JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC ORDER BY D.index_handle ,[statement] 

Locate unused indices

 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 OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 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 AND database_id = DB_ID() ) ORDER BY SchemaName ,ObjectName ,IndexName