Execution Plans & Query Tuning

An execution plan is the optimiser's recipe for running a query: which indexes to use, in what order to join, how to aggregate. Reading plans is the most leveraged skill in T-SQL performance work.

Estimated vs Actual Plans

In SSMS:

• Estimated — Ctrl+L (Display Estimated Plan) — instant, no execution
• Actual — Ctrl+M to toggle, then Ctrl+E to run — captures the real plan after execution

-- Text-mode plans (legacy but scriptable)
SET SHOWPLAN_TEXT ON;       -- estimated, text only
GO
SELECT * FROM employees WHERE department_id = 50;
GO
SET SHOWPLAN_TEXT OFF;
GO

SET SHOWPLAN_XML ON;        -- estimated, full XML
GO
SELECT * FROM employees WHERE department_id = 50;
GO
SET SHOWPLAN_XML OFF;
GO

STATISTICS IO and TIME

The two essential measurement commands:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT  e.first_name, d.department_name
FROM    hr.employees   e
JOIN    hr.departments d ON d.department_id = e.department_id
WHERE   e.salary > 5000;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

STATISTICS IO reports per-table:

• Logical reads — pages read from buffer cache (the metric that matters most)
• Physical reads — pages read from disk
• Read-ahead reads — speculative pre-fetch

STATISTICS TIME reports CPU time and elapsed time per statement.

Reading a Plan

Plans are read right-to-left, top-to-bottom in graphical view. The right side is the leaf data access; data flows leftward through joins, sorts, and aggregations to the final SELECT operator on the left.

Each operator displays a cost percentage — the optimiser's estimated relative cost. The thickness of the arrows between operators encodes estimated row counts.

Key Operators

Anti-Patterns to Spot

• Key Lookup with high estimated rows → add an INCLUDE to make the index covering.
• Index Scan when you expect a Seek → predicate non-SARGable (function on column, implicit conversion).
• Sort before a window function → leading column of an index doesn't match PARTITION BY / ORDER BY.
• Hash Match on a join with one tiny side → optimiser estimated wrong; check stats.
• Table Spool with many "rebinds" → correlated subquery rerunning per row.

Non-SARGable Predicates

A predicate is SARGable ("Search ARGument") if it allows index seeks. Wrapping the column in a function blocks seeking:

-- NON-SARGable — cannot seek IX_orders_date
SELECT * FROM hr.orders WHERE YEAR(order_date) = 2024;

-- SARGable — index seek possible
SELECT * FROM hr.orders
WHERE  order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- NON-SARGable — implicit conversion blocks seek
SELECT * FROM hr.employees WHERE phone_number = 12345;     -- nvarchar vs int

-- SARGable
SELECT * FROM hr.employees WHERE phone_number = '12345';

Parameter Sniffing

When a stored procedure is first compiled, SQL Server "sniffs" the parameters and builds a plan optimal for those specific values. Subsequent calls reuse the plan even if the parameter values are wildly different — sometimes producing a bad plan.

CREATE PROCEDURE hr.sp_get_orders @customer_id INT
AS
SELECT * FROM hr.orders WHERE customer_id = @customer_id;

If the proc is first called with a customer who has 10 orders, the plan is optimised for 10 rows. Calling later with a customer who has 1,000,000 orders may use the same (now-bad) plan.

Mitigations

-- Force recompile on every call (small overhead, always-fresh plan)
SELECT * FROM hr.orders
WHERE  customer_id = @customer_id
OPTION (RECOMPILE);

-- Tell the optimiser to assume an "average" value
SELECT * FROM hr.orders
WHERE  customer_id = @customer_id
OPTION (OPTIMIZE FOR UNKNOWN);

-- Optimise for a specific value
SELECT * FROM hr.orders
WHERE  customer_id = @customer_id
OPTION (OPTIMIZE FOR (@customer_id = 100));

-- Inside a procedure, copy the param to a local var before use
DECLARE @local_customer_id INT = @customer_id;
SELECT * FROM hr.orders WHERE customer_id = @local_customer_id;

Plan Cache Inspection

Find slow / frequently-run queries:

SELECT  TOP 20
        qs.execution_count,
        qs.total_logical_reads / qs.execution_count   AS avg_logical_reads,
        qs.total_elapsed_time  / qs.execution_count   AS avg_elapsed_us,
        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
ORDER BY avg_logical_reads DESC;

Best Practices

• Capture actual plans for tuning; estimated plans are only useful when actuals would be too slow.
• Check STATISTICS IO first — high logical reads always points to the bottleneck.
• Make every predicate SARGable. Convert literals to the column's type, not vice versa.
• Test for parameter sniffing by running the proc with both extremes of parameter values.
• Use the Query Store (SQL 2016+) for historical plan tracking — it survives plan-cache flushes.

Summary

• Estimated vs Actual plans differ in whether real row counts are recorded.
• Read graphical plans right-to-left; tune for logical reads.
• Watch for non-SARGable predicates, key lookups, and unwanted sorts.
• Parameter sniffing locks in a plan from the first call — combat with OPTION (RECOMPILE) or local variables.