Sqlserver Execution Plans

SQL Server Execution Plans

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)

-- 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
• MAXDOP is 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"

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