Database Reference
In-Depth Information
FROM Person.Address AS a
JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
WHERE a.City = @City
OPTION (OPTIMIZE FOR (@City = 'Mentor'));
Now the optimizer will ignore any values passed to
@City
and will always use the value of
Mentor
. You can even
see this in action if you modify the query as shown, which will remove the query from cache, and then you execute
it using the parameter value of
London
. This will generate a new plan in cache. If you open that plan and look at the
SELECT
properties, you'll see evidence of the hint in Figure
16-7
.
Figure 16-7.
Runtime and compile-time values differ
As you can see, the optimizer did exactly as you specified and used the value
Mentor
to compile the plan even
though you can also see that you executed the query using the value
London
. The problem with this approach is that
data changes over time and what might have been an optimal plan for your data at point is no longer. If you choose to
use the
OPTIMIZE FOR
hint, you need to plan to regularly reassess it.
If you choose to disable parameter sniffing entirely by using the trace flag, understand that it turns it off on the
entire server. Since, most of the time, parameter sniffing is absolutely helping you, you had best be sure that you're
receiving no benefits from it and the only hope of dealing with it is to turn off sniffing. This doesn't require even a
server reboot, so it's immediate. The plans generated will be based on the averages of the statistics available, so the
plans can be seriously suboptimal depending on your data. Before doing this, explore the possibility of using the
RECOMPILE
hint on your most problematic queries. You're more likely to get better plans that way even though you
won't get plan reuse.
With all these possible mitigations approaches, test carefully on your systems before you decide on an approach.
Each of these approaches works, but they work in ways that may be better in one circumstance than another, so it's
good to know the different methods, and you can experiment with them all depending on your situation.
Finally, remember that this is driven by statistics, so if your statistics are inaccurate or out-of-date, you're more
likely to get bad parameter sniffing. Reexamining your statistics maintenance routines to ensure their efficacy is
frequently the single best solution.
Summary
In this chapter I outlined exactly what parameter sniffing is and how it benefits all your parameterized queries most
of the time. That's important to keep in mind because when you run into bad parameter sniffing, it can seem like
parameter sniffing is more danger than it's worth. I discussed how statistics and data distribution can create plans
that are suboptimal for some of the data set even as they are optimal for other parts of the data. This is bad parameter
sniffing at work. There are several ways to mitigate bad parameter sniffing, but each one is a trade-off, so examine
them carefully to ensure you do what's best for your system.
In the next chapter, I'll talk about what happens to cause queries to recompile and what can be done about that.
