How do I find and fix missing indexes in SQL Server?

A query that averages 200,000 logical reads and returns 12 rows is a candidate. One that reads 200,000 pages and returns 5 million rows is doing what it was asked. Without Query Store, order sys.dm_exec_query_stats by total_logical_reads and get plans from sys.dm_exec_query_plan. Enabling Query Store and choosing targets is covered in SQL Server query performance: how to measure and improve it.

What the missing index DMVs add

SQL Server records the index the optimizer wanted for each compiled query in sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats, and shows the same request as green text in the plan. Use it as a second opinion on the query you are already looking at. Each request covers one query, ignores existing indexes and write cost, lists key columns in table order and never proposes a filtered index or a different sort order. The details are in SQL Server missing index DMVs: how to use them without over-indexing.

The DMVs also say nothing about several of the patterns below, because the optimizer only requests an index for filter and join predicates. It does not request one to remove a sort, and a missing foreign key index hurts DELETE statements on the parent table that nobody thinks to look at.

Signs of a missing index in the plan

Use the actual plan where you can. It has the runtime counts that these checks depend on.

A scan or seek with a selective residual predicate

Open the properties of an Index Scan, Clustered Index Scan or Index Seek. Seek Predicates are used to navigate the B-tree. Predicate is a residual filter applied to every row the operator reads. In an actual plan, compare Number of Rows Read with Actual Number of Rows. Reading 4 million rows to return 300 means the residual predicate does the filtering and no index supports it.

This applies to seeks too. A seek on CustomerID with a residual predicate on Status reads every order for the customer and discards most of them. If that customer has 500,000 orders, the seek is a range scan in disguise.

A key lookup executed thousands of times

A Key Lookup fetches columns the nonclustered index doesn't have, once per row. Check Number of Executions on the lookup. At 40 it is harmless. At 400,000 it is most of the query's reads. A Predicate on the Key Lookup itself is worse: rows are fetched from the clustered index only to be filtered out afterwards, so that column belongs in the nonclustered index.

A sort that an index order would remove

A Sort operator under ORDER BY, TOP, a window function or a Merge Join needs a memory grant and reads its whole input before returning a row. If the index key matches the equality filter followed by the sort column, rows arrive already ordered and a TOP (20) stops after 20 rows:

SELECT TOP (20) OrderID, OrderDate, TotalDue
FROM Sales.Orders
WHERE CustomerID = @CustomerID AND Status = 'Open'
ORDER BY OrderDate DESC;

With an index on (CustomerID) alone, SQL Server reads all of the customer's orders, looks up Status, sorts, and keeps 20. A missing index request for this query lists CustomerID and Status as equality columns and puts OrderDate among the included columns, which leaves the sort in place.

A hash join on an unindexed join column

A Hash Match join that scans a large table to match a small number of rows from the other input usually means the join column on the large table has no index. With one, the optimizer can choose Nested Loops and seek once per outer row. Compare the row counts of the two inputs: a hash join between two large inputs is often the right plan, and no index will improve it.

Foreign keys with no supporting index

Creating a foreign key constraint does not create an index on the referencing columns. Two things suffer. Joins from child to parent scan the child table. Every DELETE on the parent, and every UPDATE of its key, has to check the child table for referencing rows, which without an index means scanning the child table while holding locks. That is a common source of blocking and deadlocks on tables that look unrelated to the slow statement.

This query (SQL Server 2017 and later) lists foreign keys whose columns are not the leading key columns of any index on the child table:

WITH fk AS (
    SELECT f.object_id AS fk_id, f.name AS fk_name,
           f.parent_object_id, f.referenced_object_id,
           COUNT(*) AS column_count,
           STRING_AGG(c.name, N', ') WITHIN GROUP (ORDER BY fc.constraint_column_id) AS fk_columns
    FROM sys.foreign_keys AS f
    JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.object_id
    JOIN sys.columns AS c ON c.object_id = fc.parent_object_id AND c.column_id = fc.parent_column_id
    GROUP BY f.object_id, f.name, f.parent_object_id, f.referenced_object_id
)
SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name,
       OBJECT_NAME(fk.parent_object_id) AS child_table,
       fk.fk_name, fk.fk_columns,
       OBJECT_NAME(fk.referenced_object_id) AS parent_table,
       (SELECT SUM(ps.row_count)
        FROM sys.dm_db_partition_stats AS ps
        WHERE ps.object_id = fk.parent_object_id AND ps.index_id IN (0, 1)) AS child_rows
FROM fk
WHERE NOT EXISTS (
        SELECT 1
        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.foreign_key_columns AS fc ON fc.parent_column_id = ic.column_id
        WHERE i.object_id = fk.parent_object_id
          AND fc.constraint_object_id = fk.fk_id
          AND i.type IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND i.has_filter = 0
          AND ic.key_ordinal BETWEEN 1 AND fk.column_count
        GROUP BY i.index_id
        HAVING COUNT(*) = fk.column_count)
ORDER BY child_rows DESC;

Not every row needs an index. A small child table, or a parent whose rows are never deleted and never joined from, can stay as it is. Start with large child tables whose parents see deletes.

Design the index

Check the table's existing indexes before writing a new one. Adding a key column or an included column to an index that already leads with the right column is nearly always better than a new index. SQL Server index optimization: a practical workflow has the queries for listing and comparing them.

For the TOP (20) query above:

1. Equality columns first. CustomerID and Status are both compared with =. Lead with CustomerID, because other queries on the table filter on it alone.
2. Then the sort or range column. OrderDate DESC lets the seek return rows in the order the query wants. Only one range or sort column can be used this way.
3. Then included columns. TotalDue is only returned. OrderID is the clustered key and is already in every nonclustered index.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Status_OrderDate
ON Sales.Orders (CustomerID, Status, OrderDate DESC)
INCLUDE (TotalDue);

The plan becomes one seek that reads 20 rows, with no lookup and no sort. If open orders are a small fraction of the table and the query always uses the literal 'Open', a filtered index on (CustomerID, OrderDate DESC) INCLUDE (TotalDue) WHERE Status = 'Open' is smaller and cheaper to maintain. It does not work when the status arrives as a parameter, because a cached plan has to be valid for every value.

Create it safely on a big table

A plain CREATE INDEX on a table with hundreds of millions of rows is an outage if you run it at the wrong time. An offline build of a nonclustered index holds a shared lock on the table for the whole build, so reads continue and every write waits.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Status_OrderDate
ON Sales.Orders (CustomerID, Status, OrderDate DESC)
INCLUDE (TotalDue)
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)),
      RESUMABLE = ON, MAX_DURATION = 240 MINUTES,
      MAXDOP = 4);

Without WAIT_AT_LOW_PRIORITY, an online build that is waiting for its final lock behind one long transaction blocks every new statement on the table until that transaction ends.

Before you start, plan for:

• Data file space. Room for the new index, plus sort space of similar size in the same filegroup unless you sort in tempdb.
• tempdb. An online build uses row versioning, so concurrent updates and deletes generate version records in tempdb for as long as it runs.
• Transaction log. Online builds are fully logged. A non-resumable build is one transaction, so the log can't be truncated until it finishes, and every availability group replica has to receive and redo it. See Why does my SQL Server transaction log keep growing?
• Write overhead during the build. Each modification maintains the new index as well. A paused resumable build keeps that cost, so resume or abort it.

Track and control a resumable build:

SELECT name, state_desc, percent_complete, start_time, last_pause_time
FROM sys.index_resumable_operations;

ALTER INDEX IX_Orders_CustomerID_Status_OrderDate ON Sales.Orders PAUSE;
ALTER INDEX IX_Orders_CustomerID_Status_OrderDate ON Sales.Orders RESUME;
ALTER INDEX IX_Orders_CustomerID_Status_OrderDate ON Sales.Orders ABORT;

A resumable build creates the index statistics with default sampling and not a full scan. Run UPDATE STATISTICS ... WITH FULLSCAN on the new index afterwards if estimates on it look wrong. On Standard edition, schedule the offline build in a window when writes to the table can wait.

Verify and roll back

1. Plan. Run the query with the actual plan. The new index should appear as a seek, and the scan, lookup or sort you targeted should be gone. Reading SQL Server execution plans covers what to check.
2. Reads. Compare SET STATISTICS IO output before and after with the same parameter values. Test more than one value, including the largest customer.
3. Workload. After a few days, compare the query's average logical reads and CPU in Query Store, and check the table's INSERT and UPDATE statements for the added cost.
4. Usage. Read sys.dm_db_index_usage_stats for the new index after a week. user_seeks should be growing. An index with only user_updates was not worth creating.

SELECT i.name, us.user_seeks, us.user_scans, us.user_lookups, us.user_updates, us.last_user_seek
FROM sys.indexes AS i
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 i.object_id = OBJECT_ID(N'Sales.Orders');

Rolling back is DROP INDEX IX_Orders_CustomerID_Status_OrderDate ON Sales.Orders;. It needs a brief schema modification lock, so it can wait behind a long-running query. A new index can also change plans for other queries. If one regresses, Query Store shows the old and new plan side by side, and forcing the old plan is a quick fix while you decide.

When the fix is not an index

Some plans look like a missing index and are not. Check these before creating anything.

Non-SARGable predicates. A function or expression around the column prevents a seek on an index that already exists:

WHERE YEAR(OrderDate) = 2026                                     -- scans
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'     -- seeks

LIKE '%text', ISNULL(Col, 0) = 0 and arithmetic on the column (TotalDue * 1.2 > 100) do the same.

Implicit conversions. When a column is compared with a value of a type that has higher precedence, SQL Server converts the column. The usual case is a varchar column compared with an nvarchar parameter, which many client libraries send by default for strings. Look for CONVERT_IMPLICIT around the column in the predicate and for the plan warning. Fix the parameter type in the application, or cast the parameter and not the column.

Stale statistics. If the estimated row count is far from the actual one, the optimizer may ignore a perfectly good index or use it for far too many rows. Update the statistics and compare plans before adding an index:

UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

Parameter sensitivity. A query that is fast for most values and slow for a few has a plan problem. Why is my stored procedure fast one day and slow the next? covers it.

Common mistakes

• Starting from the suggestion list. You end up indexing for queries that don't matter and missing sorts and foreign keys that do.
• Creating a new index when an existing one needed one more column. Compare with the table's current indexes first.
• Indexing for the lookup count in an estimated plan. Use the actual plan and its Number of Executions.
• Running an offline build on a busy table in business hours. Writes queue behind it until it finishes.
• Using ONLINE = ON without WAIT_AT_LOW_PRIORITY behind long transactions. The build's lock request blocks everything queued after it.
• Ignoring log and tempdb space. A build that fills the log fails and has to roll back.
• Indexing around a non-SARGable predicate or a type mismatch. The new index gets scanned just like the old one.
• Never checking usage afterwards. Unused indexes stay for years and slow every write.

Checklist

1. Rank queries by total logical reads in Query Store and compare reads with rows returned.
2. In the actual plan, look for residual predicates, repeated key lookups, avoidable sorts and hash joins on unindexed columns.
3. List foreign keys with no supporting index, starting with the largest child tables.
4. Rule out non-SARGable predicates, implicit conversions and stale statistics.
5. Check existing indexes and widen one if it leads with the right column.
6. Design the key as equality columns, then the sort or range column, with a short INCLUDE list. Consider a filter.
7. Build with ONLINE, RESUMABLE, WAIT_AT_LOW_PRIORITY and MAXDOP where your version and edition allow, with log, data and tempdb space checked.
8. Verify the plan, reads and usage counters, and drop the index if it isn't used.

References

• CREATE INDEX (Transact-SQL) (Microsoft Learn), including resumable operations and WAIT_AT_LOW_PRIORITY
• Guidelines for online index operations
• How online index operations work
• Disk space requirements for index DDL operations
• Transaction log disk space for index operations
• sys.index_resumable_operations
• Primary and foreign key constraints, section "Indexes on foreign key constraints"
• Tune nonclustered indexes with missing index suggestions
• Data type precedence
• Editions and supported features of SQL Server 2022

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

Query sys.dm_db_missing_index_details and its companion DMVs, read the numbers critically, find the queries behind each suggestion and merge them into the few indexes worth creating.

#db-administration#execution-plans#indexing