Parameter Sniffing - SQL Server Query Performance Tuning - page 310

Figure 16-6. Part of the histogram showing how many rows you can expect

You can see that the value of London returns a lot more rows than any of the average rows displayed in
AVG_RANGE_ROWS , and it's higher than many of the other steps RANG_HI_KEY counts that are stored in EQ_ROWS .
In short, the value for London is skewed from the rest of the data. That's why the plan there is different from others.
You'll have to go through the same sort of evaluation of the statistics and compile-time parameter values in order
to understand where bad parameter sniffing is coming from.
But, if you have a parameterized query that is suffering from bad parameter sniffing, you can take control in
several different ways to attempt to reduce the problem.

Mitigating Bad Parameter Sniffing
Once you've identified that you're experiencing bad parameter sniffing in one case, you don't just have to suffer
with it. You can do something about it, but you have to make a decision. You have several choices for mitigating the
behavior of bad parameter sniffing.

• You can force a recompile of the plan at the time of execution by running

sp_ recompile

against the procedure prior to executing.

• Another way to force the recompile is to use

EXEC <procedure name> WITH RECOMPILE .
Yet another mechanism for forcing recompiles on each execution would be to create the

•

procedure using WITH RECOMPILE as part of the procedure definition.
You can also use •

OPTION ( RECOMPILE ) on individual statements to have only those statements
instead of the entire procedure recompile. This is frequently the best approach if you're going
to force recompiles.

• You can reassign input parameters to local variables. This popular fix forces the optimizer to

make a best guess at the values likely to be used by looking at the statistics of the data being
referenced, which can and does eliminate the values being taken into account. This is the old
way of doing it and has been replaced by using OPTIMIZE FOR UNKNOWN .
You can use a query hint, •

OPTIMIZE FOR , when you create the procedure and supply it with
known good parameters that will generate a plan that works well for most of your queries. You
can specify a value that generates a specific plan, or you can specify UNKNOWN to get a generic
plan based on the average of the statistics.