Script to find Unused Indexes- SQL Server

Posted by

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, 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.


  • 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.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s