List Unused SQL Server Indexes

sqlserver2014

The query below will give the list of all unused indexes in the database, including the DROP statement.

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s