Finding Missing Indexes to be added and Unused Indexes to be dropped

A few weeks ago I helped a client on performance tuning on their production SQL Server database. I provided them a bunch of scripts to do a performance health check. Finally we solved the database slowness problem by adding missing indexes and dropping unused indexes. In fact, I found most developers and software vendors don't bother to do index tuning on database or even they don't have such knowledge and skills. In this monthly blog post I would like to share you the script that I use to discover missing indexes and unused indexes. For missing indexes to be added, there may be some duplicated suggestions that you should merge them into one. Following the principles listed in my another article Indexing Guidelines, especially on the column ordering.

-- Top 10 Missing Indexes

SELECT TOP 10

DB_NAME(dm_mid.database_id) AS DatabaseName,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 

+ CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 

IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

AND OBJECTPROPERTY(dm_mid.[object_id], 'IsMsShipped') = 0

ORDER BY Avg_Estimated_Impact DESC

GO

-- Unused Indexes

SELECT

DB_NAME(dm_ius.database_id) AS DatabaseName,

o.[name] AS ObjectName,

i.[name] AS IndexName,

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 AS dm_ius

JOIN sys.indexes AS i ON i.index_id = dm_ius.index_id AND dm_ius.[object_id] = i.[object_id]

JOIN sys.objects AS o ON dm_ius.[object_id] = o.[object_id]

JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

JOIN (SELECT SUM([rows]) AS TableRows, index_id, [object_id] FROM sys.partitions GROUP BY index_id, [object_id]) AS 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

AND (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) = 0

ORDER BY TableRows DESC

GO