Parameter Sniffing - SQL Server Query Performance Tuning - page 307

Bad Parameter Sniffing
Parameter sniffing creates problems when you have issues with your statistics. The values passed in the parameter
may be representative of your data and the data distribution within the statistics. In this case, you'll see a good
execution plan. But what happens when the parameter passed is not representative of the rest of the data in the table?
This situation can arise because your data is just distributed in a nonaverage way. For example, most values in the
statistics will return only a few rows, say six, but some values will return hundreds of rows. The same thing works the
other way, with a common distribution of large amounts of data and an uncommon set of small values. In this case,
an execution plan is created, based on the nonrepresentative data, but it's not useful to most of the queries. This
situation most frequently exposes itself through a sudden, and sometimes quite severe, drop in performance. It can
even, seemingly randomly, fix itself when a recompile event allows a better representative data value to be passed in a
parameter.
You can also see this occur when the statistics are out of date or are inaccurate because of being sampled instead
of scanned (for more details on statistics in general, see Chapter 12). Regardless, the situation creates a plan that is
less than useful and stores it in cache. For example, take the following stored procedure:
CREATE PROC dbo.AddressByCity @City NVARCHAR(30)
AS
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 = @City;
GO
If the stored procedure created previously, dbo.AddressByCity , is run again but this time with a different
parameter, then it returns with a different set of I/O and execution times but the same execution plan because it is
reused from cache.
EXEC dbo.AddressByCity @City = N'Mentor';
Table 'Address'. Scan count 1, logical reads 216
Table 'StateProvince'. Scan count 1, logical reads 3
CPU time = 15 ms, elapsed time = 84 ms.
The I/O is the same since the same execution plan is reused. The execution time is faster because fewer rows are
being returned. You can verify that the plan was reused by taking a look at the output from sys.dm_exec_query_stats
(in Figure 16-3 ).
SELECT dest.text,
deqs.execution_count,
deqs.creation_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text LIKE 'CREATE PROC dbo.AddressByCity%';