9.3 Index Changes and Execution Plan Review

Missing Index DMVs

When the optimizer cannot find a useful index, it records a missing-index suggestion in memory-only DMVs. These persist only until the service restarts, so on a running server they reflect activity since the last restart, not the lifetime of the database. The three views join on index_handle and group_handle:

• sys.dm_db_missing_index_details: one row per missing-index feature, with the equality, inequality, and included columns the optimizer wanted, plus an estimated average user impact (a percentage).
• sys.dm_db_missing_index_group_stats: aggregated stats per group - user_seeks, user_scans, avg_user_impact, last_user_seek - ranking candidates by impact and frequency.
• sys.dm_db_missing_index_groups: the join table mapping groups to details.

A typical ranking query:

SELECT TOP (20)
    mig.index_group_handle, mid.index_handle,
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.user_seeks, migs.user_scans, migs.avg_user_impact, migs.last_user_seek,
    mid.statement AS [object]
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

Treat the suggestions as input, not output. The optimizer does not de-duplicate overlapping suggestions, does not know about existing indexes that almost cover the request, and cannot tell you whether a suggested index hurts write throughput on a busy OLTP table. Before creating one, merge overlapping suggestions, check for existing indexes that already cover most columns, and weigh the read benefit against write overhead. The estimated impact percentage is a heuristic, not a measurement - always verify with SET STATISTICS IO ON before and after.

Unused Indexes

Every nonclustered index costs writes: each insert, update, or delete must maintain every index. sys.dm_db_index_usage_stats exposes per-index read and write counters since the last service restart. An index whose user_seeks, user_scans, and user_lookups are all zero (or very low) since the last restart is a drop candidate.

SELECT OBJECT_NAME(i.object_id) AS [object], i.name AS index_name,
    ius.user_seeks, ius.user_scans, ius.user_lookups,
    ius.user_updates, ius.last_user_seek
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND ius.database_id = DB_ID()
ORDER BY ius.user_updates DESC, COALESCE(ius.user_seeks + ius.user_scans + ius.user_lookups, 0) ASC;

Cautions the exam tests: counters reset to zero on service restart, so a monthly batch job whose index shows zero since yesterday's restart is not unused - it just has not run yet. A unique index backing a constraint (is_unique_constraint = 1) should never be dropped just because it is unused for reads; it exists to enforce correctness. And user_updates counts writes, not just row updates; an index with high user_updates and zero reads is the strongest drop candidate.

Index Types and When to Use Each

Covering indexes eliminate key lookups: when a nonclustered index satisfies a query's WHERE but not its SELECT list, the engine looks up each remaining column from the clustered index (or heap) - one lookup per row, often the dominant cost. Adding the missing columns as INCLUDE columns makes the index covering and removes the lookup. This is one of the highest-leverage optimizations on the exam.

Filtered indexes are powerful but picky: the query's predicate must be a subset of (or equal to) the index filter for the optimizer to choose it. A filtered index WHERE status = 'Open' is usable by WHERE status = 'Open' AND priority = 1 but not by WHERE status IN ('Open', 'Pending').

Reading Execution Plans

Execution plans are read right to left; the rightmost operator is the first to execute. The operators to recognize on sight:

• Index Seek / Clustered Index Seek: efficient; uses the index b-tree to find rows. Good.
• Index Scan / Clustered Index Scan: reads the whole structure. Bad unless the table is small or the query needs most rows.
• Key Lookup: fetches missing columns from the clustered index per row. The classic covering-index signal.
• RID Lookup: same as a key lookup, on a heap.
• Sort: memory-consuming; often indicates a missing supporting index that could deliver ordered data.
• Hash Match (Join): builds a hash table in memory; good for large unsorted inputs; bad when one input is small and indexed (a nested loop would be cheaper).
• Merge Join: requires sorted inputs; efficient when both inputs are already ordered via indexes.
• Nested Loops: best when the outer input is small and the inner input has an index seek.
• Table Spool / Index Spool: caches intermediate results; occasionally useful, often a sign of a re-executed subquery.
• Compute Scalar: evaluates an expression; cheap unless it wraps a function that disables SARGability.

Estimated plans are produced by the optimizer from statistics without executing the query; actual plans include runtime row counts, actual row counts versus estimated, and actual recompiles. A wide gap between estimated and actual rows is the signature of stale statistics or parameter sniffing.

SET STATISTICS IO and TIME

SET STATISTICS IO ON returns logical reads, physical reads, read-ahead reads, and lob logical reads per table referenced by the query - the most reliable measure of query cost. SET STATISTICS TIME ON returns parse, compile, and CPU time. Reduce logical reads (the number of 8 KB pages touched in the buffer pool) as the primary objective; CPU time and duration are secondary signals.

Parameter Sniffing, Plan Regression, and Forcing Plans

Parameter sniffing is the optimizer using the first parameter values it sees to compile a plan that is then reused for all future parameter values - good when distributions are uniform, bad when they are skewed. The classic symptom: a stored procedure runs fast for some parameter values and slowly for others, and the plan shows an inappropriate join or scan for the slow values. Remedies:

• OPTION (RECOMPILE): forces a fresh plan every execution; best for ad hoc parameter sets where compile cost is small relative to execution cost.
• OPTIMIZE FOR UNKNOWN: uses an averaged cardinality estimate instead of a specific parameter value, trading a perfect plan for a stable one.
• Local variables inside the procedure defeat sniffing at the cost of using density estimates.
• Query Store plan forcing: pin the plan that works across the most parameter distributions, especially when automatic plan correction is involved.
• Query hints via USE PLAN or query store hints: more invasive, generally reserved for stubborn regressions.

The durable progression the exam rewards: first diagnose with Query Store and the plan, then prefer a query hint or index change that fixes the root cause, and use plan forcing as a stabilizer while the durable fix is built.

Putting It Together: A Diagnostic Loop

The exam frames index and plan work as a loop, not a single step:

1. Query Store flags a regression.
2. Compare the regressed and previous plans; identify the operator that changed (scan replaced a seek, hash replaced a nested loop, sort appeared).
3. If a missing index is suggested by sys.dm_db_missing_index_details, validate it: does it cover the WHERE and SELECT list? Would it create a covering index that removes a key lookup? Is it duplicative of an existing index?
4. Apply the index; re-run the query with SET STATISTICS IO ON; confirm logical reads dropped.
5. If the regression is parameter-sensitivity, choose a hint (OPTIMIZE FOR UNKNOWN, RECOMPILE) or force the plan via Query Store while the durable fix stabilizes.
6. Drop unused indexes identified by sys.dm_db_index_usage_stats only after confirming no monthly or annual workload depends on them.

This loop connects section 9.1 (Query Store detects regression), 9.3 (index and plan changes fix the cause), and 9.4 (query construct modifications prevent recurrence).