Query To Get List of Unused Indexs In SQL Server

Query To Get List of Unused Indexs In SQL Server

Generally Index used in SQL server to improve performance.Sometime we never use some Index's but it present in Database which can be one of the reason of performance degradation.
 Below query will give the list of all unused index in Database with DROP statement.
 
Query to get list of Unused Index's
 

SELECT o.name AS ObjectName , i.name AS IndexName

, i.index_id AS IndexID, dm_ius.user_seeks AS UserSeek

, dm_ius.user_scans AS UserScans, dm_ius.user_lookups AS UserLookups

, dm_ius.user_updates AS UserUpdates, p.TableRows

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'

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) TableRows, 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

GO

comments powered by Disqus