Missing-index suggestions

In one line: the missing-index DMVs (and the green hint in SSMS) tell you a query wanted an index. They don’t tell you which index to build. Suggestions overlap, ignore ORDER BY, and can include every column in the table.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/missing-index-suggestions on GitHub.

What you’ll see

  • Several suggestions for one table that all lead with the same column. In the demo, three of five led with CustomerID, each claiming 99.86% impact.
  • A suggested INCLUDE list that copies the table. In the demo, a 244.3 MB index on a 244.6 MB table.
  • More index space than table, if you build them all: 332 MB of indexes on a 245 MB table.

Why it happens

Each suggestion is recorded per query shape, in isolation. It knows nothing about the other suggestions, and nothing about the ORDER BY. It records the WHERE clause and the columns read, so a query that reads every column gets a suggestion that includes every column.

What to do

  • Treat each suggestion as evidence of need, then design by hand:
    • Merge overlapping suggestions. One index on (CustomerID, Status, OrderDate) INCLUDE (Amount) served four queries at 3 reads each, less than the three suggestions did.
    • Put ORDER BY columns in the key, after the equality columns. A TOP 50 ... ORDER BY OrderDate went from 82 reads plus a sort to 3 reads with no sort.
    • Refuse INCLUDE lists that copy the table, and accept a key lookup instead where the query touches few rows.
  • Measure the design, not each suggestion. Indexes interact: one query ignored its own suggestion when another index was there.
  • Capture the DMVs before you change anything. Creating any index on the table discards its suggestions, and a restart discards them all.

In the demo, three hand-designed indexes took 82 MB, against 332 MB for the five suggestions. They read the same or less for five of the six queries.

What doesn’t help

  • Building every suggestion. You pay for each one on every insert, update and delete.
  • Ranking by “impact”. It’s per query, in isolation: 99.86% each for three indexes that one index made redundant.

Check your server

-- Missing-index suggestions for this database, grouped so overlaps on the same table sit together.
SELECT OBJECT_NAME(d.object_id, d.database_id) AS table_name,
       d.equality_columns,
       d.inequality_columns,
       d.included_columns,
       s.user_seeks,
       s.avg_user_impact,
       s.last_user_seek
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.index_group_handle
WHERE d.database_id = DB_ID()
ORDER BY table_name, d.equality_columns;

See it happen

The missing-index-suggestions demo runs a six-query workload against a table with no nonclustered indexes and captures what the DMVs suggest. It then measures that design against three hand-designed indexes, query by query and megabyte by megabyte.

Related: key lookups and the tipping point · index fragmentation · pagination: OFFSET vs keyset

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights