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
