Execution Plans and Query Tuning

35.1 Getting Plans

Estimated plan (Ctrl+L) vs actual plan (Ctrl+M). Live Query Statistics.

SET SHOWPLAN_XML ON;     -- estimated
SET STATISTICS XML ON;   -- actual
SET STATISTICS IO, TIME ON;
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);
SELECT * FROM sys.dm_exec_query_plan_stats(@plan_handle);  -- last actual plan (2019+)

35.2 Reading Plans

Read right to left, top to bottom. Data flows right to left.

Check: estimated vs actual rows, warnings (yellow triangles), operator costs, thick arrows, memory grants, parallelism.

35.3 Key Operators

Table Scan / Clustered Index Scan Reads the whole table Navigates the B-tree. Efficient.

Key Lookup / RID Lookup Fetches missing columns Nested Loops / Merge / Hash Match Expensive. Can spill to tempdb.

Hash Match (Aggregate) / Stream Aggregate Spool (Table/Index/Row Count)

Temporary storage of intermediate results Parallelism (Gather/Repartition/Distribute Streams)

Compute Scalar, Filter, Top, Concatenation Runtime choice between hash and NL Columnstore and batch mode on rowstore

35.4 Common Performance Problems and Fixes

Scans, high reads Create an appropriate index Scan instead of seek Rewrite the predicate CONVERT_IMPLICIT warning Match data types Bad cardinality estimates Est. ≠ actual by orders of magnitude Update stats, rewrite, temp tables Parameter sniffing Fast for some params, slow for others

OPTION (RECOMPILE), OPTIMIZE FOR, PSP optimization (2022), plan guides, Query Store forcing Spills to tempdb Sort/Hash warnings Fix estimates, memory grant feedback CXPACKET/CXCONSUMER waits

Tune MAXDOP and Cost Threshold for Parallelism Scalar UDFs / MSTVFs Inline them, rewrite as iTVF Extra I/O, no covering Select only the needed columns UNION ALL, index union Large IN lists TVP or temp table

35.5 Parameter Sniffing in Detail

-- Problem: plan compiled for a rare value is reused for a common value
CREATE PROC dbo.usp_OrdersByStatus @Status VARCHAR(20) AS
SELECT * FROM dbo.Orders WHERE Status = @Status;

-- Fixes
... OPTION (RECOMPILE);
... OPTION (OPTIMIZE FOR (@Status = 'Pending'));
... OPTION (OPTIMIZE FOR UNKNOWN);
-- SQL 2022: Parameter Sensitive Plan (PSP) optimization creates multiple plan variants automatically

35.6 Query Hints

Hints are a last resort. They override the optimizer permanently. Prefer Query Store hints (2022) and plan forcing.

OPTION (MAXDOP 1), OPTION (RECOMPILE), OPTION (HASH JOIN), OPTION (FORCE ORDER),
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')), OPTION (QUERYTRACEON 9481),
OPTION (MAX_GRANT_PERCENT = 10), OPTION (FAST 10), OPTION (KEEPFIXED PLAN)
-- Table hints: WITH (INDEX(IX_name)), WITH (FORCESEEK), WITH (FORCESCAN)

35.7 Plan Cache

Ad hoc vs prepared vs procedure plans. Optimize for ad hoc workloads setting. Forced and simple parameterization.

SELECT TOP (20) qs.execution_count, qs.total_worker_time / qs.execution_count AS avg_cpu,
       qs.total_logical_reads / qs.execution_count AS avg_reads, st.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 qs.total_worker_time DESC;

DBCC FREEPROCCACHE (@plan_handle);                       -- one plan
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; -- one database

35.8 Tuning Methodology

Identify the top resource consumers (Query Store, DMVs, wait stats).

Capture the actual plan and STATISTICS IO/TIME.

Find the most expensive operator or the biggest estimate mismatch.

Fix indexes, statistics, query rewrites, or schema.

Measure before and after. Change one thing at a time.

Watch for regressions.