Parameter Sniffing - SQL Server Query Performance Tuning - page 311

• You can use a plan guide, which is a mechanism to get a query to behave a certain way without

making modifications to the procedure. This will be covered in detail in Chapter 17.

• You can disable parameter sniffing for the server by setting trace flag 4136 to on. Understand

that this beneficial behavior will be turned off for the entire server, not just one problematic
query. This is potentially a highly dangerous choice to make for your system. I discuss it
further in a moment.

• If you have a particular query pattern that leads to bad parameter sniffing, you can isolate the

functionality by setting up two, or more, different procedures using a wrapper procedure to
determine which to call. This can help you use multiple different approaches at the same time.

Each of these possible solutions comes with trade-offs that must be taken into account. If you decide to just
recompile the query each time it's called, you'll have to pay the price for the additional CPU needed to recompile the
query. This goes against the whole idea of trying to get plan reuse by using parameterized queries, but it could be the
best solution in your circumstances. Reassigning your parameters to local variables is something of an old-school
approach; the code can look quite silly.

ALTER PROC dbo.AddressByCity @City NVARCHAR(30)
AS
DECLARE @LocalCity NVARCHAR(30) = @City;

SELECT a.AddressID,
a.AddressLine1,
AddressLine2,
a.City,
sp.[Name] AS StateProvinceName,
a.PostalCode
FROM Person.Address AS a
JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
WHERE a.City = @LocalCity;

Using this approach, the optimizer makes its cardinality estimates based on the density of the columns in
question, not using the histogram. But it looks odd in a query. In fact, if you take this approach, I strongly suggest
adding a comment in front of the variable declaration, so it's clear why you're doing this. Here's an example:

-- This allows the query to bypass bad parameter sniffing

But, with this approach you're now subject to the possibility of variable sniffing, so it's not really recommended
unless you're on a SQL Server instance that is older than 2008. From SQL Server 2008 and onward, you're better off
using the OPTIMIZE FOR UNKOWN query hint to do the same thing.
You can use the OPTIMIZE FOR query hint and pass a specific value. So, for example, if you wanted to be sure that
the plan that was generated by the value Mentor is always used, you can do this to the query:

ALTER PROC dbo.AddressByCity @City NVARCHAR(30)
AS
SELECT a.AddressID,
a.AddressLine1,
AddressLine2,
a.City,
sp.[Name] AS StateProvinceName,
a.PostalCode