SQL Server Missing Indexes Report: dm_db_missing_index

calendar Feb 28, 2026 / · 6 min read · sql server missing indexes performance dm db missing index details dm db missing index group stats index tuning dba scripts query optimization  ·
Share on: linkedin copy

SQL Server tracks index recommendations automatically as queries execute, storing them in the missing index DMVs. This T-SQL script queries those views to produce a ranked report of missing indexes, ordered by the estimated improvement they would provide, along with a ready-to-run CREATE INDEX statement for each recommendation.

Purpose and Overview

Every time SQL Server executes a query that would benefit from an index that does not exist, it records the recommendation in sys.dm_db_missing_index_details. Over time these recommendations accumulate and can reveal the highest-impact indexing changes you can make to improve query performance.

The data is reset when SQL Server restarts, so the longer your instance has been running, the more reliable the recommendations become. This script calculates an improvement_measure score — the product of estimated cost savings and usage frequency — to rank recommendations by their real-world impact rather than showing every suggestion with equal weight.

Use this report as a starting point for index tuning. Always review the generated CREATE INDEX statement in context before running it in production.

Code Breakdown

 1SELECT TOP 20
 2    DB_NAME(mid.database_id)              AS database_name,
 3    OBJECT_SCHEMA_NAME(mid.object_id,
 4        mid.database_id)                  AS schema_name,
 5    OBJECT_NAME(mid.object_id,
 6        mid.database_id)                  AS table_name,
 7    mid.equality_columns,
 8    mid.inequality_columns,
 9    mid.included_columns,
10    migs.user_seeks,
11    migs.user_scans,
12    migs.avg_total_user_cost             AS avg_query_cost,
13    migs.avg_user_impact                 AS avg_pct_benefit,
14    ROUND(
15        migs.avg_total_user_cost
16        * migs.avg_user_impact
17        * (migs.user_seeks + migs.user_scans),
18        2
19    )                                     AS improvement_measure,
20    'CREATE INDEX [IX_' +
21        OBJECT_NAME(mid.object_id, mid.database_id) +
22        '_' +
23        REPLACE(
24            ISNULL(mid.equality_columns, '') +
25            ISNULL('_' + mid.inequality_columns, ''),
26            ', ', '_'
27        ) +
28        '] ON ' +
29        OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) +
30        '.' +
31        OBJECT_NAME(mid.object_id, mid.database_id) +
32        ' (' +
33        ISNULL(mid.equality_columns, '') +
34        CASE
35            WHEN mid.inequality_columns IS NOT NULL
36                AND mid.equality_columns IS NOT NULL
37            THEN ', '
38            ELSE ''
39        END +
40        ISNULL(mid.inequality_columns, '') +
41        ')' +
42        ISNULL(
43            ' INCLUDE (' + mid.included_columns + ')',
44            ''
45        ) + ';'                           AS create_index_statement
46FROM
47    sys.dm_db_missing_index_details     AS mid
48    INNER JOIN sys.dm_db_missing_index_groups AS mig
49        ON mid.index_handle = mig.index_handle
50    INNER JOIN sys.dm_db_missing_index_group_stats AS migs
51        ON mig.index_group_handle = migs.group_handle
52WHERE
53    mid.database_id = DB_ID()
54ORDER BY
55    improvement_measure DESC;

The Three Missing Index DMVs

The missing index system uses three related views that must be joined together:

  • sys.dm_db_missing_index_details — one row per unique missing index recommendation, containing the table and column information
  • sys.dm_db_missing_index_groups — groups related index recommendations together (SQL Server may consolidate similar suggestions)
  • sys.dm_db_missing_index_group_stats — contains the usage statistics and cost estimates for each group

Calculating the Improvement Measure

The improvement_measure column is a composite score:

1improvement_measure = avg_total_user_cost × avg_user_impact × (user_seeks + user_scans)
  • avg_total_user_cost — the average cost of the queries that would benefit from this index
  • avg_user_impact — the estimated percentage improvement (0–100) if the index existed
  • user_seeks + user_scans — how many times queries needed this index since the last restart

A high score means an expensive query ran many times and would benefit significantly from the index. Low scores may not be worth creating.

Equality vs Inequality Columns

The recommendation splits index columns into two groups:

  • Equality columns — columns used in WHERE col = value predicates; these become the leading key columns of the index
  • Inequality columns — columns used in range predicates (>, <, BETWEEN, LIKE); these go after equality columns in the key

The CREATE INDEX statement in the output follows this ordering automatically.

Generating the CREATE INDEX Statement

The script builds a CREATE INDEX statement using string concatenation. This is provided as a starting point only — review the name, column order, and INCLUDE columns before running it. Consider whether the new index duplicates an existing one or whether an existing index could be modified instead.

Filtering to the Current Database

WHERE mid.database_id = DB_ID() limits results to the database you are connected to. Remove this filter and replace DB_NAME(mid.database_id) as the first column to see recommendations across all databases on the instance.

Key Benefits and Use Cases

  • Ranked recommendations — the improvement_measure score guides you to the highest-impact indexes first
  • Ready-to-run DDL — the generated CREATE INDEX statement reduces manual work
  • Post-deployment review — run after deploying new application code to see what new queries are requesting
  • Recurring maintenance — add to a weekly DBA checklist to catch emerging performance issues
  • Query tuning context — combine with execution plan analysis to confirm index recommendations match actual query patterns

Performance Considerations

This script reads only in-memory DMVs and has no meaningful overhead. Keep these points in mind when acting on the results:

  • Data resets on restart — missing index statistics are lost when SQL Server restarts. On instances that restart frequently, the data may not reflect long-term patterns
  • Do not create every recommended index — each index adds overhead to INSERT, UPDATE, and DELETE operations. Evaluate each recommendation against write workload impact
  • Check for overlapping indexes — before creating a new index, compare against existing indexes on the same table using sys.indexes and sys.index_columns. A small modification to an existing index is often better than a new one
  • Missing index DMVs have limits — SQL Server stores at most 500 missing index recommendations per database. On heavily-queried systems, low-priority recommendations may be pushed out
  • Use with execution plans — the DMVs show aggregate recommendations; execution plans show the recommendation for a specific query. Use both together for the best analysis

Practical Tips

  • Run during peak hours — the data is most representative when captured under normal production load
  • Save a baseline — insert the results into a history table weekly to track whether new missing indexes appear after deployments
  • Compare with index usage — join to sys.dm_db_index_usage_stats to see whether indexes you previously created from these recommendations are actually being used
  • Review the INCLUDE columns — the included_columns list covers columns referenced in the SELECT clause. Including too many columns increases index size; be selective
  • Consider filtered indexes — if a recommendation targets a specific subset of rows, a filtered index (WHERE status = 'Active') may be smaller and faster than a full table index

Conclusion

The missing index DMVs give you a built-in, continuously updated list of index recommendations ranked by real workload data. This script surfaces the top 20 highest-impact suggestions for the current database and generates the CREATE INDEX statement for each. Use it regularly as part of performance tuning and after application deployments to keep query performance optimized.

References

Posts in this series