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

The usecounts of the executable plan for the parameterized query appropriately represents the number of reuses
as 1. Also, note that the objtype for the autoparameterized executable plan is no longer Adhoc ; it reflects the fact that
the plan is for a parameterized query, Prepared.
The original ad hoc query, even though not executed, gets compiled to create the query tree required for the
simple parameterization of the query. The compiled plan for the ad hoc query will be saved in the plan cache. But
before creating the executable plan for the ad hoc query, SQL Server figured out that it was safe to autoparameterize
and thus autoparameterized the query for further processing. This is visible as the highlighted line in Figure 15-7 .
Since this ad hoc query has been autoparameterized, SQL Server will reuse the existing execution plan if you
reexecute simpleparameterization.sql with a different value for the variable part.
SELECT.*
FROM Person.Address AS a
WHERE a.[AddressID] = 52; --previous value was 42
Figure 15-8 shows the output of sys.dm_exec_cached_plans .
Figure 15-8. sys.dm_exec_cached_plans output showing reuse of the autoparameterized plan
From Figure 15-8 , you can see that although a new plan has been generated for this ad hoc query, the ad hoc one
using an Addressld value of 52, the existing prepared plan is reused as indicated by the increase in the corresponding
usecounts value to 2. The ad hoc query can be reexecuted repeatedly with different filter criterion values, reusing the
existing execution plan—all this despite that the original text of the two queries does not match. The parameterized
query for both would be the same, so it was reused.
There is one more aspect to note in the parameterized query for which the execution plan is cached. In
Figure 15-7 , observe that the body of the parameterized query doesn't exactly match with that of the ad hoc query
submitted. For instance, in the ad hoc query, there are no square brackets on any of the objects.
On realizing that the ad hoc query can be safely autoparameterized, SQL Server picks a template that can be used
instead of the exact text of the query.
To understand the significance of this, consider the following query:
SELECT a.*
FROM Person.Address AS a
WHERE a.AddressID BETWEEN 40 AND 60;
Figure 15-9 shows the output of sys.dm_exec_cached_plans .
Figure 15-9. sys.dm_exec_cached_plans output showing plan simple parameterization using a template