Execution plans show exactly how SQL Server will execute (or did execute) a query. Reading them is the fastest way to diagnose why a query is slow.
Capturing Plans
Method 1: Statistics IO + Time (lightweight, always available)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- Your query here
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET STATISTICS IO OFF; SET STATISTICS TIME OFF;
**Output interpretation:**
Table 'Orders'. Scan count 1, logical reads 1247, physical reads 0, ... SQL Server Execution Times: CPU time = 312 ms, elapsed time = 445 ms.
- **logical reads** — pages read from buffer pool (in-memory). High = too much data touched.
- **physical reads** — pages read from disk (not in buffer). High = buffer pool cold or too small.
- **CPU time** — pure computation. High = complex calculations or large in-memory sort.
- **elapsed time** — wall clock. Always >= CPU time. Gap = waiting (I/O, locks, etc.)
---
### Method 2: XML Execution Plan (estimated)
```sql
-- Estimated plan — does NOT execute the query
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET SHOWPLAN_XML OFF;
Method 3: Actual Execution Plan
-- Actual plan — executes the query and captures real row counts
SET STATISTICS XML ON;
GO
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET STATISTICS XML OFF;
The XML result contains the full plan. Paste it into SSMS or SentryOne Plan Explorer for visual rendering.
Method 4: Pull Plan from Cache
-- Find a cached plan by query text substring
SELECT TOP 5
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.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) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%Orders%' -- adjust filter
ORDER BY avg_elapsed_ms DESC;
Click the query_plan result to open the graphical plan in SSMS.
Key Operators — What to Look For
See references/plan-operators.md for the full reference table.
🔴 Bad: Table Scan
Operator: Table Scan (or Clustered Index Scan on large table)
Cost: HIGH — reads every row in the table
Fix: Add a non-clustered index with appropriate key columns
A table scan is acceptable only on small tables (< ~1000 rows). On large tables, it's almost always a missing index.
🔴 Bad: Key Lookup (Bookmark Lookup)
Operator: Key Lookup
Cost: MEDIUM–HIGH per row — two B-tree traversals per row
Fix: Add INCLUDE columns to the non-clustered index to cover the SELECT list
-- You have: CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID)
-- Query needs: CustomerID (seek), OrderDate, Status, TotalAmount (lookup to clustered)
-- Fix:
CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID)
INCLUDE (OrderDate, Status, TotalAmount);
🟡 Warning: Index Scan (non-clustered, large table)
Operator: Index Scan
Cost: MEDIUM — reads all or a large portion of the index leaf level
Fix: Check if a predicate is non-SARGable; verify index column order
🟡 Warning: Hash Match (with memory spill warning)
Operator: Hash Match (Join or Aggregate)
Symptom: Spill to TempDB warning icon, high memory grant
Fix: Add index to eliminate hashing; reduce row counts entering the join
Hash Match joins are not inherently bad — they're appropriate for large un-indexed joins. The problem is memory spills: when the hash table doesn't fit in the granted memory, SQL Server spills to TempDB — catastrophic for performance.
🟡 Warning: Sort (large, or causing spill)
Operator: Sort
Symptom: High cost percentage; spill warning
Fix: Add an index whose key order matches the ORDER BY to eliminate the sort
🟢 Good: Index Seek
Operator: Index Seek
Cost: LOW — navigates directly to relevant rows
This is what you want. The optimizer used the index B-tree to find exactly the rows needed.
ℹ️ Parallelism (Exchange Operators)
Operators: Distribute Streams, Gather Streams, Repartition Streams
Context: Query is executing in parallel
Related wait: CXPACKET, CXCONSUMER
Parallelism is not inherently bad. Issues arise when:
- One thread finishes early and waits for others (CXPACKET skew)
- Query is too small to justify parallelism overhead
MAXDOPis too high for the workload
-- Force single-threaded execution for testing
SELECT * FROM Orders WHERE CustomerID = 42
OPTION (MAXDOP 1);
Row Estimate vs Actual Rows
One of the most important things to check: does SQL Server's estimate match reality?
In SSMS: Hover over any operator — "Estimated Number of Rows" vs "Actual Number of Rows"
| Discrepancy | Meaning | Fix |
|---|---|---|
| Estimate = 1, Actual = 50,000 | Severely underestimated — bad plan (wrong join strategy, missing index) | Update statistics, check for parameter sniffing |
| Estimate = 50,000, Actual = 1 | Overestimated — wasteful grants, parallel plan where serial is better | Update statistics |
| Within 2-3x | Acceptable | — |
Update statistics:
-- Update all statistics on a table
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
-- Update all statistics in the database
EXEC sp_updatestats;
-- Check statistics age
SELECT
OBJECT_NAME(s.object_id) AS table_name,
s.name AS stat_name,
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 OBJECT_NAME(s.object_id) = 'Orders'
ORDER BY sp.modification_counter DESC;
Parameter Sniffing
SQL Server compiles a stored procedure's plan on first execution using the parameter values at that time. If those values aren't representative, all subsequent executions use a bad plan.
Symptom: A stored procedure is fast when run directly but slow when called from the application (or vice versa).
Diagnose:
-- Check the cached plan and parameters it was compiled for
SELECT
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms,
qp.query_plan
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
JOIN sys.dm_exec_query_stats qs ON ps.plan_handle = qs.plan_handle
WHERE OBJECT_NAME(ps.object_id) = 'usp_GetOrders';
-- Look at the plan — was it compiled for a selective or non-selective parameter value?
Fixes (in order of preference):
-- Option 1: OPTIMIZE FOR UNKNOWN — use average statistics, not sniffed values
CREATE PROCEDURE usp_GetOrders @CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));
GO
-- Option 2: OPTION (RECOMPILE) — recompile on every execution (CPU cost)
CREATE PROCEDURE usp_GetOrders @CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);
GO
-- Option 3: Local variable (breaks sniffing — not always ideal)
CREATE PROCEDURE usp_GetOrders @CustomerID INT
AS
DECLARE @LocalCustomerID INT = @CustomerID;
SELECT * FROM Orders WHERE CustomerID = @LocalCustomerID;
GO
-- Option 4: Clear the procedure cache (nuclear, use carefully in production)
DBCC FREEPROCCACHE;
Reference
- Plan Operators Reference — Full operator table with cost, trigger conditions, and fixes