SQL Server missing index DMVs: how to use them without over-indexing

What the columns mean:

• equality_columns: columns compared with =.
• inequality_columns: columns compared with anything else (>, <, BETWEEN, <>).
• included_columns: columns the query selects or uses that would make the index covering.
• user_seeks / user_scans: how many query executions could have used the index. This counts executions, so one statement in a loop can produce a huge number.
• avg_total_user_cost: the average estimated cost of the queries that asked for it, in the optimizer's cost units.
• avg_user_impact: the estimated percentage by which that cost would fall.
• unique_compiles: how many compilations asked for it. A low number with high seeks usually means one query run very often.

improvement_measure is only a sort key. It multiplies three estimates together, so use it to rank suggestions and never as a prediction of what you will gain.

Check how much history you are looking at

The DMVs live in memory. They are emptied when the instance restarts or fails over, or when the database goes offline. The entries for a table are also dropped when that table's metadata changes, which includes adding or dropping a column and creating an index on it. An ALTER INDEX operation on the table, such as a rebuild in a nightly maintenance job, clears them too.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

If the server restarted yesterday, you are looking at one day of workload. Month-end reports, weekly batch jobs and Monday morning traffic are not in there. Wait for a full business cycle before making decisions, and save a copy of the output on a schedule if your servers are patched often.

The reverse also applies. After you create one index on a table, the other suggestions for that table vanish. That does not mean they were solved. Record the list before you change anything.

Why you can't take the suggestions at face value

Microsoft's documentation is direct about this: the feature is not intended to fine-tune an indexing configuration. The specific limits are the ones that hurt in production.

It looks at one query at a time. Three queries that filter the same table slightly differently produce three suggestions. Nothing merges them into the one index that would serve all three.

It ignores the indexes you already have. If IX_Orders_CustomerID exists on (CustomerID) INCLUDE (OrderDate), a query that also needs TotalDue produces a suggestion for a brand new index on (CustomerID) INCLUDE (OrderDate, TotalDue). Creating it gives you two indexes that do nearly the same job. The right fix is to add the column to the existing one.

It doesn't choose the key column order. Columns are listed in the order they appear in the table, grouped into equality and inequality. The order of the key columns decides which queries can seek on the index, and that decision is left to you.

It ignores the cost of writes and storage. Every nonclustered index has to be maintained by each INSERT and DELETE, and by each UPDATE that touches its columns. It also takes space on disk, in the buffer pool, in backups and on every replica. None of this is weighed against the estimated benefit.

The included column lists get very wide. A SELECT * or an ORM that loads whole entities produces a suggestion that includes nearly every column in the table. That index is a second copy of the table.

It only knows one kind of index. Suggestions are always plain nonclustered rowstore indexes. You will never be offered a filtered index, a unique index, a columnstore index or a change to the clustered index, even when one of those is the better answer.

The numbers are estimates. avg_user_impact comes from the optimizer's cost model, not from measurement. A 99% improvement on a query that costs 0.05 is worth nothing. Costs are also less accurate for queries that use only inequality predicates.

The list is incomplete. At most 600 missing index groups are tracked, and queries with trivial plans are never considered.

Find the queries behind a suggestion

You can't judge a suggestion without seeing the query that asked for it. On SQL Server 2019 and later, the per-query DMV gives you that directly:

SELECT TOP (25)
       d.statement AS table_name,
       d.equality_columns, d.inequality_columns, d.included_columns,
       q.user_seeks, q.avg_total_user_cost, q.avg_user_impact,
       q.query_hash,
       SUBSTRING(t.text, q.last_statement_start_offset / 2 + 1,
         (CASE q.last_statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
               ELSE q.last_statement_end_offset END - q.last_statement_start_offset) / 2 + 1) AS last_statement
FROM sys.dm_db_missing_index_group_stats_query AS q
JOIN sys.dm_db_missing_index_groups AS g ON g.index_group_handle = q.group_handle
JOIN sys.dm_db_missing_index_details AS d ON d.index_handle = g.index_handle
OUTER APPLY sys.dm_exec_sql_text(q.last_sql_handle) AS t
WHERE d.database_id = DB_ID()
ORDER BY q.avg_total_user_cost * q.avg_user_impact * q.user_seeks DESC;

last_statement is NULL when the plan has already left the cache. In that case use query_hash to find the query in Query Store (sys.query_store_query.query_hash).

On older versions, search the plan cache for plans that carry a missing index request for the table:

SELECT TOP (20) qs.execution_count, qs.total_logical_reads, t.text, p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE p.query_plan.exist('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                          //sp:MissingIndex[@Table="[Orders]"]') = 1
ORDER BY qs.total_logical_reads DESC;

Parsing every cached plan as XML is expensive. On a server with a large plan cache, run it outside peak hours.

Once you have the query, look at it before you look at the index. Many suggestions go away when the query is fixed: a SELECT * that only needs four columns, a function wrapped around the filtered column, or a parameter whose type doesn't match the column.

Compare with the indexes that already exist

List what the table has now (SQL Server 2017 and later, for STRING_AGG):

SELECT i.name, i.type_desc, i.is_unique, i.filter_definition,
       STRING_AGG(CASE WHEN ic.is_included_column = 0 THEN c.name END, ', ')
         WITHIN GROUP (ORDER BY ic.key_ordinal, ic.index_column_id) AS key_columns,
       STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name END, ', ')
         WITHIN GROUP (ORDER BY ic.key_ordinal, ic.index_column_id) AS included_columns
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'Sales.Orders')
GROUP BY i.index_id, i.name, i.type_desc, i.is_unique, i.filter_definition
ORDER BY i.index_id;

Then check how those indexes are used and what they cost in writes:

SELECT i.name, us.user_seeks, us.user_scans, us.user_lookups, us.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
       ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID(N'Sales.Orders');

Three things to look for:

1. An existing index with the same leading key column. Add the missing included column, or append a key column, instead of creating a new index.
2. A high user_updates count across the table. The table is write-heavy, so each extra index is expensive. Set the bar higher.
3. Existing indexes with updates and no seeks or scans. Dropping one of those may pay for the index you are about to add.

A worked example: three suggestions, one index

Suppose the DMVs return these three rows for Sales.Orders:

Following them literally creates three indexes that all start with CustomerID. Look at them together instead:

• All three seek on CustomerID, so it is the first key column.
• The busiest query then filters a range on OrderDate, so that goes second. Equality columns go before inequality columns in the key.
• TotalDue and Status are only read, so they are included columns.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
ON Sales.Orders (CustomerID, OrderDate)
INCLUDE (TotalDue, Status)
WITH (ONLINE = ON);   -- Enterprise edition and Azure SQL only; remove it elsewhere

This index fully serves the first two queries. The third can seek on CustomerID and then filter Status from the included column without a key lookup. For a customer with a few hundred orders that costs almost nothing extra, and at 1,800 executions it doesn't justify a second index. If an existing index already started with CustomerID, you would change that index instead of creating this one.

General rules for turning suggestions into an index definition:

• Put equality columns first and inequality columns after them.
• Among the equality columns, put first the ones that the most queries filter on, so one index serves several queries.
• Keep the included list to the columns the queries really need. If the suggestion includes most of the table, fix the query's column list or accept the key lookup.
• If the queries always filter on the same fixed value (WHERE Status = 'Open', WHERE DeletedAt IS NULL), consider a filtered index. The DMVs will never suggest one.

Measure before and after

Test on a restored copy of production first, with the real query and realistic parameter values:

SET STATISTICS IO, TIME ON;

SELECT OrderDate, TotalDue
FROM Sales.Orders
WHERE CustomerID = 48213 AND OrderDate >= '2026-01-01';

Record logical reads and CPU time, create the index, and run it again. Check the actual plan to confirm the new index is used with a seek. Then run the table's main write statements and compare their logical reads too, because that is where the cost of the index shows up.

After deploying, look at sys.dm_db_index_usage_stats again a week or two later. An index with a growing user_updates count and no seeks should be dropped. The optimizer changed its mind, or the query was never as frequent as the estimate suggested.

Common mistakes

• Scripting CREATE INDEX for every row. This is how tables end up with twenty overlapping indexes, slow writes and a database twice the size it should be.
• Sorting by avg_user_impact alone. High impact with five seeks is noise. Rank by the combined measure, then read the query.
• Deciding right after a restart or a failover. The list reflects only the workload since then.
• Copying the column order from the DMV. It reflects the table definition, not what makes a good key.
• Creating a near-duplicate of an existing index when one extra included column on the existing index would do.
• Assuming an empty list means the indexing is right. Trivial plans, the 600-group limit and metadata changes all hide requests, and no suggestion ever tells you an index should be removed.
• Building a large index on a busy table in business hours without ONLINE = ON. An offline build blocks writes to the table until it finishes.

Azure SQL Database can act on these same suggestions automatically (the CREATE INDEX automatic tuning option). It is safer than a script because it measures the workload afterwards and reverts indexes that didn't help, but it still doesn't consolidate overlapping indexes for you.

Go deeper with the DpkDB SQL Server tuning series

Missing index suggestions are one input to tuning, and they only make sense next to the plan, the workload and the table design. These DpkDB guides cover the rest of that work for production DBAs:

• Reading SQL Server execution plans: a practical guide explains seeks, scans, key lookups and estimate problems, which is what you need to confirm a new index is doing its job.
• Clustered vs. nonclustered indexes in SQL Server covers key choice, covering indexes and why every nonclustered index carries the clustered key.
• SQL Server index optimization: a practical workflow is the other half of this job: finding the unused, duplicate and overlapping indexes you already have.
• Why is my stored procedure fast one day and slow the next? deals with parameter sniffing, a problem that often looks like a missing index and isn't one.

The full set is on the SQL Server page.

Checklist

1. Check sqlserver_start_time. Make sure the DMVs cover a full business cycle, and save the output before changing anything.
2. Rank suggestions by cost × impact × seeks, then find the actual queries behind the top ones.
3. Fix the query first if it selects too much or its predicate can't use an index.
4. Compare with existing indexes. Widen one of them before adding a new one.
5. Merge overlapping suggestions into one index: equality columns, then inequality columns, with a short included list.
6. Measure reads and writes before and after on a copy of production, then re-check usage after deployment and drop what isn't used.

References

• Tune nonclustered indexes with missing index suggestions (Microsoft Learn), including the full list of limitations
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats_query
• sys.dm_db_index_usage_stats

Why does my SQL Server transaction log keep growing, and should I shrink it?

A 200 GB .ldf for a 20 GB database? Shrinking only treats the symptom. log_reuse_wait_desc tells you the real cause, and here's how to fix each one.

#maintenance#transactions#troubleshooting