Fifteen years ago, I wrote a little post about the missing index DMVs. Six major SQL Server releases later, it's time for an update.
The Old Query: Still Kicking
Here's the gist of what we had back then:
SELECT
avg_user_impact average_improvement_percentage,
avg_total_user_cost average_cost_of_query_without_missing_index,
'CREATE INDEX idx_' + [statement] +
ISNULL(equality_columns, '_') +
ISNULL(inequality_columns, '_') + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, ' ') +
ISNULL(inequality_columns, ' ') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b
ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c
ON b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 40
This still runs fine. If you're on SQL Server 2005-2017, or you just need a quick-and-dirty list, it gets the job done.
But it has a couple blind spots:
- You see suggestions, but not which queries need them
- No idea if the suggestion is from current or stale statistics
What's New Since 2010
The Big One: sys.dm_db_missing_index_group_stats_query
Introduced in v2019, this links missing index suggestions to actual queries via query_hash. No more guessing which query is crying for help — you can see it directly.
Freshness Check: last_user_seek and last_user_scan
The freshness columns -- last_user_seek and last_user_scan -- were always there, but I didn't include them in my 2010 post. I should have. They tell you exactly when the optimizer last wanted that index. A suggested index based on current stats is very different from one using old/outdated statistics.
Cleaner T-SQL
We've got CONCAT, CONCAT_WS, and FORMAT now. No more nested ISNULL chains.
The 2025 Query
/*
Missing Index Analysis
sqlfingers.com | 2025
Requires: SQL Server 2019+
*/
SELECT
ROW_NUMBER() OVER (
ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) DESC) priority,
CAST(s.avg_user_impact AS INT) [impact_%],
FORMAT(s.user_seeks, 'N0') seeks,
FORMAT(s.user_scans, 'N0') scans,
FORMAT(s.last_user_seek, 'yyyy-MM-dd HH:mm') last_seek,
DB_NAME(d.database_id) db,
OBJECT_SCHEMA_NAME(d.object_id, d.database_id) [schema],
OBJECT_NAME(d.object_id, d.database_id) [table],
d.equality_columns,
d.inequality_columns,
d.included_columns,
CONCAT(
'CREATE NONCLUSTERED INDEX [IX_',
OBJECT_NAME(d.object_id, d.database_id), '_',
REPLACE(REPLACE(REPLACE(
CONCAT_WS('_', d.equality_columns, d.inequality_columns),
'[', ''), ']', ''), ', ', '_'),
'] ON [', OBJECT_SCHEMA_NAME(d.object_id, d.database_id), '].[',
OBJECT_NAME(d.object_id, d.database_id), '] (',
CONCAT_WS(', ', d.equality_columns, d.inequality_columns), ')',
IIF(d.included_columns IS NOT NULL,
CONCAT(' INCLUDE (', d.included_columns, ')'), ''),
';'
) create_statement
FROM sys.dm_db_missing_index_details d INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) DESC;
Bonus: Find the Query in Need
This one is fun. We can use sys.dm_db_missing_index_group_stats_query to see exactly which query wants the index:
SELECT
CAST(sq.avg_user_impact AS INT) [impact_%],
OBJECT_NAME(d.object_id, d.database_id) [table],
d.equality_columns,
d.inequality_columns,
LEFT(t.query_sql_text, 150) query_snippet
FROM sys.dm_db_missing_index_details d INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle INNER JOIN sys.dm_db_missing_index_group_stats_query sq
ON g.index_group_handle = sq.group_handle INNER JOIN sys.query_store_query q
ON sq.query_hash = q.query_hash INNER JOIN sys.query_store_query_text t
ON q.query_text_id = t.query_text_id
WHERE d.database_id = DB_ID()
ORDER BY sq.avg_user_impact DESC;
Now we're not just seeing such-n-such index on tablename. We get the actual SELECT statement that is suffering. Pure gold.
Quick Caveats
- DMV stats reset on restart — let your server run a full business cycle before trusting these numbers
- Not every suggestion is gold — the optimizer doesn't consider write overhead or maintenance cost. You must always fully review and test the recommended indexes rather than trusting them blindly.
- Look for overlaps — if you see 3 suggestions for the same table, you might consolidate into 1
More to Read:
Original 2010 Post: Which indexes are missing?
Microsoft Docs: sys.dm_db_missing_index_group_stats_query
Happy indexing.