Missing Indexes

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.

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

1. The optimizer parses the query and checks statistics, which are summary numbers about data distribution.
2. 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.
3. If it sees a cheaper index shape, it records columns into equality_columns, inequality_columns, and included_columns in the missing-index DMVs.
4. SQL Server also aggregates repeated sightings with counters like user_seeks, user_scans, and a rough rank called improvement_measure.
5. 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

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.

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 WHERE and JOIN first in the key, with equality columns before range columns. Put only output columns in INCLUDE so 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 INCLUDE for coverage, and keep the seek key narrow.
• Ignoring maintenance cost. Correction: remember every extra index slows INSERT, UPDATE, and DELETE.
• 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.
• INCLUDE helps 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.