Lessons in this course (11)
5. Plan cache, parameter sniffing and Query Store
Compiled plans are stored in the plan cache keyed by the exact query text (and SET options). Parameterised queries (stored procedures, sp_executesql, ADO.NET/EF/Dapper parameters) are compiled once and reused.
Parameter sniffing: the first execution's parameter values are used to optimise the plan; later executions with very different values reuse it. Example: @CustomerId = 1 (10 rows) gets a seek + key lookup plan; then a big customer with 2 million rows reuses it → millions of lookups → very slow.
| Fix | Trade-off |
|---|---|
OPTION (RECOMPILE) | Fresh plan every execution — CPU cost; great for rare, varied reports |
OPTION (OPTIMIZE FOR (@p = value / UNKNOWN)) | Plan for typical/average values |
| Better index (covering) | Makes the plan good for all values — best fix |
| Split procedures by case | More code |
| Query Store plan forcing | Pins a known good plan |
| SQL Server 2022 PSP optimisation | Automatically keeps multiple plans per parameter range |
-- Top CPU queries currently in cache
SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu, qs.execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 200) 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 qs.total_worker_time DESC;Query Store (database-level flight recorder): stores queries, plans and runtime statistics over time; shows regressed queries after a deployment or stats update; lets you force a previous good plan. Enabled by default for new databases in SQL Server 2022 and in Azure SQL.
Plan cache pollution: ad-hoc queries with literals (WHERE Id = 1, WHERE Id = 2 …) each get their own plan — use parameters; consider optimize for ad hoc workloads.