Browse cheat sheets · SQL Server
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
- Problem
- Quick answer
- Code / commands
- 1. Rank missing-index candidates by estimated benefit
- 2. Show raw missing-index details for one table
- 3. Compare missing-index recommendations with existing indexes
- 4. Generate a reviewable CREATE INDEX template
- 5. Check write activity on the target table
- 6. Check when the current observation window started
- What the result means
- What to look for
- High score + high seek count
- Many recommendations for the same table
- Large INCLUDE lists
- Equality and inequality columns mixed together
- Recommendation appears once after a recent restart
- Production notes
- Missing-index DMVs are advisory
- Recommendations are transient
- Recommendations can overlap
- Missing-index DMVs do not tell you everything
- Permissions vary by version and platform
- Creating an index can block or consume significant resources
- Common mistakes
- 1. Turning every recommendation into a CREATE INDEX statement
- 2. Trusting avg_user_impact as a guaranteed percentage improvement
- 3. Ignoring existing indexes
- 4. Ignoring write cost
- 5. Creating very wide covering indexes
- 6. Using a short observation window
- 7. Assuming a missing index is the root cause
- Next step
- 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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.