As most of you know, an index can improve the performance of a query most of the time; internally sql server has to do a lot of work to maintain those indexes. When I started a new job, I came to see a lot of indexes were created for some of our production tables. So I decided to find a script that returns the indexes that’s not in use.
Note : it uses the dmv’s so, this is not a pretty accurate query. But you can use this as a start and apply common sense before running the drop index statement.
SELECT DB_NAME() AS database_name ,
S.name AS [schema_name] ,
O.name AS [object_name] ,
C.name AS column_name ,
I.name AS index_name ,
( CASE WHEN I.is_disabled = 1 THEN 'Yes'
ELSE 'No'
END ) AS [disabled] ,
( CASE WHEN I.is_hypothetical = 1 THEN 'Yes'
ELSE 'No'
END ) AS hypothetical ,
rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id
AND p.object_id = i.object_id GROUP BY p.index_id, p.OBJECT_ID),
N'USE ' + DB_NAME() + N'; DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) +
'.' + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) AS 'drop statement'
FROM [sys].[indexes] I
INNER JOIN [sys].[objects] O ON O.[object_id] = I.[object_id]
AND O.[type] = 'U'
AND O.is_ms_shipped = 0
AND O.name <> 'sysdiagrams'
INNER JOIN [sys].[tables] T ON T.[object_id] = I.[object_id]
INNER JOIN [sys].[schemas] S ON S.[schema_id] = T.[schema_id]
INNER JOIN [sys].[index_columns] IC ON IC.[object_id] = I.[object_id]
AND IC.index_id = I.index_id
INNER JOIN [sys].[columns] C ON C.[object_id] = IC.[object_id]
AND C.column_id = IC.column_id
WHERE I.[type] > 0
AND I.is_primary_key = 0
AND I.is_unique_constraint = 0
AND NOT EXISTS ( SELECT *
FROM [sys].[index_columns] XIC
INNER JOIN [sys].[foreign_key_columns] FKC ON FKC.parent_object_id = XIC.[object_id]
AND FKC.parent_column_id = XIC.column_id
WHERE XIC.[object_id] = I.[object_id]
AND XIC.index_id = I.index_id )
AND NOT EXISTS ( SELECT *
FROM [master].[sys].[dm_db_index_usage_stats] IUS
WHERE IUS.database_id = DB_ID(DB_NAME())
AND IUS.[object_id] = I.[object_id]
AND IUS.index_id = I.index_id )
