Parameter Sniffing

Status: Reviewed (deep-dive v1)
Stack note: Warehouse usp_* boards (SQL-12) compile with the first parameter set — a tiny @SqlInstanceId plan can torture the “all instances” call (or the reverse). Ties to SQL-16/20.
Goal: Spot sniffing, fix with measured options — not cargo-cult RECOMPILE on everything.

Quick chooser

Symptom	Likely sniffing?	First move
Same proc fast in SSMS, slow in Blazor	Often (different params / settings)	Actual plans both ways; QS (SQL-20)
Fast after deploy, slow next morning	Stats + new sniff	Compare plans in QS
Only bad when `@Id IS NULL` (all rows)	Classic	Separate proc or `RECOMPILE` on that path
Always slow	Missing index / blocking — not sniffing	SQL-16 / SQL-18

What’s going on

SQL Server caches a plan optimized for the sniffed parameter values at compile. Skewed estates (one huge instance, many tiny) make one plan a bad fit for others.

Mitigations (pick smallest that works)

Fix the query/index so one plan is good (best)
OPTION (RECOMPILE) on the statement — fresh plan each call; OK for heavy boards called moderately
Local variables — assign params to locals (classic blindfold; can help or hurt)
OPTIMIZE FOR (@p UNKNOWN) / known value — deliberate; document
Split procs — GetBoard vs GetBoardForInstance
QS force plan — temporary bridge only (SQL-20)

-- targeted recompile on the hot statement
SELECT … FROM dbo.Alert a
WHERE (@SqlInstanceId IS NULL OR a.SqlInstanceId = @SqlInstanceId)
OPTION (RECOMPILE);

-- optimize for unknown (density-based)
OPTION (OPTIMIZE FOR UNKNOWN);

Estate habits

[ ] Optional-param boards: watch plans for NULL vs valued
[ ] After stats jobs (SQL-03): glance QS regressed usp_*
[ ] Don’t wrap every proc in WITH RECOMPILE
[ ] Blazor: same SET options as prod (ARITHABORT etc.) when comparing

Tiny lab

Call board with selective id → save plan
Call with NULL (all) → compare
Add OPTION (RECOMPILE) — confirm both acceptable

Done when

[ ] Known sniffy procs listed with chosen mitigation
[ ] Deploy checklist includes QS glance for those procs
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-24 Parameter Sniffing v1