Run the below script on a database where you want to find the missing indexes.
DECLARE @sql varchar(2000) ,@DatabaseName varchar(20)
SET @DatabaseName = 'database_name' /*Change the database name to your database*/
SET @sql = 'USE ' + @DatabaseName
SET @sql = @sql + '; SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID AND (si.is_disabled = 0 AND si.is_primary_key=0)
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID(''' + @DatabaseName + ''')
AND (User_Seeks = 0 AND user_scans=0 and user_lookups=0)
AND si.type=2 AND si.is_unique = 0 AND si.is_primary_key = 0 AND is_disabled = 0
ORDER BY sis.user_updates desc'
EXEC (@sql)
In the above Fig. Check for User_seeks, User_scans, User_lookups,User_updates. Then Watch for more number of user_updates than user_seeks and User_scans then that index would be an unused Index.
This is a quick way of findings but you all need to collect the results from this query and save it in your folder and repeat the step over the three different weeks.
From the collected results analyze how the indexes are working in each week and then figure it out the unused indexes.
Tips:
- Never delete the index that is unused untill you are sure about it, diable it first and see the performance of the queries.
- Make sure you are not deleting the culstered index that is going to drop all other non-clustered indexes on a table.
