How to Read SQL Server Execution Plans: A Complete Guide from Beginner to Advanced

---

Most SQL Server DBAs and developers know how to open an execution plan. Press Ctrl+M, run the query, click the Execution Plan tab. That part is easy. The hard part is understanding what you are looking at once it opens — what the icons mean, what the numbers tell you, which properties actually matter and which you can ignore, and how to connect what the plan shows to what is making your query slow.

This guide starts from the very beginning — what an execution plan actually is and why SQL Server creates one — and builds through to the advanced topics: reading operator properties in depth, understanding logical reads vs cost percentages, diagnosing the most common performance problems, and proving that an index you added actually helped. Every section is labeled so you can jump to the level relevant to you.

1 What an Execution Plan Actually Is Beginner

When you send a query to SQL Server, SQL Server does not run it immediately. It first builds a plan — a step-by-step set of instructions for how to retrieve the data. It considers multiple different approaches, estimates the cost of each, and chooses the one it believes will be fastest. That plan is the execution plan.

Think of it like a GPS navigation system. You type in your destination (your query), and the GPS considers several possible routes, estimates driving time for each, and presents you with the one it calculates will be fastest. The execution plan is that route — it is SQL Server’s answer to the question “what is the most efficient way to answer this query?”

The plan is made up of operators — individual steps that each do one specific thing: scan a table, seek through an index, join two result sets, sort rows, aggregate values. Each operator receives data from one or more inputs on the right and produces output to the left. The operators connect to form a tree, and together they describe the complete data retrieval process from start to finish.

2 Estimated vs Actual Plans — Which to Use Beginner

There are two types of execution plan: estimated and actual. This distinction trips up beginners constantly.

The estimated plan is generated before the query runs. SQL Server uses statistics — metadata about the distribution of data in your tables — to predict how many rows each operator will process, how much work each step will require, and which approach will be fastest. No data is actually read. The plan is produced in milliseconds. Use estimated plans when you want to understand what SQL Server intends to do without waiting for a long-running query to finish, or when you cannot run the query in production.

The actual plan is generated after the query executes. It contains everything the estimated plan has, plus the real numbers: how many rows each operator actually processed, how many times each operator ran, actual memory usage, and actual warnings triggered during execution. Use actual plans when diagnosing performance problems — the difference between estimated and actual row counts is one of the most important diagnostic signals in the entire plan.

	Estimated Plan	Actual Plan
When generated	Before execution	After execution
Requires query to run	No	Yes
Shows actual row counts	No — estimates only	Yes
Shows actual warnings	Partial	Full
Best use	Quick check, long queries you cannot run	Performance diagnosis
SSMS shortcut	Ctrl+L	Ctrl+M then F5

3 How to Open Plans in SSMS and VS Code Beginner

SSMS — Graphical Plan (Primary Tool)

-- Method 1: Keyboard shortcuts (most common)
-- Estimated plan: Ctrl + L (does NOT execute the query)
-- Actual plan:    Ctrl + M to toggle on, then F5 to execute

-- Method 2: Toolbar buttons
-- Click the "Display Estimated Execution Plan" icon (looks like a diamond)
-- Or click "Include Actual Execution Plan" icon to toggle, then execute

-- Method 3: T-SQL (XML format -- useful for saving plans or automating capture)
SET SHOWPLAN_XML ON;   -- estimated plan as XML
GO
SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderDate > '2025-01-01';
GO
SET SHOWPLAN_XML OFF;
GO

-- Actual plan as XML:
SET STATISTICS XML ON;
GO
SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderDate > '2025-01-01';
GO
SET STATISTICS XML OFF;
GO

Visual Studio Code with the MSSQL Extension (Replaces Azure Data Studio)

The MSSQL extension for VS Code is the Microsoft-recommended replacement for Azure Data Studio. It supports execution plan capture and display, and runs on Windows, macOS, and Linux. Install it by searching SQL Server (mssql) in the VS Code Marketplace.

-- VS Code with MSSQL Extension:
-- Install: search "SQL Server (mssql)" in the VS Code Extensions Marketplace

-- To capture an actual execution plan:
-- 1. Connect to your SQL Server using the MSSQL extension connection panel
-- 2. Write or open your query in a .sql file
-- 3. Right-click in the editor → "Run Query with Actual Plan"
--    OR Command Palette (Ctrl+Shift+P) → "MSSQL: Run Query with Actual Plan"
-- 4. The results pane shows a "Query Plan" tab with a graphical plan viewer
--    and a "Top Operations" panel that ranks operators by cost --
--    useful for quickly spotting the most expensive step in large plans

-- VS Code MSSQL plan features as of 2026:
-- ✓ Graphical execution plan with operator tooltips
-- ✓ Top Operations panel (ranked by cost)
-- ✓ Estimated and actual plan support
-- ✓ Save plan as .sqlplan for sharing
-- ✗ Live Query Statistics (use SSMS for this)
-- ✗ Plan comparison (use SSMS or Query Store)
-- ✗ SQL Server Agent management (use SSMS)

-- For the full execution plan feature set -- plan comparison, live query
-- statistics, Query Store UI, and server administration -- SSMS on Windows
-- remains the more complete tool. VS Code + MSSQL is the right choice for
-- cross-platform development and daily query work on macOS and Linux.

Saving and Loading Plans

-- Save a plan for later comparison or sharing:
-- In SSMS: right-click on the execution plan → Save Execution Plan As...
-- Saves as .sqlplan file (XML format)

-- Load a saved plan:
-- File → Open → File → select the .sqlplan file
-- SSMS renders it identically to a live plan

-- Save the XML directly for programmatic analysis:
-- The XML contains every property of every operator
-- Useful for automated plan regression detection in CI/CD pipelines

4 How to Read the Direction — Right to Left, Top to Bottom Beginner

The single most common confusion beginners have with execution plans is reading direction. Data flows from right to left. The plan executes from right to left. You read it from right to left. The leftmost operator — almost always the SELECT, INSERT, UPDATE, or DELETE — is where the query’s final output is produced. Everything to the right feeds into it.

Imagine a factory assembly line running from right to left. Raw materials enter on the far right (your tables). Each station on the line does some work — cuts, shapes, assembles. The finished product comes off the line on the left. SQL Server’s execution plan is that assembly line. The rightmost operators fetch raw data from storage. Each operator to the left processes it further until the result arrives at the SELECT node on the far left.

-- A simple query and what its plan structure looks like:
SELECT
    o.OrderID,
    o.OrderDate,
    c.CustomerName
FROM dbo.Orders    o
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > '2025-01-01';

-- Plan reads right to left:
--
-- [Index Seek: Orders]  →  [Key Lookup: Orders]  ─┐
--                                                   ├─ [Nested Loops Join] → [SELECT]
-- [Clustered Index Scan: Customers]             ─┘
--
-- Reading order:
-- 1. Far right top:    Index Seek on Orders (finds rows matching OrderDate filter)
-- 2. Next left:        Key Lookup on Orders (fetches OrderID and OrderDate columns)
-- 3. Far right bottom: Clustered Index Scan on Customers (reads all customers)
-- 4. Center:           Nested Loops Join (matches each Orders row to a Customer)
-- 5. Far left:         SELECT (returns results)

5 The Arrow — The Most Important Thing in the Plan Beginner

The arrows connecting operators are not just decorative lines. The thickness of each arrow represents the number of rows flowing between operators. A thick arrow means many rows. A thin arrow means few rows. This is one of the fastest ways to spot a performance problem — if an operator is producing far more rows than you expect, the arrow going into the next operator will be visibly thicker, and you immediately know where excessive data volume is occurring.

Hover over any arrow to see its details. You will see two critical numbers: the estimated number of rows and (in an actual plan) the actual number of rows. When these two numbers are very different — for example, SQL Server estimated 10 rows but actually processed 500,000 — you have a cardinality estimation problem, which is one of the root causes of most bad execution plans.

-- How to interpret arrow thickness:

-- FAT arrow feeding into a join → SQL Server is joining too many rows
-- This often means a missing filter, a bad index, or outdated statistics

-- THIN arrow where you expect fat → rows are being filtered early, which is good
-- Early filtering reduces the work every downstream operator has to do

-- FAT arrow where you expect thin → something upstream is not filtering correctly
-- Check the predicate on the operator feeding that arrow

-- Example: you query for orders from a specific customer
-- Expected: thin arrow (maybe 50 orders for one customer)
-- Actual fat arrow: SQL Server scanned ALL orders first, joined ALL customers,
-- THEN applied the customer filter -- classic missing index problem

-- Hover over the arrow between two operators to see:
-- Estimated Number of Rows: what SQL Server guessed
-- Actual Number of Rows:    what actually flowed (actual plan only)
-- Estimated Row Size:       bytes per row (affects memory grant sizing)
-- Estimated Data Size:      total estimated bytes flowing through this arrow

6 Data Access Operators — Seeks, Scans, and Lookups Beginner

Data access operators are the operators that actually touch your tables and indexes. They are the rightmost operators in most plans and they are where the majority of I/O cost originates. Understanding the difference between them is fundamental to reading plans.

-- Seek: SQL Server goes directly to the right rows using the index B-tree
-- Best outcome for filtered queries
-- Appears when: WHERE clause matches the leading index column

-- Scan: SQL Server reads every row
-- Appears when:
--   1. No index exists for the filter column
--   2. The filter is non-SARGable (function applied to the column)
--   3. Table is small and a scan is cheaper than a seek
--   4. Query retrieves a large percentage of the table

-- Key Lookup: seek found the rows, but extra columns needed a round-trip
-- The most common performance problem after adding an index

-- Diagnosis: when you see a Key Lookup, right-click it and look at:
-- "Output List" in the tooltip -- these are the columns being fetched
-- "Seek Predicates" -- the original index key that found the row location

-- Fix: add the Output List columns to the nonclustered index as INCLUDE columns
-- Before:
CREATE INDEX IX_Orders_Date ON dbo.Orders (OrderDate);
-- After: query needs OrderID and CustomerID, add them as includes
CREATE INDEX IX_Orders_Date ON dbo.Orders (OrderDate)
INCLUDE (OrderID, CustomerID);
-- Key Lookup disappears, logical reads drop dramatically

Seek Predicates vs Predicates — A Critical Distinction

In an Index Seek operator tooltip you will see two separate sections: Seek Predicates and Predicates. These look similar but behave completely differently.

Seek Predicates are applied during the B-tree navigation. SQL Server uses these to navigate directly to the correct position in the index. Only the rows matching the seek predicate are read. This is the efficient path.

Predicates (also called residual predicates) are applied after the rows have already been read from the index. SQL Server read rows that matched the seek predicate, then checked each one against the additional predicate. If many rows pass the seek but few pass the residual predicate, you are reading more rows than necessary.

-- Example: compound index on (OrderDate, CustomerID)
-- Query: WHERE OrderDate > '2025-01-01' AND Status = 'Shipped'

-- Seek Predicate (efficient):   OrderDate > '2025-01-01'  ← uses the index B-tree
-- Predicate (residual):         Status = 'Shipped'        ← filters AFTER reading rows

-- The seek navigated to orders after Jan 1 2025 (fast)
-- Then each row was checked to see if Status = 'Shipped' (slower)
-- If 90% of orders after Jan 1 are not Shipped, you read a lot of extra rows

-- Fix option 1: add Status to the index key
CREATE INDEX IX_Orders_DateStatus ON dbo.Orders (OrderDate, Status);
-- Now Status is part of the seek predicate -- no residual filter needed

-- Fix option 2: add Status as an INCLUDE column
-- Use this if Status is only needed for output, not as a filter
CREATE INDEX IX_Orders_Date ON dbo.Orders (OrderDate)
INCLUDE (Status, CustomerID, OrderTotal);

7 Join Operators — Nested Loop, Hash Match, Merge Join Intermediate

When a query joins two tables, SQL Server chooses one of three physical join algorithms. The choice is made by the query optimizer based on the estimated row counts, available indexes, and whether the data is sorted. Understanding which algorithm was chosen — and why — is essential for diagnosing join performance problems.

Nested Loops Properties — Reading Them Correctly

-- Right-click a Nested Loops operator and select Properties (F4)
-- Key properties to read:

-- Outer Rows: how many rows came from the outer (top) input
-- If this is large, the loop ran many times -- potential performance problem

-- Inner Rows (Executions): how many times the inner input was accessed
-- In a well-tuned nested loop: Outer Rows = Inner Executions

-- Estimated Number of Executions: what optimizer expected
-- Actual Number of Executions:    what actually happened
-- Large gap here = statistics are wrong for the outer table

-- Example interpretation:
-- Outer Rows = 15 (small)
-- Inner Executions = 15
-- Inner Input = Index Seek (indexed)
-- → This is EFFICIENT: 15 seeks into an indexed table -- fast

-- Problem example:
-- Outer Rows = 450,000 (large)
-- Inner Executions = 450,000
-- → This is EXPENSIVE: 450,000 seeks -- even if each is fast, the total is massive
-- Fix: check if the outer table has a filter that is not being applied early enough
--      or consider whether a Hash Join would be chosen with better statistics

Hash Match Properties — Memory Grants and Spills

-- Hash Match tooltip: key things to look for

-- Memory Fractions: fraction of the memory grant allocated to this operator
-- If multiple Hash Match operators share a grant, each gets a fraction

-- Spill Level: appears in actual plans when the hash table ran out of memory
-- Spill Level 1: mild spill to tempdb
-- Spill Level 2: tempdb ran out of space and spilled to a secondary location
-- Any spill is a warning sign -- the memory grant was too small

-- How to diagnose hash spills:
-- 1. Look for the "Spill to TempDB" property in the Hash Match tooltip
-- 2. Run SET STATISTICS IO ON before executing -- tempdb logical reads appear
-- 3. Check for RESOURCE_SEMAPHORE_QUERY_COMPILE wait type in sys.dm_exec_requests

-- How to fix hash spills:
-- Option 1: add or improve an index so the optimizer can use Merge or Loop instead
-- Option 2: update statistics so the optimizer allocates a larger memory grant
-- Option 3: split the query into steps with temp tables to control row counts
-- Option 4 (last resort): OPTION (MIN_GRANT_PERCENT = n) hint

When Each Join Algorithm Is Chosen

Algorithm	Best When	Watch Out For
Nested Loops	Outer input is small (under ~1,000 rows). Inner input has an index on the join key.	Outer input is actually large — estimated rows were wrong. Each inner lookup becomes expensive at scale.
Hash Match	Large inputs, no sort order, no useful indexes. One input fits in memory for the build phase.	Memory grant too small causes tempdb spill. Spill Level shows in actual plan tooltip.
Merge Join	Both inputs are large and sorted on the join key. Clustered index order matches the join.	If inputs are not already sorted, an explicit Sort operator appears before the join — which costs memory and CPU.

8 Other Operators You Will Encounter Intermediate

9 The Tooltip — What Each Property Means Intermediate

Hover over any operator in SSMS to see its tooltip. For a full property list press F4 with an operator selected to open the Properties window. Here is what each property means and which ones actually matter for diagnosis.

Property	What It Means	Use It When
Physical Operation	The actual algorithm used: Clustered Index Seek, Hash Match, Nested Loops, etc.	Always — this tells you what SQL Server is actually doing.
Logical Operation	The conceptual operation: Inner Join, Left Anti Semi Join, Aggregate, etc.	Useful for understanding the query intent. Left Anti Semi Join = NOT EXISTS pattern.
Estimated Number of Rows	How many rows the optimizer predicted this operator would process.	Compare to Actual Number of Rows. Large gaps = stale statistics or data skew.
Actual Number of Rows	How many rows this operator actually processed. Actual plans only.	The ground truth. Always compare to estimated. This is fact; estimated is a guess.
Number of Rows Read	How many rows were read from storage before filtering. Often higher than rows output.	Shows how many extra rows were read and discarded. High value with low output rows = bad filter pushdown.
Estimated I/O Cost	The optimizer’s estimate of I/O work for this operator. Unitless relative number.	Use for relative comparison only. Do not treat as milliseconds or bytes.
Estimated CPU Cost	The optimizer’s estimate of CPU work for this operator.	Combined with I/O cost to produce subtree cost. Relative comparison only.
Estimated Subtree Cost	Cumulative cost of this operator and everything to its right.	Useful for identifying where most cost accumulates in the plan tree.
Output List	The columns this operator passes to the next operator on its left.	Critical for diagnosing Key Lookups — the Output List shows which columns caused the lookup.
Seek Predicates	Conditions applied during the index B-tree navigation.	Shows what the index is actually filtering on. More predicates here = more efficient seek.
Predicates (Residual)	Conditions applied after rows are read from storage.	Rows matching this were read unnecessarily. More predicates here = wasted I/O.
Actual Rebinds / Rewinds	How many times the operator restarted (rebind) or replayed from cache (rewind). Nested Loops inner input only.	Rebind count = number of outer rows. High rebind count on a slow inner input = join performance problem.
Memory Grant	KB of memory allocated to this operator for sort buffers or hash tables.	If grant is small and data is large, spills to tempdb. Check for Spill Level property.

10 Logical Reads vs Cost Percentages — Which to Trust Intermediate

This is one of the most important and most misunderstood concepts in execution plan analysis. The cost percentages shown on each operator — “Table Scan 78%” for example — look authoritative. They are not. They are the optimizer’s estimates, calculated before the query ran, based on statistics that may be outdated. They are guesses, not measurements.

Logical reads, on the other hand, are measurements. They count exactly how many 8 KB data pages SQL Server read from the buffer cache to execute the query. Every logical read represents real work the engine performed. They do not lie. A query with 50,000 logical reads did exactly 50,000 page reads regardless of what the cost percentages suggest.

-- Enable logical read output before running your query
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    o.TotalAmount
FROM dbo.Orders    o
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > '2025-01-01'
ORDER BY o.OrderDate DESC;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- Output in the Messages tab:
-- Table 'Orders'.    Scan count 1, logical reads 45127, physical reads 0,
--                    read-ahead reads 0
-- Table 'Customers'. Scan count 1, logical reads 312,   physical reads 0,
--                    read-ahead reads 0
-- SQL Server Execution Times:
--    CPU time = 1250 ms, elapsed time = 1834 ms

Reading the STATISTICS IO Output

Value	What It Means	What to Do With It
logical reads	Pages read from the buffer cache. This is the primary performance metric. Each page is 8 KB.	This is your baseline. After adding an index or rewriting the query, compare this number. Lower is better.
physical reads	Pages read from disk because they were not in the buffer cache. High on first run, near-zero after data is cached.	Run the query twice and use the second run’s numbers for performance comparison — first run may include cold cache overhead.
read-ahead reads	Pages SQL Server pre-fetched speculatively before they were needed. Common during large scans.	High read-ahead with high logical reads confirms a scan is occurring — not just a seek.
scan count	How many times this table or index was accessed. More than 1 usually indicates it was accessed once per outer row in a loop.	Scan count of 10,000 on the inner table of a Nested Loop = 10,000 index seeks — investigate the outer input size.
CPU time	Milliseconds of CPU used. High CPU with low logical reads = sorting, hashing, or complex computation.	Compare before and after query changes. CPU time and elapsed time both matter.
elapsed time	Total wall-clock milliseconds. Includes waits. Elapsed significantly exceeding CPU = waits (I/O, locks, memory).	If elapsed >> CPU, the query is waiting for something. Check wait statistics alongside the plan.

11 Estimated vs Actual Rows — The Cardinality Gap Intermediate

Every operator in an execution plan has two row counts: the estimated rows (what the optimizer predicted) and the actual rows (what really happened). The gap between these two numbers — called the cardinality estimation error — is the root cause of most bad execution plans. When the optimizer thinks a table has 100 matching rows but it actually has 2 million, it makes the wrong algorithm choices: it picks Nested Loops when it should pick Hash Match, allocates a memory grant that is too small and spills to tempdb, or builds an inefficient join order.

-- How to spot cardinality estimation problems:

-- In the actual execution plan:
-- 1. Hover over any operator to see both estimated and actual row counts
-- 2. Look for operators where Actual Rows >> Estimated Rows (severe under-estimate)
--    This causes the optimizer to undersize memory grants and choose wrong algorithms
-- 3. Look for operators where Estimated Rows >> Actual Rows (severe over-estimate)
--    This causes the optimizer to choose Hash Join when Nested Loop would be faster

-- Threshold rule of thumb:
-- 10x difference: investigate
-- 100x difference: almost certainly causing a bad plan
-- 1000x+ difference: definitely causing a bad plan

-- Common causes of cardinality estimation errors:
-- 1. Stale statistics (update hasn't run since large data changes)
-- 2. Skewed data distribution (most customers are in one state, but statistics show even distribution)
-- 3. Ascending key problem (new rows added beyond the range of existing statistics)
-- 4. Multiple filters (optimizer multiplies selectivities independently -- can be very wrong)
-- 5. User-defined functions (optimizer cannot see inside UDFs -- assumes 1 row output always)

-- Check statistics age:
SELECT
    sp.stats_id,
    s.name            AS StatName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.Orders')
ORDER BY sp.last_updated DESC;

-- Update stale statistics manually:
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
-- WITH FULLSCAN reads every row -- most accurate but most expensive
-- Default uses a sample -- good enough for most tables

12 Warnings — The Yellow Triangle Beginner

Yellow warning triangles on operators are SQL Server explicitly flagging a problem. Never ignore them. They are the optimizer’s way of saying “something went wrong here and you should know about it.”

Warning Type	What It Means	How to Fix It
Missing Index	SQL Server identified an index that would improve this query. Shown on the SELECT node, not the scan operator.	Review the suggestion but do not blindly create it. The suggestion does not account for write overhead or your full workload. Evaluate it, test it, then decide.
Implicit Conversion	SQL Server is converting one data type to another because the query predicate type does not match the column type. This often prevents index use entirely.	Match data types. If the column is VARCHAR and you pass an NVARCHAR literal (N'value'), the index cannot be used. Fix the application code or the column type.
Spill to TempDB	A Sort or Hash Match operator ran out of its memory grant and wrote intermediate data to tempdb. Significant performance impact.	Update statistics so the optimizer allocates a larger grant. Add indexes to eliminate the sort. For Hash Match, consider whether the join can be converted to a Merge Join with sorted inputs.
No Statistics	SQL Server has no statistics on a column used in a filter or join. The optimizer is guessing cardinality blindly.	Create statistics: CREATE STATISTICS stat_name ON table(column). Enable auto-create statistics if it is off (it should be on by default).
Unmatched Index	A query hint referenced an index that does not exist or cannot be used. The query ran without the hinted index.	Remove the invalid hint. Never rely on index hints in production code — indexes can be dropped or renamed.

-- Check for implicit conversion warnings programmatically
-- (useful for finding them in cached plans without running each query manually)

SELECT
    qs.total_logical_reads,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)   st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))
      LIKE '%PlanAffectingConvert%'   -- implicit conversion warning in plan XML
ORDER BY qs.total_logical_reads DESC;

13 SET STATISTICS IO — Reading Logical Read Output Beginner

You know how to read the numbers from Section 10. Here is how to use them in practice — specifically, how to establish a baseline before making any changes and how to measure the improvement after.

-- STEP 1: Always establish a baseline BEFORE making any changes
-- Run the query twice -- use the second run to avoid cold cache effects

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Your slow query here
SELECT
    o.OrderID,
    o.OrderDate,
    o.TotalAmount,
    c.CustomerName
FROM dbo.Orders    o
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > '2025-01-01'
AND   o.Status    = 'Pending';
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- Record from Messages tab:
-- BASELINE:
-- Orders:    logical reads 47,382
-- Customers: logical reads 312
-- CPU time:  1,456 ms
-- Elapsed:   2,103 ms

-- STEP 2: Look at the execution plan (Ctrl+M then F5)
-- Identify the operator with the most cost / thickest arrow / scan instead of seek
-- In this example: Orders has a Clustered Index Scan -- no usable index on OrderDate + Status

-- STEP 3: Check the missing index suggestion (yellow triangle on SELECT node)
-- SSMS shows: Missing Index Impact = 87.4%
-- Suggested: CREATE NONCLUSTERED INDEX [missing_index] ON dbo.Orders (Status, OrderDate)
--            INCLUDE (CustomerID, TotalAmount)

-- STEP 4: Create the index
CREATE NONCLUSTERED INDEX IX_Orders_StatusDate
ON dbo.Orders (Status, OrderDate)
INCLUDE (CustomerID, TotalAmount);

-- STEP 5: Re-run the query and compare
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT
    o.OrderID,
    o.OrderDate,
    o.TotalAmount,
    c.CustomerName
FROM dbo.Orders    o
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > '2025-01-01'
AND   o.Status    = 'Pending';
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- AFTER:
-- Orders:    logical reads 94    ← down from 47,382 (99.8% reduction)
-- Customers: logical reads 312
-- CPU time:  8 ms
-- Elapsed:   12 ms

-- The execution plan now shows:
-- Orders:    Index Seek (was Clustered Index Scan) -- correct
-- Key Lookup: GONE (CustomerID and TotalAmount are in INCLUDE columns)
-- Result:    the index eliminated both the scan AND the key lookup

14 Proving Your Index Worked — Before and After Beginner

Creating an index and assuming it helped is a mistake many developers make. The optimizer may not use your new index, may use it differently than you expected, or your query may have other bottlenecks that the index does not address. Always verify the improvement with measurements.

-- Three-point verification that your index is working:

-- 1. Logical reads dropped significantly
-- Before: 47,382 | After: 94
-- This is the most reliable measurement

-- 2. The execution plan shows Index Seek instead of Clustered Index Scan
-- Right-click the operator and check Physical Operation = "Index Seek"
-- Check "Object" property shows your new index name

-- 3. Key Lookup is gone from the plan
-- If you added INCLUDE columns, the Key Lookup should disappear entirely
-- If it is still there, check the Output List on the Key Lookup to see what column is still missing

-- Bonus: verify the index is being used in production (not just in your test)
-- Check sys.dm_db_index_usage_stats after deploying to production
SELECT
    i.name              AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes              i
JOIN sys.dm_db_index_usage_stats ius
    ON ius.object_id = i.object_id
    AND ius.index_id = i.index_id
WHERE i.object_id = OBJECT_ID('dbo.Orders')
AND ius.database_id = DB_ID()
ORDER BY ius.user_seeks DESC;
-- user_seeks should be climbing if your index is being used for seek operations
-- If user_seeks = 0 and user_scans is high, the index is being scanned not seeked -- investigate why

15 The Most Common Problems and What They Look Like Intermediate

Problem 1: Key Lookup (Most Common)

You see: Index Seek → Key Lookup → Nested Loops with a thick arrow on the Key Lookup side. The nonclustered index found the rows but had to make a second trip back to the clustered index for additional columns. High logical reads on the Key Lookup.

Fix: add the columns in the Key Lookup’s Output List to the nonclustered index as INCLUDE columns.

Problem 2: Implicit Type Conversion Kills the Index

-- The column is VARCHAR(20) but the application sends NVARCHAR
CREATE TABLE dbo.Products (ProductCode VARCHAR(20) PRIMARY KEY);

-- Query with implicit conversion (application sends NVARCHAR literal):
SELECT * FROM dbo.Products WHERE ProductCode = N'ABC-001';
-- SSMS plan shows: Clustered Index SCAN (not Seek) + yellow warning
-- The CONVERT_IMPLICIT in the predicate prevents index navigation

-- Fix: match data types
SELECT * FROM dbo.Products WHERE ProductCode = 'ABC-001';  -- VARCHAR literal, no N prefix
-- Or fix the column: ALTER TABLE dbo.Products ALTER COLUMN ProductCode NVARCHAR(20)

Problem 3: Non-SARGable Predicate (Function on Column)

-- Non-SARGable: function applied TO the column -- index cannot be used
-- BAD: YEAR() applied to the column prevents index seek
SELECT * FROM dbo.Orders WHERE YEAR(OrderDate) = 2025;
-- Plan: Clustered Index Scan -- SQL Server must evaluate YEAR() for every row

-- SARGable: filter expressed as a range directly on the column
-- GOOD: range predicate on the column -- index can seek directly
SELECT * FROM dbo.Orders WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';
-- Plan: Index Seek -- SQL Server navigates directly to the date range

-- Other common non-SARGable patterns:
-- WHERE LEFT(CustomerName, 3)  = 'Smi'    -- function on column
-- WHERE CustomerID + 1         = 101      -- arithmetic on column
-- WHERE ISNULL(Status, 'X')    = 'Active' -- ISNULL wrapping the column
-- WHERE CONVERT(VARCHAR, OrderDate, 101) = '01/15/2025' -- CONVERT on column

Problem 4: Sort Appearing Before a Join

-- Symptom: Sort operator with high cost before a Merge Join
-- Cause: Merge Join requires sorted inputs -- SQL Server added a Sort because no sorted index exists
-- This Sort requires a memory grant and may spill to tempdb

-- The plan looks like:
-- [Index Scan: Orders] → [Sort by CustomerID] ─┐
--                                               ├─ [Merge Join] → [SELECT]
-- [Index Scan: Customers sorted by CustomerID]─┘

-- Fix: create an index sorted by the join column
-- This allows SQL Server to use Index Seek + Merge Join without the explicit Sort
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID) INCLUDE (OrderDate, TotalAmount);
-- Now Orders is delivered in CustomerID order -- Sort operator disappears

Problem 5: Table Spool from Correlated Subquery

-- Symptom: Table Spool in the plan, often with high cost
-- Cause: correlated subquery executes once per outer row -- SQL Server spools to avoid re-execution

-- BAD (correlated subquery):
SELECT
    OrderID,
    OrderDate,
    (SELECT SUM(Amount) FROM dbo.OrderLines ol WHERE ol.OrderID = o.OrderID) AS LineTotal
FROM dbo.Orders o;
-- Plan: Table Spool appears -- recalculates subquery for every order row

-- GOOD (rewrite as JOIN or GROUP BY):
SELECT
    o.OrderID,
    o.OrderDate,
    SUM(ol.Amount) AS LineTotal
FROM dbo.Orders     o
JOIN dbo.OrderLines ol ON ol.OrderID = o.OrderID
GROUP BY o.OrderID, o.OrderDate;
-- Plan: Hash Match Aggregate -- no Table Spool, much lower total cost

16 Advanced: Comparing Two Plans Side by Side Advanced

SSMS 18 and later supports opening two execution plan files side by side for direct comparison. This is the right tool when you want to confirm that a code change or index improved a plan across your whole team's understanding, not just your own.

-- Method 1: Save both plans as .sqlplan files and compare in SSMS
-- Run the query BEFORE your change with Ctrl+M enabled
-- Right-click plan → Save Execution Plan As → baseline.sqlplan

-- Make your change (add index, rewrite query, update statistics)

-- Run the query again with Ctrl+M enabled
-- Right-click plan → Save Execution Plan As → after_change.sqlplan

-- In SSMS: File → Open → Compare Showplan
-- Select baseline.sqlplan as Plan 1, after_change.sqlplan as Plan 2
-- SSMS highlights differences between the plans

-- Method 2: Capture plans from Query Store for production comparison
-- Query Store stores both before and after plans for the same query
SELECT
    qsq.query_id,
    qsp.plan_id,
    qsrs.avg_logical_io_reads,
    qsrs.avg_duration,
    qsrs.count_executions,
    TRY_CONVERT(XML, qsp.query_plan) AS query_plan
FROM sys.query_store_query           qsq
JOIN sys.query_store_plan            qsp  ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats   qsrs ON qsrs.plan_id = qsp.plan_id
WHERE qsq.query_hash = 0x...  -- replace with the query hash of your slow query
ORDER BY qsrs.avg_logical_io_reads DESC;

17 When NOT to Use the Execution Plan Advanced

Execution plans are powerful but they are not the right diagnostic tool for every performance problem. Knowing when to use other tools first saves significant time.

• When the query is blocking, not slow. A query that is blocked by a lock will show a normal-looking execution plan. The problem is not the plan — it is the wait. Use sys.dm_exec_requests and sys.dm_os_waiting_tasks to diagnose blocking, not execution plans.
• When a small table is involved. If the table in question has fewer than 10,000 rows, do not spend time optimizing the execution plan. A full table scan on a small table completes in microseconds. The plan is not your bottleneck — look elsewhere.
• When the problem is a memory grant wait. RESOURCE_SEMAPHORE waits mean queries are queuing for memory before they even start executing. The plan itself may look fine — the problem is server-level memory pressure, not individual query plan choices.
• When the query runs fine in isolation but is slow under load. The plan may be perfectly tuned. The problem may be CPU saturation, I/O bottleneck, or contention from concurrent queries. Check wait statistics and server-level metrics before concluding the plan needs improvement.
• When statistics are extremely stale. If statistics have not been updated after a large data load, the execution plan shown is based on wrong estimates. Update statistics first, then re-examine the plan. You may find the "bad plan" fixes itself once the optimizer has accurate data.

References

• Grant Fritchey — SQL Server Execution Plans (Red Gate Books, free PDF)
• Hugo Kornelis — SQL Server Execution Plans: Introduction
• Microsoft Docs — Display an Actual Execution Plan
• Microsoft Docs — Azure Data Studio Retirement and Migration to VS Code (February 28, 2026)
• VS Code Marketplace — SQL Server (MSSQL) Extension
• Microsoft Docs — Execution Plans Overview
• Microsoft Docs — sys.dm_exec_query_stats
• Microsoft Docs — Query Store Overview
• Microsoft Docs — Statistics in SQL Server
• Brent Ozar — Execution Plan Training Materials
• SQLYARD — SQL Server Performance Tuning: The Complete DBA and Developer Guide
• SQLYARD — Understanding Parameter Sniffing in SQL Server