Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: IndexingDifficulty: IntermediateUpdated

Find Missing Indexes

Use SQL Server missing-index DMVs as a triage signal, rank candidates by estimated impact, and validate each recommendation before creating an index.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Rank missing-index candidates by estimated benefit
  5. 2. Show raw missing-index details for one table
  6. 3. Compare missing-index recommendations with existing indexes
  7. 4. Generate a reviewable CREATE INDEX template
  8. 5. Check write activity on the target table
  9. 6. Check when the current observation window started
  10. What the result means
  11. What to look for
  12. High score + high seek count
  13. Many recommendations for the same table
  14. Large INCLUDE lists
  15. Equality and inequality columns mixed together
  16. Recommendation appears once after a recent restart
  17. Production notes
  18. Missing-index DMVs are advisory
  19. Recommendations are transient
  20. Recommendations can overlap
  21. Missing-index DMVs do not tell you everything
  22. Permissions vary by version and platform
  23. Creating an index can block or consume significant resources
  24. Common mistakes
  25. 1. Turning every recommendation into a CREATE INDEX statement
  26. 2. Trusting avg_user_impact as a guaranteed percentage improvement
  27. 3. Ignoring existing indexes
  28. 4. Ignoring write cost
  29. 5. Creating very wide covering indexes
  30. 6. Using a short observation window
  31. 7. Assuming a missing index is the root cause
  32. Next step
  33. Related deep dives

Problem

SQL Server can record missing-index suggestions when the optimizer estimates that another index might reduce query cost.

These DMVs are useful, but they are recommendations, not design instructions.

The goal is to answer:

  • Which missing-index suggestions have the highest estimated value?
  • Which queries or tables are driving them?
  • Are multiple suggestions really asking for nearly the same index?
  • Does an existing index already cover most of the recommendation?
  • What is the likely read benefit versus write and storage cost?

Quick answer

Start by joining the missing-index DMVs and rank candidates using the optimizer’s estimated benefit signal.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,

    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,

    CAST(
        migs.user_seeks
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        AS decimal(18,2)
    ) AS estimated_improvement_score,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    estimated_improvement_score DESC;

Use this to prioritize review. Do not turn the output directly into CREATE INDEX statements without checking existing indexes, workload importance, key order, and write cost.

Code / commands

1. Rank missing-index candidates by estimated benefit

This is the best first-pass query.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,

    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,

    migs.avg_total_user_cost,
    migs.avg_user_impact,

    CAST(
        (migs.user_seeks + migs.user_scans)
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        AS decimal(18,2)
    ) AS estimated_improvement_score,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    estimated_improvement_score DESC;

The score is a triage heuristic based on the optimizer’s estimated cost and impact.

It is not a guaranteed performance gain.


2. Show raw missing-index details for one table

Use this after a table appears repeatedly in the ranked output.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    mid.index_handle,
    mid.database_id,
    mid.object_id,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    mid.statement
FROM sys.dm_db_missing_index_details AS mid
WHERE
    mid.database_id = DB_ID()
    AND mid.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
ORDER BY
    mid.index_handle;

If you see several similar rows, consolidate mentally before designing anything.

For example, these suggestions may overlap heavily:

(A, B) INCLUDE (C)
(A, B) INCLUDE (C, D)
(A, B, E) INCLUDE (C)

Creating all three is rarely the right answer.


3. Compare missing-index recommendations with existing indexes

Before creating anything, inspect the current index definitions.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    i.index_id,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.has_filter,
    i.filter_definition,
    ic.key_ordinal,
    ic.is_included_column,
    c.name AS column_name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE
    i.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
    AND i.is_hypothetical = 0
ORDER BY
    i.index_id,
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

You are looking for:

  • same leading key columns
  • near-duplicate INCLUDE sets
  • same filter logic
  • indexes that could potentially be widened instead of duplicated
  • uniqueness or constraint semantics that matter

4. Generate a reviewable CREATE INDEX template

This produces a draft, not an automatic deployment script.

SELECT TOP (50)
    'CREATE INDEX IX_'
    + REPLACE(
        REPLACE(
            OBJECT_NAME(mid.object_id, mid.database_id),
            ']',
            ''
        ),
        ' ',
        '_'
    )
    + '_MissingIndex'
    + CHAR(13) + CHAR(10)
    + 'ON '
    + QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id))
    + '.'
    + QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id))
    + ' ('
    + COALESCE(mid.equality_columns, '')
    + CASE
        WHEN mid.equality_columns IS NOT NULL
         AND mid.inequality_columns IS NOT NULL
            THEN ', '
        ELSE ''
      END
    + COALESCE(mid.inequality_columns, '')
    + ')'
    + CASE
        WHEN mid.included_columns IS NOT NULL
            THEN ' INCLUDE (' + mid.included_columns + ')'
        ELSE ''
      END
    + ';' AS create_index_draft,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    (
        migs.user_seeks
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
    ) DESC;

Review the generated SQL manually.

Typical changes before deployment may include:

  • choosing a real index name
  • removing redundant INCLUDE columns
  • changing key order
  • merging multiple similar recommendations
  • considering a filtered index
  • deciding not to create the index at all

5. Check write activity on the target table

A read-optimized index can hurt a heavily modified table.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE
    t.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
    AND i.is_hypothetical = 0
ORDER BY
    i.index_id;

If the table is write-heavy, every added index has a cost.


6. Check when the current observation window started

Missing-index recommendations are transient.

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

If the instance restarted recently, the missing-index DMVs represent only the workload since that restart.

That may be too short a window for a design decision.

What the result means

Field Why it matters
user_seeks Number of user seeks that could have benefited from the recommendation.
user_scans Number of user scans associated with the recommendation.
avg_total_user_cost Average optimizer-estimated cost of user queries associated with the group.
avg_user_impact Optimizer estimate of how much the index might reduce query cost, expressed as a percentage.
estimated_improvement_score Triage heuristic combining frequency, cost, and estimated impact. Not a guarantee.
equality_columns Columns referenced by equality predicates such as =.
inequality_columns Columns referenced by range-style predicates such as >, <, or BETWEEN.
included_columns Non-key columns the optimizer suggests covering in the index.
unique_compiles Number of compilations/recompilations that contributed to the recommendation group.
last_user_seek Last observed seek associated with the recommendation.

What to look for

High score + high seek count

This is a strong candidate for deeper review.

Validate:

  • the query is business-important
  • the workload is representative
  • no existing index already covers most of the pattern
  • the new index will not create excessive write cost

Many recommendations for the same table

This often means the raw DMV output needs consolidation.

Look for repeated leading columns and overlapping INCLUDE sets.

A smaller number of carefully designed indexes is usually better than implementing every recommendation literally.

Large INCLUDE lists

Be cautious.

A very wide covering index can:

  • consume significant storage
  • increase buffer-pool pressure
  • increase write cost
  • increase maintenance time

Cover only what has a defensible workload benefit.

Equality and inequality columns mixed together

A common starting point is:

  • equality columns first
  • inequality columns after

But do not treat the DMV’s text output as a perfect key-order design.

Column selectivity, ordering needs, join patterns, existing indexes, and workload shape still matter.

Recommendation appears once after a recent restart

Weak evidence.

Collect a longer observation window or validate through Query Store and execution plans.

Production notes

Missing-index DMVs are advisory

The optimizer records what it believes could help a specific optimization problem.

It does not consider your full index portfolio, write workload, storage budget, maintenance strategy, or every other query on the table.

Recommendations are transient

Missing-index information can be cleared by events such as SQL Server restart.

Do not treat the DMVs as durable history.

If you want to analyze trends, snapshot them periodically into your own monitoring store.

Recommendations can overlap

The same underlying workload can generate several similar index suggestions.

Blindly implementing each one can create index bloat.

Always compare:

  • key columns
  • key order
  • INCLUDE columns
  • filters
  • uniqueness
  • existing indexes
  • workload importance

Missing-index DMVs do not tell you everything

They do not replace:

  • execution-plan analysis
  • Query Store
  • index usage statistics
  • table size analysis
  • write-volume analysis
  • real workload testing

Permissions vary by version and platform

Access to missing-index DMVs depends on SQL Server version and platform.

On newer SQL Server versions, server-level performance-state permissions are used for broad DMV visibility; earlier versions commonly rely on VIEW SERVER STATE.

Azure SQL Database has its own scope and permission rules.

Creating an index can block or consume significant resources

On a large production table, index creation can require substantial:

  • CPU
  • I/O
  • transaction log
  • memory
  • tempdb
  • lock/metadata activity

Use the appropriate online/resumable options only where your SQL Server edition/version supports them and where they fit your operational plan.

Common mistakes

1. Turning every recommendation into a CREATE INDEX statement

This is the most common mistake.

Missing-index DMVs generate workload-specific suggestions, not a globally optimized index design.

2. Trusting avg_user_impact as a guaranteed percentage improvement

It is an optimizer estimate.

It does not mean the query will literally become 80% faster.

3. Ignoring existing indexes

A recommendation may be close to an index you already have.

Sometimes the right answer is:

  • modify an existing index
  • merge two indexes
  • adjust INCLUDE columns
  • do nothing

4. Ignoring write cost

Every nonclustered index must be maintained during relevant data changes.

A read benefit must justify that ongoing cost.

5. Creating very wide covering indexes

More INCLUDE columns can reduce lookups, but wide indexes can become expensive in storage, memory, writes, and maintenance.

6. Using a short observation window

A restart can make the DMVs look clean and focused simply because they contain only recent workload.

Do not redesign indexing from a few minutes or hours of data unless the incident itself justifies that narrow scope.

7. Assuming a missing index is the root cause

A slow query can also be caused by:

  • bad cardinality estimates
  • blocking
  • stale or inappropriate statistics
  • parameter sensitivity
  • memory pressure
  • poor query shape
  • tempdb pressure
  • server-wide resource contention

Treat missing-index evidence as one signal.

Next step

Use the recommendation pattern to decide what to investigate next.

  • High-value candidate + no similar existing index → inspect the execution plan and test a carefully designed index.
  • Several similar recommendations → consolidate them before creating anything.
  • Wide INCLUDE list → identify which columns truly need coverage.
  • Write-heavy table → weigh read benefit against update cost.
  • Recent SQL Server restart → collect more evidence before making structural changes.
  • Recommendation targets one problematic query → validate with Query Store and the actual execution plan.
  • Existing index is close → consider modifying/consolidating rather than adding another index.
  • No convincing missing-index candidates → continue broader performance troubleshooting instead of forcing an indexing fix.

← All cheat sheets