Reading an Execution Plan
The SQL Server query optimizer produces an execution plan describing exactly how it will retrieve data — which indexes to use, which join algorithm to apply, and in what order operations happen. Plans are read right-to-left, bottom-to-top, with each operator showing an estimated or actual cost as a percentage of the total batch. A Clustered Index Scan reads every row in the table's clustered index, an Index Seek navigates directly to matching rows using an index's B-tree structure, and the gap between estimated and actual row counts in an actual execution plan is one of the strongest signals that statistics are stale or a predicate is poorly selective.
Cricket analogy: An Index Seek is like a fielder throwing directly to the keeper for a run-out because they know exactly where the stumps are, while a Clustered Index Scan is like searching the entire boundary rope for a lost ball instead of going straight to where it landed.
Common Costly Operators and Warnings
Beyond scans versus seeks, three plan patterns deserve particular attention. A Key Lookup following an Index Seek means a nonclustered index didn't cover all the columns needed, forcing an extra trip to the clustered index per row — expensive at scale and often fixable with an INCLUDE column. A Hash Match join is used when neither input is sorted on the join key and typically indicates a missing useful index, while a Nested Loops join, efficient for small outer inputs, becomes a performance problem when the optimizer's row estimate is wrong and the outer input turns out to be huge. SQL Server also surfaces explicit warnings directly in the plan, such as implicit conversion warnings that silently disable index usage.
Cricket analogy: A Key Lookup is like a scorer finding a batsman's name on the scoresheet (the seek) but then having to walk to the pavilion to check their full career stats (the lookup) because the scoresheet alone doesn't have it.
Tuning with Indexes and Query Rewrites
The most reliable tuning tool is a well-designed nonclustered index whose key columns match the query's WHERE and JOIN predicates and whose INCLUDE columns cover the remaining SELECT list, eliminating Key Lookups entirely. Beyond indexing, SARGable predicates matter enormously: wrapping a column in a function, such as WHERE YEAR(OrderDate) = 2026, prevents the optimizer from seeking on an index for that column, while a rewrite like WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01' remains seekable. Updating statistics with UPDATE STATISTICS or enabling automatic statistics updates keeps the optimizer's row estimates accurate, which directly affects whether it chooses Nested Loops or Hash Match, and whether memory grants are sized correctly.
Cricket analogy: A covering index is like a scorecard that already lists runs, balls faced, and strike rate together, so a commentator never needs to flip to a separate stats sheet — exactly what an INCLUDE column achieves for a query's SELECT list.
-- Non-SARGable: function on column prevents index seek
SELECT OrderId, CustomerId, OrderDate
FROM Orders
WHERE YEAR(OrderDate) = 2026;
-- SARGable rewrite: allows an index seek on OrderDate
SELECT OrderId, CustomerId, OrderDate
FROM Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';
-- Covering index to eliminate a Key Lookup
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Covering
ON Orders (OrderDate)
INCLUDE (OrderId, CustomerId, TotalAmount);
-- Inspect the plan and force a statistics refresh
SET STATISTICS XML ON;
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
GODo not tune purely from estimated execution plans on a development box with a tiny or unrepresentative dataset. Row estimates and chosen join strategies are driven by cardinality and statistics that differ wildly between a 1,000-row dev table and a 500-million-row production table — always validate with actual execution plans against production-scale data or a restored production backup.
The Query Store, enabled at the database level with ALTER DATABASE ... SET QUERY_STORE = ON, automatically captures plan history and runtime statistics over time, making it far easier to spot plan regressions after a deployment than relying on ad hoc plan captures.
- Execution plans are read right-to-left, bottom-to-top, and show the actual data access strategy chosen by the optimizer.
- Index Seeks are generally efficient; Clustered Index Scans read every row and are costly on large tables.
- Key Lookups indicate a nonclustered index is missing INCLUDE columns needed by the query.
- Hash Match joins often signal a missing useful index; Nested Loops can degrade badly with bad row estimates.
- Non-SARGable predicates (functions wrapping a column) prevent index seeks; rewrite them to preserve seekability.
- Covering indexes with INCLUDE columns eliminate Key Lookups by satisfying the entire SELECT list from the index.
- Query Store captures plan history over time and is the most practical tool for catching plan regressions.
Practice what you learned
1. In which direction should a graphical SQL Server execution plan be read?
2. What does a Key Lookup operator following an Index Seek typically indicate?
3. Why is WHERE YEAR(OrderDate) = 2026 considered non-SARGable?
4. What is the primary purpose of adding INCLUDE columns to a nonclustered index?
5. What does the SQL Server Query Store primarily provide?
Was this page helpful?
You May Also Like
Backup and Restore
How SQL Server backup types, the recovery model, and restore sequences work together to protect data and meet recovery objectives.
SQL Server Agent Jobs
How SQL Server Agent automates scheduled tasks, jobs, steps, schedules, alerts, and operator notifications.
Security and Permissions
The principal-securable-permission model in SQL Server, from logins and users to roles and the principle of least privilege.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics