HardSQL#1368 min readJul 11, 2026
Missing Indexes
practice
Hook: Missing-index tips are SQL Server’s way of waving a tiny flag and saying, “This query is taking the scenic route.” Interviewers love this topic because it tests whether you know the difference between a helpful suggestion and a safe, production-ready fix.
Question: What are missing indexes in SQL Server, and how should you use them?
Answer: A missing index is not a broken index; it is a recommendation from SQL Server that a query might run faster with a different nonclustered index. The optimizer noticed scans or key lookups that were expensive and recorded a possible improvement in the execution plan or missing-index DMVs. You should treat that as a starting point, then verify the real workload, because a good read index can still hurt inserts, updates, deletes, and storage.
Interview-Ready Answer: I think of missing indexes as SQL Server’s cost-based hints, not commands. When the optimizer sees a query doing too much work, it may suggest a nonclustered index based on the predicates and selected columns. I don’t create it blindly; I check the execution plan, the DMV ranking like improvement_measure, and whether the index shape is right — equality columns first, range columns next, and INCLUDE only for columns I need to cover the query.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3EMissing%20Indexes%3C%2Ftext%3E%3C%2Fsvg%3E)
What it is
A missing index is really an advisory signal. SQL Server is saying, “If this query had an index shaped like this, I estimate the read cost would drop.” It is usually about a nonclustered index, which is a separate sorted structure used to find rows faster than scanning the whole table. A lookup is when SQL Server finds rows in one index, then jumps back to the table or clustered index to fetch columns that are not covered.
How SQL Server finds it under the hood
- The optimizer parses the query and checks statistics, which are summary numbers about data distribution.
- It compares access paths: table scan, index scan, index seek, and lookup. A seek is a targeted read; a scan reads many or all rows.
- If it sees a cheaper index shape, it records columns into
equality_columns,inequality_columns, andincluded_columnsin the missing-index DMVs. - SQL Server also aggregates repeated sightings with counters like
user_seeks,user_scans, and a rough rank calledimprovement_measure. - You then review the suggestion, merge duplicates, test it against the real workload, and create the index only if the read win is worth the write cost.
How to design the real index
| Part | Job | Rule of thumb | Gotcha |
|---|---|---|---|
| Key columns | Seek/filter | Equalities first | Bad order hurts seeks |
| Range column | Limit scan | Put last | Only one useful range |
| INCLUDE columns | Cover output | Only select list | Do not help seeks |
| Filtered index | Subset only | Hot rows only | DMVs rarely suggest it |
When and why to use it
Use missing-index advice when a high-traffic read query is slow because it is scanning a large table or doing many lookups. This is most useful in OLTP systems, where a small read improvement can save a lot of CPU and latency. Do not trust it blindly on tiny tables, batch reports, or queries that return a huge percentage of the table; in those cases a scan may be cheaper than an index seek. Also remember that every extra index has a space cost and a write cost: each insert, update, and delete must maintain that index too.
Performance and limits
Read cost is roughly O(n) for a scan and about O(log n + k) for a seek, where k is the number of rows actually returned. That is why a selective index can turn a query from reading thousands of 8 KB pages into reading only a few dozen. But indexes are not free: storage is roughly proportional to table size, and write overhead grows with every extra index. In SQL Server, missing-index DMVs are also limited and temporary; they do not persist across restart or failover, and they can be overwritten, so treat them as a short-term clue, not an audit log. One more version note: Azure SQL Database can automate some index creation and dropping when automatic tuning is enabled, but regular SQL Server mostly only recommends — you decide.
Memory Hook: Think of a missing index like a store clerk saying, “If we put this aisle closer to the door, shoppers would move faster” — but you still have to check whether that new aisle makes stocking harder.
Real-World Story: In a checkout service, the Orders table grows into tens of millions of rows. A common API call filters by CustomerID, Status, and recent OrderDate, but after a sales event the endpoint jumps from 40 ms to 2 seconds. The plan shows a scan and a missing-index hint, CPU climbs, and logs start filling with timeout errors. A rushed fix that creates three separate indexes from three similar plans helps reads, but it also slows inserts enough to cause lock contention during peak traffic. The real fix is one well-shaped nonclustered index that matches the common predicates and covers only the needed columns.
What goes wrong in incidents like this is not that the missing-index hint was useless; it is that someone treated it like an automatic truth. The symptom is usually high CPU, large logical reads, and a sudden increase in wait time on the database. Users see slow order lookups, retry spinners, and occasional 500 errors. In the logs, you often see timeouts, deadlock retries, or query duration spikes right after a feature launch or traffic surge.
SQL
-- SQL Server demo: how a missing index idea becomes a real index decision.
-- This script is safe to run in tempdb and shows both the before/after pattern
-- and the important edge case: a missing index hint is only a hint.
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
IF OBJECT_ID('tempdb..#OrdersDemo') IS NOT NULL
DROP TABLE #OrdersDemo;
CREATE TABLE #OrdersDemo
(
OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate date NOT NULL,
Status varchar(12) NOT NULL,
Amount decimal(10,2) NOT NULL,
Notes varchar(100) NULL
);
;WITH n AS
(
SELECT TOP (5000)
ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS rn
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b
)
INSERT INTO #OrdersDemo (CustomerID, OrderDate, Status, Amount, Notes)
SELECT
(rn % 200) + 1,
DATEADD(day, rn % 365, CONVERT(date, '2024-01-01')),
CASE
WHEN rn % 10 = 0 THEN 'Pending'
WHEN rn % 10 < 8 THEN 'Shipped'
ELSE 'Cancelled'
END,
CAST((rn % 1000) / 10.0 AS decimal(10,2)),
CONCAT('row ', rn)
FROM n;
-- Baseline: no supporting nonclustered index yet, so this is more likely to scan.
-- OPTION (RECOMPILE) helps SQL Server compile the statement now, which is when
-- it can decide whether to record a missing-index suggestion.
SELECT CustomerID, OrderDate, Amount
FROM #OrdersDemo
WHERE CustomerID = 42
AND Status = 'Pending'
AND OrderDate >= '2024-06-01'
OPTION (RECOMPILE);
-- Advisory only: in some environments or tiny demos, this DMV can be empty.
-- In real systems, it helps you rank candidates, but it does NOT create them.
SELECT
mid.statement AS table_name,
migs.user_seeks,
migs.user_scans,
CAST(migs.avg_total_user_cost AS decimal(18,2)) AS avg_total_user_cost,
CAST(migs.avg_user_impact AS decimal(18,2)) AS avg_user_impact,
CAST((migs.user_seeks + migs.user_scans) * migs.avg_total_user_cost * migs.avg_user_impact AS decimal(18,2)) AS improvement_measure,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;
-- Design choice: equality columns first, then the range column.
-- INCLUDE columns are only for coverage; they do not help the seek itself.
CREATE INDEX IX_OrdersDemo_Customer_Status_Date
ON #OrdersDemo (CustomerID, Status, OrderDate)
INCLUDE (Amount);
-- Same query again: SQL Server now has a cheaper access path.
SELECT CustomerID, OrderDate, Amount
FROM #OrdersDemo
WHERE CustomerID = 42
AND Status = 'Pending'
AND OrderDate >= '2024-06-01'
OPTION (RECOMPILE);
-- Edge case: a query that returns many rows may still prefer a scan.
-- That is normal and proves why you never create every suggested index blindly.
SELECT COUNT(*) AS matching_rows
FROM #OrdersDemo
WHERE Status = 'Shipped'
OPTION (RECOMPILE);
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;Follow-up & Tricky Questions:
- How do you decide whether to create a missing index? I check whether the query is important, how selective the predicate is, and whether the index would help multiple queries. Then I test the change with real workload metrics like logical reads, CPU, and write latency.
- What goes in the key and what goes in INCLUDE? Put columns used in
WHEREandJOINfirst in the key, with equality columns before range columns. Put only output columns inINCLUDEso the index covers the query without bloating the seek path. - Why do missing-index suggestions often look duplicated? Different queries may request overlapping shapes, and the DMV does not consolidate them into one perfect design for you. Your job is to merge them into a smaller set of useful indexes instead of creating one index per plan.
- What are the biggest DMV limitations? The data is temporary, limited, and workload-dependent. It does not understand future write cost, and it does not always capture filtered, unique, or perfectly ordered designs.
- Does a missing index always mean the current plan is bad? No. Sometimes a scan is still the cheapest choice, especially for small tables or queries that return a large portion of rows. The hint means “possible improvement,” not “must fix.”
- Should I trust the improvement_measure exactly? No, it is only a ranking signal based on observed usage and estimated cost. It helps you prioritize, but you still need to validate with actual I/O and execution time.
- Can SQL Server create the index for me? Standard SQL Server only recommends it, but Azure SQL Database can automate some index changes if automatic tuning is enabled. Even there, you should understand why the change is happening.
- Should I create every missing index from the DMV? No, that is the classic mistake. The DMV shows opportunities, but some are redundant, some are too broad, and some would slow the write path more than they help reads.
- Do missing-index DMVs survive restart or failover? No, they are not a permanent history table. Treat them as a short-lived performance breadcrumb, not a long-term record.
Tricky / Gotcha Questions:
- Can the DMV tell me the perfect key order? Not reliably. It gives column groups, but you still need to reason about equality before range, selectivity, and whether the same index should serve more than one query.
- Can a missing index suggestion be a filtered index? Usually not directly. If only a small hot slice of rows matters, a filtered index may be better, but that is a manual design choice you make after seeing the workload.
- If the query is fast after caching, do I still need the index? Maybe not. A plan that is fast once but expensive under load can still become a bottleneck, so you judge by sustained workload, not a single lucky execution.
Common Mistakes:
- Creating every suggestion from the DMV. Correction: consolidate overlapping suggestions and validate the write cost first.
- Putting output columns in the key. Correction: use
INCLUDEfor coverage, and keep the seek key narrow. - Ignoring maintenance cost. Correction: remember every extra index slows
INSERT,UPDATE, andDELETE. - Trusting one query’s plan in isolation. Correction: test the broader workload, because one perfect query can hurt ten others.
Memory Hook: “Seek for filters, include for output, and verify before you build.”
Cheat Sheet:
- Missing indexes are recommendations, not errors.
- They usually point to a nonclustered index.
- Key order matters: equality first, range last.
INCLUDEhelps coverage, not seeks.- Use the DMV as a ranking hint, not a final decision.
- Always measure reads, CPU, and write overhead after the change.
Practice Tasks:
- Take a slow query in a test database and identify which columns belong in the key versus
INCLUDE. - Find a missing-index hint in an execution plan, then compare it to the DMV output and explain whether they match.
- Create the index, rerun the query, and compare logical reads before and after.