Parameter Sniffing - SQL Server Query Performance Tuning - page 308

Figure 16-3. Output from sys.dm_exec_query_stats verifies procedure reuse
To show how bad parameter sniffing can occur, you can reverse the order of the execution of the procedures. First
flush the buffer cache by running DBCC FREEPROCCACHE , which should not be run against a production machine. Then
rerun the queries in reverse order. The first query, using the parameter value Mentor , results in the following I/O and
execution plan (Figure 16-4 ):
Table 'StateProvince'. Scan count 0, logical reads 2
Table 'Address'. Scan count 1, logical reads 216
CPU time = 0 ms, elapsed time = 78 ms
Figure 16-4. The execution plan changes
Figure 16-4 is not the same execution plan as that shown in Figure 16-2 . The number of reads drops slightly, but
the execution time stays roughly the same. The second execution, using London as the value for the parameter, results
in the following I/O and execution times:
Table 'StateProvince'. Scan count 0, logical reads 868
Table 'Address'. Scan count 1, logical reads 216
CPU time = 0 ms, elapsed time = 283 ms.
This time the reads are radically higher, up to what they were when using the local variable, and the execution time
was increased. The plan created in the first execution of the procedure with the parameter London created a plan best
suited to retrieve the 434 rows that match those criteria in the database. Then the next execution of the procedure using
the parameter value Mentor did well enough using the same plan generated by the first execution. When the order is
reversed, a new execution plan was created for the value Mentor that did not work at all well for the value London .
In these examples, I've actually cheated just a little. If you were to look at the distribution of the data in the
statistics in question, you'd find that the average number of rows returned is around 34, while London 's 434 is an
outlier. The slightly better performance you saw when the procedure was compiled for London reflects the fact that
a different plan was needed. However, the performance for values like Mentor was slightly reduced with the plan for
London . Yet, the improved plan for Mentor was absolutely disastrous for a value like London . Now comes the hard part.
You have to determine which of your plans is correct for your system's load. One plan is slightly worse for the
average values, while another plan is better for average values but seriously hurts the outliers. The question is, is it
better to have somewhat slower performance for all possible data sets and support the outliers' better performance or
let the outliers suffer in order to support a larger cross section of the data because it may be called more frequently?
You'll have to figure this out on your own system.