What does "query plan parameter sniffing" mean in the context of stored procedures, and what issue can it cause?
Correct! Well done.
Incorrect.
The correct answer is B) The optimizer generates and caches an execution plan based on the parameter values from the first execution; if later calls use very different values with different data distributions, the cached plan may be suboptimal for them
Correct Answer
The optimizer generates and caches an execution plan based on the parameter values from the first execution; if later calls use very different values with different data distributions, the cached plan may be suboptimal for them
Because the optimizer "sniffs" the first parameter values to build a plan it then caches and reuses, a plan well-suited for one parameter value (e.g. a very selective filter) may perform poorly for another (e.g. a filter matching most rows), a classic performance pitfall in parameterized stored procedures.