SQL Server / Intermediate / Lesson 9

Indexes, statistics, and execution plans

Connect a query predicate to physical access paths. Read plan evidence, choose focused indexes, and separate estimation mistakes from expensive but appropriate work.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Explain clustered and nonclustered indexes.
Identify a SARGable range predicate.
Compare estimates, actual rows, and logical reads.

Understand what an index stores

A rowstore clustered index organizes the table's leaf data by its key. A nonclustered index maintains another ordered structure with keys and a row locator. A table has at most one clustered index but can have several nonclustered indexes. A primary key commonly creates a supporting unique index; logical identity and physical clustering are related choices, not synonyms.

Every additional index occupies space and adds maintenance to inserts, deletes, and relevant updates. A read improvement may slow a write-heavy workload. Choose narrow, stable keys and justify each index with actual query patterns. A heap is a table without a clustered index; it is not inherently corrupt or always slow. Evaluate the workload rather than applying a universal table template.

Make search predicates usable

A SARGable predicate can support useful index search arguments. A direct date range on CreatedAt is usually more index-friendly than applying YEAR(CreatedAt) to every row. This does not guarantee a seek: the optimizer may reasonably scan when many rows qualify. Compatible parameter types also matter because implicit conversion on the indexed column can impair access or estimation.

The two predicates express January 2026 differently. The half-open range preserves all timestamp precision and exposes a contiguous key range. Use an execution plan to verify the effect rather than declaring every function universally forbidden. A computed-column index is a different design that can sometimes support an expression, but it needs its own requirements and maintenance analysis.

sql Compare equivalent date predicates

-- Predicate comparison only; assumes a lab OrderHeader table.
SELECT OrderId FROM dbo.OrderHeader
WHERE YEAR(CreatedAt)=2026 AND MONTH(CreatedAt)=1;
SELECT OrderId FROM dbo.OrderHeader
WHERE CreatedAt>='20260101' AND CreatedAt<'20260201';

Both target January for non-null timestamps. The second exposes a direct range; no plan shape or timing is fabricated.

Design a focused composite index

For customer-specific date searches, an index beginning with CustomerId and then CreatedAt can support equality followed by a date range. INCLUDE can carry selected non-key columns without adding them to the search key. The exact useful order depends on predicates, ordering, selectivity, and other queries. The conceptual DDL is a design proposal, not an instruction to add an index blindly.

Covering a query can avoid repeated lookups, but including every column makes an index wide and expensive. Examine existing indexes for overlap and write cost before creating another. A missing-index suggestion describes one optimization opportunity and omits broader design tradeoffs. It is evidence to evaluate, not an automatically approved migration.

sql A candidate covering index

-- Design illustration for a disposable lab schema.
CREATE INDEX IX_OrderHeader_Customer_Created
ON dbo.OrderHeader(CustomerId,CreatedAt)
INCLUDE (OrderId,Amount);

Expected benefit is fewer pages or lookups for compatible searches; actual benefit requires representative plans and measurements.

Read estimates alongside runtime facts

An estimated plan explains a proposed strategy without running the query. An actual plan requires execution and includes runtime facts. Never request an actual plan for an unsafe mutation on a live database merely to inspect it. Look at actual versus estimated rows, join choices, residual predicates, sort or hash spills, and repeated key lookups.

Statistics describe distributions used for cardinality estimates; stale or unrepresentative statistics can contribute to poor choices. A large estimate error is a clue, not proof that updating all statistics is the right fix. Compare parameter values, predicate correlation, and data skew. Operator percentage costs are estimated model costs, not a stopwatch measuring real elapsed time.

Measure the complete tradeoff

In a disposable representative environment, compare logical reads with SET STATISTICS IO and CPU/elapsed metrics with SET STATISTICS TIME, alongside actual plans. Repeat only enough to distinguish noise and cache effects, documenting conditions. Small table-variable examples teach semantics but cannot demonstrate production selectivity or index value. Avoid clearing a shared plan or buffer cache for a lesson.

Validate identical result sets before comparing speed. Then include insert and update costs, index size, concurrency, and the frequency of the query. A change saving ten milliseconds on one rare report can be worse than a small regression on every order write. Keep a reversible migration and an explicit success criterion for any real optimization.

Common errors and fixes

A seek still reads many rows.

Why it happens: The seek range is broad or a residual predicate rejects many candidates.
How to fix it: Inspect rows read, output rows, and key ordering.
Prevent it next time: Measure work rather than operator labels.

Writes slowed after indexing.

Why it happens: Each mutation now maintains additional structures.
How to fix it: Remove unjustified overlap after workload review.
Prevent it next time: Budget write and storage costs before adding indexes.

Plan percentages disagree with elapsed time.

Why it happens: Estimated cost percentages were interpreted as actual timings.
How to fix it: Use runtime metrics and waits with the plan.
Prevent it next time: Label estimated and observed evidence separately.

Check your understanding

1. A query filters CustomerId by equality and CreatedAt by range. Suggest a candidate key order.

Show worked solution

CustomerId, CreatedAt is a reasonable starting point because equality selects a customer range before the date interval. Verify ordering needs, existing indexes, selectivity, and write cost; it is not automatically optimal.

2. An actual plan shows 100 estimated rows and 100000 actual rows. What should be investigated?