A short reminder, again to get this fresh in my mind....
Parameter Sniffing is when a previously compiled execution plan is reused with different parameter values. This is by design and is generally good as we are saving time by not compiling a new plan. Occasionally though it can lead to suboptimal choices when the procedure is run again, with new parameter values.
If a procedure returns only 1 row with 1 parameter value and 1 million with a different parameter value then the optimal plans for retrieving those rows may be very different.
Here the retrieval of a million rows wold be done using the plan generated for 1 row.
2 ways around parameter sniffing are forcing a recompile (WITH RECOMPILE) in the procedure definition or using the OPTIMIZE FOR query hint.
Simple Talk : Parameter Sniffing
Brent Ozar : Parameter Sniffing
MSDN : Parameter Sniffing & Workarounds
No comments:
Post a Comment