SQL Server: missing index suggestions for one table
List the optimizer's missing index suggestions for one table, with the creation script and an improvement measure.
This query reads the missing index suggestions that the optimizer records in the sys.dm_db_missing_index_* DMVs. For each suggestion, it generates a CREATE INDEX statement and computes an improvement measure (estimated cost x impact x number of seeks and scans). It only keeps the suggestions whose measure is above 10 and that concern a table whose name contains YourTableName.
Query
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
AND mid.statement LIKE '%YourTableName%'
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Notes
- The suggestions are optimizer recommendations, not certainties: check the execution plans of the affected queries before creating an index.
- Before creating an index, check that an existing index does not already cover the suggestion.
- The suggestions are lost when the instance restarts.
- Replace
YourTableNamewith the table to analyze, or remove the condition to cover all tables.