Reusing A Plan
Compiling a query plan is expensive, so engines cache the plan for a parameterized query and reuse it for later executions with different parameter values. When the plan is first built, the engine sniffs the actual parameter values supplied to estimate cardinality and pick a plan.
When It Goes Wrong
The sniffed values may be unrepresentative. Suppose a column is skewed: one value matches a handful of rows, another matches millions. If the plan is compiled for the rare value, the engine may choose an index lookup. Reused for the common value, that same plan does millions of random lookups instead of a scan, and performance collapses.
- The first execution shapes the cached plan.
- Later executions with very different selectivity inherit a mismatched plan.
- The symptom is a query that is fast sometimes and slow other times for no obvious reason.
Mitigations
- Force a recompile for the statement so each run gets a fresh plan.
- Use an optimize for hint to compile for a typical value.
- Split the query or use plan guides so distinct value classes get distinct plans.
Key idea
Parameter sniffing builds a cached plan from the first parameter values, which can badly fit later values on skewed data; recompiles and hints restore a plan suited to the real workload.