Database Reference
In-Depth Information
The optimizer sniffed the value
London
and arrived at a plan based on the data distribution that the city of
London represented within the statistics on the
Address
table. There may be other tuning opportunities in that query
or with the indexes on the table, but the plan is optimal for London. You can write an identical query using a local
variable just like this:
DECLARE @City NVARCHAR(30) = N'London';
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;
When this query gets executed, the results of the I/O and execution times are different.
Table 'StateProvince'. Scan count 0, logical reads 868
Table 'Address'. Scan count 1, logical reads 216
CPU time = 0 ms, elapsed time = 212 ms.
The execution time has gone up, and you've moved from 219 reads total to 1084. This somewhat explained by
taking a look at the new execution plan shown in Figure
16-2
.
Figure 16-2.
An execution plan created using a local variable
What has happened is that the optimizer was unable to sample, or sniff, the value for the local variable and
therefore had to use an average number of rows from the statistics. You can see this by looking at the estimated
number of rows in the properties of the
Index Scan
operator. It shows 34.113. Yet, if you look at the data returned,
there are actually 434 rows for the value
London
. In short, if the optimizer thinks it needs to retrieve 434 rows, it creates
a plan using the merge join and only 219 reads. But, if it thinks it's returning only about 34 rows, it uses the plan with a
nested loop join, which, by the nature of the nested loop that seeks in the lower value once for each value in the upper
set of data, results in 1,084 reads and slower performance.
That is parameter sniffing in action resulting in improved performance. Now, let's see what happens when
parameter sniffing goes bad.
