Which Indexes Are Missing? — 2025 Edition

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.