The missing index list looks like a ready-made deployment plan. Missing index suggestions are optimizer clues gathered from query compilations, not finished index designs. Three checks keep one helpful suggestion from becoming several overlapping indexes that slow every write.
Read Missing Index Suggestions With Their Context
The missing index DMVs record equality columns, inequality columns, included columns, and estimated benefit. They do not fully account for index maintenance, storage, or every existing access path. Their contents are transient and can disappear after a restart. I use missing index suggestions to find expensive queries worth examining, not to generate CREATE INDEX commands automatically.
Join details to groups and group statistics for the current database. The query below shows both the suggested column lists and the number of user seeks and scans that contributed. A high impact figure is an estimate from the optimizer, not a measured improvement after building the index.
SELECT d.statement, d.equality_columns,
d.inequality_columns, d.included_columns,
gs.user_seeks, gs.user_scans,
gs.avg_total_user_cost, gs.avg_user_impact,
g.index_group_handle
FROM sys.dm_db_missing_index_details AS d
JOIN sys.dm_db_missing_index_groups AS g
ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats AS gs
ON gs.group_handle = g.index_group_handle
WHERE d.database_id = DB_ID()
ORDER BY gs.avg_total_user_cost * gs.avg_user_impact
* (gs.user_seeks + gs.user_scans) DESC;The statement column contains the table name, not the full SQL statement. For that, use the query-specific view on SQL Server 2019 and later, or Query Store and plan XML. Which actual query asked for the index, and how frequently does that query matter to users?
Find the Query That Asked
sys.dm_db_missing_index_group_stats_query can connect a group to the last SQL handle and query hash. More than one query can request the same group. The query below shows recent text for one group, with the group handle supplied from the first query. A cached batch can contain several statements, so inspect offsets or Query Store before attributing the suggestion to one line.
DECLARE @GroupHandle int = 0;
SELECT q.group_handle, q.query_hash,
q.user_seeks, q.user_scans,
t.text AS last_batch_text
FROM sys.dm_db_missing_index_group_stats_query AS q
OUTER APPLY sys.dm_exec_sql_text(q.last_sql_handle) AS t
WHERE q.group_handle = @GroupHandle;Replace zero with the target group handle. This DMV is available on SQL Server 2019 and later. Its counters are not a persistent audit log. If the row is gone, Query Store can still hold query history. I compare the suggested index to the actual plan, especially the first costly scan or lookup. Sometimes a query rewrite removes the need for another index.
Check One: Is an Existing Index Close?
List indexes on the suggested table, their key order, included columns, filter, and size. A suggestion for (CustomerID, OrderDate) can overlap a current index on (CustomerID, OrderDate, Status) that needs only one included column. Extending or replacing an index can be cheaper than creating a duplicate. The following catalog query lists keys and included columns for one table.
SELECT i.name AS index_name, ic.key_ordinal,
ic.is_included_column, c.name AS column_name
FROM sys.indexes AS i
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id
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(N'dbo.Orders')
AND i.index_id > 0
ORDER BY i.name, ic.is_included_column, ic.key_ordinal;The query is an inventory, not a complete index definition. Check filters, compression, sort direction, and constraints separately. Do not drop an existing index because the suggestion looks similar. Other queries can depend on it. Compare usage, write volume, and the full workload before consolidating.
Check Two: Does the Key Order Fit?
The DMV separates equality and inequality columns, but it does not hand you the final order among them. Put equality predicates where they form useful leading keys for important queries, then consider range and ordering requirements. Look at actual Seek Predicates and residual Predicates in the current plan. A column that appears in an equality list for one query can be a range column in another.
I test candidate key orders in a nonproduction copy with representative parameter values. Logical reads and plan shape matter more than the generated suggestion text. One index can serve several queries if its leading keys match their predicates. Another can be perfect for one rare report and costly for every insert. The workload decides.
A candidate with equality keys followed by a date range can support a common search well. Reverse the keys without checking the predicates, and the plan can read a broad date range before filtering customer. If two equality columns appear together, use their distribution and other workload needs to decide their order. The DMV list does not express every ordering requirement or parameter pattern. Capture the actual query and test it.
Check Three: Is the INCLUDE List Worth Its Size?
Included columns can avoid key lookups, but a suggestion built for SELECT * can ask for almost every column. That makes a large index with more pages, more write work, and longer maintenance. Ask whether the application needs every projected column. If the query can return a narrower result, fix that first when possible.
Estimate index size on a test copy and measure insert, update, and delete overhead. Also check whether a filtered index could cover the important slice with fewer rows. Missing index suggestions do not design filters for you. A large included payload can make a seek appear cheap in the plan while making the storage cost very real.
Check the estimated width of each included column and how frequently it changes. A wide string can dominate leaf pages even if the key is narrow. Updates to included values still maintain the index. If the query returns ten columns but only two are needed by the caller, trimming the projection can make the current index sufficient. That is an application change worth measuring, not a reason to add ten more included columns by default.
Test Missing Index Suggestions and Keep the Numbers
Build one candidate in a safe test environment, update relevant statistics, and compare Query Store runtime metrics or repeated test executions. Measure reads, CPU, duration, memory grant, and write workload. Keep the old plan and index inventory so the result can be reviewed. If the gain is small, do not keep a large index simply because a DMV once suggested it.
I close the request only when I can say which query improved, by how much under a representative workload, and what the index costs to maintain. The three checks are simple, but they prevent an index collection from becoming a museum of optimizer hints. A suggestion is the beginning of the design review. Also note the cache lifetime. A high seek count after months of uptime is different from the same count after one hour. Pair the DMV snapshot with server start time and a current Query Store window. That prevents an old estimated benefit from outranking a new, measured workload.
Related reading on this blog: Validating AI-Generated Index Recommendations and Functions and Missing Indexes: SQL in Sixty Seconds 204.
A missing-index suggestion is not a deployment order, it is a candidate for a workload test.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
