Execution Plan Cache Behavior - SQL Server Query Performance Tuning - page 286

From Figure 15-9 , you can see that SQL Server put the query through the simplification process and substituted
a pair of >= and <= operators, which are equivalent to the BETWEEN operator. Then the parameterization step modified
the query again. That means instead of resubmitting the preceding ad hoc query using the BETWEEN clause, if a similar
query using a pair of >= and <= is submitted, SQL Server will be able to reuse the existing execution plan. To confirm
this behavior, let's modify the ad hoc query as follows:
SELECT a.*
FROM Person.Address AS a
WHERE a.AddressID >= 40
AND a.AddressID <= 60;
Figure 15-10 shows the output of sys.dm_exec_cached_plans .
Figure 15-10. sys.dm_exec_cached_plans output showing reuse of the autoparameterized plan
From Figure 15-10 , you can see that the existing plan is reused, even though the query is syntactically different
from the query executed earlier. The autoparameterized plan generated by SQL Server allows the existing plan to
be reused not only when the query is resubmitted with different variable values but also for queries with the same
template form.
Simple Parameterization Limits
SQL Server is highly conservative during simple parameterization because the cost of a bad plan can far outweigh
the cost of generating a new plan. The conservative approach prevents SQL Server from creating an unsafe
autoparameterized plan. Thus, simple parameterization is limited to fairly simple cases, such as ad hoc queries with
only one table. An ad hoc query with a join operation between two (or more) tables (as shown in the early part of the
“Plan Reusability of an Ad Hoc Workload” section) is not considered safe for simple parameterization.
In a scalable system, do not rely on simple parameterization for plan reusability. The simple parameterization
feature of SQL Server makes an educated guess as to which variables and constants can be parameterized. Instead
of relying on SQL Server for simple parameterization, you should actually specify it programmatically while building
your application.
Forced Parameterization
If the system you're working on consists of primarily ad hoc queries, you may want to attempt to increase the number
of queries that accept parameterization. You can modify a database to attempt to force, within certain restrictions, all
queries to be parameterized just like in simple parameterization.
To do this, you have to change the database option PARAMETERIZATION to FORCED using ALTER DATABASE like this:
ALTER DATABASE AdventureWorks2012 SET PARAMETERIZATION FORCED;