The ticket was titled "applied all missing index recommendations, performance improved." Performance had not improved. A well-meaning engineer had run the internet's favorite query against sys.dm_db_missing_index_details, generated CREATE INDEX statements for all forty-one suggestions above an arbitrary score, and deployed them on a Tuesday. Three weeks later the review found: write latency on the orders table roughly doubled, twelve of the new indexes overlapping each other so completely that the optimizer used whichever it found first, and three indexes that had never been read a single time — their "missing" moments had been one-off admin queries that would never run again. The rollback script took longer to write than the deployment had.
The missing index DMVs are not broken. They are a hint engine answering a much narrower question than "what indexes should this database have," and treating their output as a design document is the misuse this article is about. What they actually measure, the limits baked into them, and the workflow that turns them from a footgun into a genuinely useful shortlist — SQL Server 2016 through 2022.
What do the missing index DMVs actually measure?
They record moments when the query optimizer, while compiling a plan, concluded that an index it wanted did not exist — and they accumulate those moments with rough benefit estimates. Four views make up the feature: sys.dm_db_missing_index_details carries the suggestion itself (the table, equality_columns, inequality_columns, included_columns, and the statement it came from), sys.dm_db_missing_index_groups links suggestions to statistics, sys.dm_db_missing_index_group_stats accumulates seeks, scans, and cost figures, and sys.dm_db_missing_index_group_stats_query on newer builds ties suggestions back to query hashes. The familiar "improvement measure" — avg_total_user_cost times avg_user_impact times (user_seeks + user_scans) — is a ranking heuristic built from compile-time cost estimates, not measured runtime savings. That distinction is the root of every misuse: these are hypotheses the optimizer formed while planning, not observations of what actually ran slowly.
SELECT TOP 20
d.statement,
d.equality_columns,
d.inequality_columns,
d.included_columns,
s.user_seeks,
s.avg_total_user_cost,
s.avg_user_impact,
s.avg_total_user_cost * s.avg_user_impact
* (s.user_seeks + s.user_scans) AS improvement_measure
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 s
ON s.group_handle = g.group_handle
WHERE d.database_id = DB_ID()
ORDER BY improvement_measure DESC;What are the hard limits baked into the feature?
The feature caps at 600 missing index groups per instance — once the cap is hit, new suggestions stop being tracked at all, with no error and no warning — and it silently evicts or resets data, so the DMV is a sample, not an inventory. The reset behavior is the operational trap: statistics for a suggestion vanish when metadata changes on the table, including when you create an index that satisfies or overlaps it, and the counters reset at every instance restart. A weekly report built on these DMVs is reporting on a window you cannot define. Beyond the caps, the suggestion engine itself is deliberately shallow: it proposes equality columns before inequality columns but does not reason about the best key order within those sets, it never compares its suggestion against indexes that already exist to notice a two-column addition would do, it has no opinion on sort order, filtered indexes, or compression, and it prices only the read benefit — the write cost of maintaining another index appears nowhere in the improvement measure.
One more limit that matters in practice: suggestions come from plan compilation, so a query whose plan never compiled on this instance — or one that came from an ORM generating slightly different SQL each time — may never register, while a single compile of a rare admin query registers with a cost figure that looks identical to a hot query's. The DMV cannot tell you which is which. Only execution data can.
How do I turn suggestions into a shortlist instead of forty-one indexes?
Group, merge, verify — in that order, and never skip the merge step. Grouping means collapsing suggestions on the same table that share leading equality columns: five suggestions on (CustomerId, OrderDate) with different included columns are one index with a union of the includes, not five indexes. Merging means comparing each candidate against the existing index list — sys.indexes with sys.index_columns — and asking whether an existing index extended by one key column or a few includes covers the suggestion with a fraction of the maintenance cost; in my experience a third to a half of all suggestions die at this step on any mature database. Verifying means checking the suggestion against real execution history before creating anything. Query Store is the tool: find the query text named in the suggestion's statement column, confirm it actually runs frequently and is actually slow, and confirm the plan shape the index would serve:
SELECT TOP 10 q.query_id,
qt.query_sql_text,
rs.count_executions,
rs.avg_duration / 1000.0 AS avg_ms,
rs.avg_logical_io_reads
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE N'%FROM dbo.Orders WHERE CustomerId%'
ORDER BY rs.count_executions * rs.avg_duration DESC;The discipline mirrors the one from my Query Store regression hunting notes: rank by total cost — executions times duration — not by the ratio, because a suggestion serving a twice-a-day report should never outrank one serving eight thousand executions an hour, whatever the improvement measure says.
When is creating the suggested index verbatim actually fine?
It is fine in exactly one situation: a young database with a thin, well-understood index layer, a suggestion whose key columns match a query you recognize as hot, and no existing index within merging distance. That describes week two of a greenfield application, not year four of a production system. Everywhere else the verbatim path fails the same way it failed in the opening story: the DMV does not know that the (CustomerId, OrderDate) suggestion and the (CustomerId, OrderDate, Status) suggestion and the existing (CustomerId, OrderDate) index are one decision, and it does not know that the orders table already carries eleven indexes and every insert maintains all of them. Write amplification from redundant indexing is the quiet tax here — the same trade the index maintenance reality check makes from the fragmentation side — and it lands on every INSERT, UPDATE, and DELETE, forever, to pay for a read benefit that was only ever a compile-time estimate.
What should the standing process look like?
A monthly review, not a deployment pipeline. Export the DMV output with timestamps so resets are visible, group and merge against the live index inventory, verify survivors against Query Store execution data, deploy at most a handful at a time, and then — the step almost everyone skips — check sys.dm_db_index_usage_stats a month later and remove what is not being read. An index created from a suggestion that turns out to be unused is not a sunk cost to protect; it is write overhead to delete. The review cadence also protects against the opposite failure: suggestions that were right all along but drowned below forty redundant ones. The feature earns its keep as the first stage of a funnel with a human and real execution data downstream of it. Treat it as the last stage, and you get forty-one indexes and a write latency problem with a change ticket attached.
Watching index suggestions with MonPG when SQL Server support lands
The signals worth graphing here are the missing index group count against its 600 cap, top suggestions trended week over week so resets are visible, and read-versus-write ratios per index so the cost side of the ledger is as visible as the benefit side. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the two queries in this article run monthly into a table you keep, plus dm_db_index_usage_stats on the same schedule, are the whole workflow — and they are enough to make the next forty-one-index deploy script die in review.