Database Reference
In-Depth Information
Check the
cacheobjtype
value. It has changed from Compiled Plan Stub to Compiled Plan. Finally, to see the
real difference between a stub and a full plan, check the
sizeinbytes
column in Figure
15-5
and Figure
15-6
. The size
changed from 352 in the stub to 65536 in the full plan. This shows precisely the savings available when working with
lots of ad hoc queries. Before proceeding, be sure to disable
optimize for ad hoc workloads
.
EXEC sp_configure
'optimize for ad hoc workloads',
0;
GO
RECONFIGURE;
Personally, I see little downside to implementing this on just about any system. Like with all recommendations,
you should test it to ensure your system isn't exceptional. However, the cost of writing the plan into memory when it's
called a second time is extremely trivial to the savings in memory overall that you see by not storing plans that are only
ever going to be used once. In all my testing and experience, this is a pure benefit with little downside.
Simple Parameterization
When an ad hoc query is submitted, SQL Server analyzes the query to determine which parts of the incoming
text might be parameters. It looks at the variable parts of the ad hoc query to determine whether it will be safe to
parameterize them automatically and use the parameters (instead of the variable parts) in the query so that the query
plan can be independent of the variable values. This feature of automatically converting the variable part of a query
into a parameter, even though not parameterized explicitly (using a prepared workload technique), is called
simple
parameterization.
During simple parameterization, SQL Server ensures that if the ad hoc query is converted to a parameterized
template, the changes in the parameter values won't widely change the plan requirement. On determining the
simple parameterization to be safe, SQL Server creates a parameterized template for the ad hoc query and saves the
parameterized plan in the procedure cache.
The parameterized plan is not based on the dynamic values used in the query. Since the plan is generated
for a parameterized template, it can be reused when the ad hoc query is reexecuted with different values for the
variable parts.
To understand the simple parameterization feature of SQL Server, consider the following query:
SELECT a.*
FROM Person.Address AS a
WHERE a.AddressID = 42;
When this ad hoc query is submitted, SQL Server can treat this query as it is for plan creation. However,
before the query is executed, SQL Server tries to determine whether it can be safely parameterized. On determining
that the variable part of the query can be parameterized without affecting the basic structure of the query, SQL
Server parameterizes the query and generates a plan for the parameterized query. You can observe this from the
sys.dm_exec_ cached_plans
output shown in Figure
15-7
.
Figure 15-7.
sys.dm_exec_cached_plans output showing an autoparameterized plan
