Roadmap › SQL Server & T-SQL › Module: Indexes and Performance
Lessons in this course (19)
16. Statistics, parameter sniffing and Query Store
Advanced★ Must knowLesson 16 of 19
- Statistics — histograms of value distribution that the optimizer uses to estimate row counts. Auto-created/updated; refresh with
UPDATE STATISTICSor maintenance jobs. - Parameter sniffing — the first execution's parameter values shape the cached plan; a plan good for a small customer can be terrible for a huge one. Fixes:
OPTION (RECOMPILE),OPTIMIZE FOR (@p UNKNOWN)or a specific value, splitting procs, plan forcing in Query Store, SQL 2022's Parameter Sensitive Plan optimisation. - Query Store (2016+) — records queries, plans and runtime stats over time; find regressed queries and force a known-good plan.
- Index maintenance — fragmentation matters less on SSDs; prioritise statistics updates. Rebuild (offline/online) vs reorganise.