What is parameter sniffing?
Quick Answer
Parameter sniffing is SQL Server compiling a stored procedure's plan based on the parameter values from the first execution, then caching and reusing that plan. If the first call had an atypical value (e.g. one that matches millions of rows or just one), the cached plan can be terrible for later calls with normal values.
When a stored procedure first runs, SQL Server compiles a plan using the actual parameter values it was passed, then caches that plan and reuses it. That's normally good. The problem is when that first execution had an unusual parameter — say a customer with a million orders, or one with none — the plan gets optimised for that case and then everyone else is stuck with it. The classic symptom is a proc that was fine and suddenly goes slow after a restart or a stats update. Fixes range from OPTION (RECOMPILE) for a fresh plan each time, to OPTIMIZE FOR a typical value, to splitting the query for the skewed cases.
- Symptom: a proc that's usually fast suddenly runs slowly for everyone after a stats update, a server restart, or a plan eviction — because it recompiled off a skewed parameter.
- Options:
OPTION (RECOMPILE)on the statement — a fresh plan every time (CPU cost, but always right for the values).OPTIMIZE FOR (@p = value)orOPTIMIZE FOR UNKNOWN— compile for a typical / average value.- Split into separate procs / branches for the skewed cases.
- Fix the underlying indexing so all values get a good plan.
- It's not a bug — reusing plans is usually a win. It only bites when data is very skewed.