What is parameter sniffing, and how do you deal with it?
Quick Answer
SQL Server compiles a stored procedure's plan using the first parameter values it sees (“sniffs”) and caches it. If data is skewed, that plan can be great for those values and terrible for others — e.g. a plan built for a customer with 5 orders runs for a customer with 5 million. Fixes range from OPTION (RECOMPILE) to OPTIMIZE FOR hints to rewriting the query.
When a stored procedure first runs, the optimizer looks at the actual parameter values, builds a plan tuned for them, and caches it. That's usually good, but if your data is lopsided it backfires: the cached plan was built for a rare value that returns a handful of rows, and now it's being reused for a common value that returns millions, so it picks nested loops and a lookup instead of a scan. Symptoms are “it's fast for months then suddenly slow after a stats update or restart.” Fixes: OPTION (RECOMPILE) if compilation cost is acceptable, OPTIMIZE FOR a typical value, or split the proc so each path gets its own plan. 2022 added automatic multi-plan handling for this.
OPTION (RECOMPILE)— fresh plan every call; best per-call plan, more CPU to compile.OPTIMIZE FOR (@p = value)orOPTIMIZE FOR UNKNOWN— compile for a representative/average value.- Split divergent code paths into separate procedures; copy parameters to local variables (disables sniffing, uses density estimate).
- SQL Server 2022 “Parameter Sensitive Plan optimization” caches multiple plans for one statement.