Reading Execution Plans — Identifying Scans, Seeks and Key Lookups

The execution plan is SQL Server’s blueprint for executing a query — it shows every operation (seek, scan, join, sort, aggregate) and its estimated cost as a percentage of the total query cost. Reading execution plans is the most direct path to understanding why a query is slow and what index or rewrite would fix it. The graphical plan in SSMS reads right-to-left (data flows from right to left), with each node’s width representing the volume of rows processed.

Reading Execution Plans #

-- ── Enable actual execution plan ──────────────────────────────────────────
-- SSMS: Ctrl+M or Query menu → Include Actual Execution Plan
-- Then run the query — plan appears in the Execution Plan tab

-- ── I/O and time statistics ───────────────────────────────────────────────
SET STATISTICS IO   ON;   -- shows logical reads per table
SET STATISTICS TIME ON;   -- shows parse, compile and execution time

SELECT p.Id, p.Title, u.DisplayName
FROM   dbo.Posts p
JOIN   dbo.Users u ON u.Id = p.AuthorId
WHERE  p.IsPublished = 1
ORDER  BY p.PublishedAt DESC;

-- Example output:
-- Table 'Posts'. Scan count 1, logical reads 342
-- Table 'Users'. Scan count 1, logical reads 8
-- SQL Server Execution Times: CPU time = 47ms, elapsed time = 52ms

SET STATISTICS IO   OFF;
SET STATISTICS TIME OFF;

-- ── Identify the operation type from the plan ─────────────────────────────
-- Index Seek:    ✅ B-tree navigation to matching rows — fast
--                   Appears when WHERE uses the leading index column(s)
-- Index Scan:    ⚠️  Reads all rows in the index — may be OK for small tables
--                   Appears when no useful index, or optimizer chose scan
-- Table Scan:    ❌  Full table read (heap table, no clustered index)
--                   Always bad for large tables
-- Key Lookup:    ⚠️  Non-clustered index found rows but needs more columns
--                   → Fix by adding INCLUDE columns to the non-clustered index
-- RID Lookup:    ⚠️  Row ID lookup on a heap (no clustered index)

-- ── Diagnose a key lookup ─────────────────────────────────────────────────
-- Query that causes a key lookup:
SELECT p.Id, p.Title, p.Body, p.PublishedAt   -- Body not in the index
FROM   dbo.Posts p
WHERE  p.IsPublished = 1 AND p.CategoryId = 3;
-- Plan: Index Seek (IX_Posts_Category_Date) → Key Lookup (Clustered) for Body

-- Fix: add Body to INCLUDE clause:
CREATE NONCLUSTERED INDEX IX_Posts_Category_Date
ON dbo.Posts (IsPublished, CategoryId, PublishedAt DESC)
INCLUDE (Title, Slug, ViewCount, AuthorId, Body);   -- ← add Body

-- ── Read the missing index recommendation ────────────────────────────────
-- SSMS shows green text in the plan: "Missing Index: CREATE NONCLUSTERED INDEX..."
-- Also visible in sys.dm_db_missing_index_details:
SELECT
    d.statement AS TableName,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    gs.user_seeks,
    gs.avg_total_user_cost * gs.user_seeks AS EstimatedBenefit
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups  g  ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle
ORDER BY EstimatedBenefit DESC;

Common Mistakes #

Mistake 1 — Running estimated plan instead of actual plan (misleading row counts) #

❌ Wrong — using estimated plan (Ctrl+L); shows optimizer’s guesses, not actual rows processed.

✅ Correct — use actual plan (Ctrl+M then run); shows real row counts and reveals statistics problems.

Mistake 2 — Implementing all missing index recommendations (over-indexing) #

❌ Wrong — creating every index the plan recommends; 20 indexes on Posts; write performance collapses.

✅ Correct — evaluate frequency and cost; consolidate overlapping recommendations; add only the highest-value indexes.