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

From the sys.dm_exec_cached_plans output in Figure 15-4 , you can see that the previous plan for the query
hasn't been reused; the corresponding usecounts value remained at the old value of 2. Instead of reusing the existing
plan, a new plan is generated for the query and is saved in the procedure cache with a new plan_handle . If this ad
hoc query is reexecuted repeatedly with different filter criterion values, a new execution plan will be generated every
time. The inefficient reuse of the execution plan for this ad hoc query increases the load on the CPU by consuming
additional CPU cycles to regenerate the plan.
To summarize, ad hoc plan caching uses statement-level caching and is limited to an exact textual match. If an
ad hoc query is not complex, SQL Server can implicitly parameterize the query to increase plan reusability by using a
feature called simple parameterization. The definition of a simple query for simple parameterization is limited to fairly
simple cases such as ad hoc queries with only one table. As shown in the previous example, most queries requiring a
join operation cannot be autoparameterized.
Optimize for an Ad Hoc Workload
If your server is going to primarily support ad hoc queries, it is possible to achieve a degree of performance
improvement. One server option is called optimize for ad hoc workloads . Enabling this for the server changes the
way the engine deals with ad hoc queries. Instead of saving a full compiled plan for the query the first time it's called,
a compiled plan stub is stored. The stub does not have a full execution plan associated, saving the storage space
required for it and the time saving it to the cache. This option can be enabled without rebooting the server.
EXEC sp_configure
'optimize for ad hoc workloads',
1;
GO
RECONFIGURE;
After changing the option, flush the cache and then rerun the ad hoc query. Modify the query against
sys.dm_exec_cached_plans so that you include the size_in_bytes column; then run it to see the results in Figure 15-5 .
Figure 15-5. sys.dm_exec_cached_plans showing a compiled plan stub
Figure 15-5 shows in the cacheobjtype column that the new object in the cache is a compiled plan stub. Stubs
can be created for lots more queries with less impact on the server than full compiled plans. But the next time an ad
hoc query is executed, a fully compiled plan is created. To see this in action, run the query one more time and check
the results in sys.dm_exec_ cachedplans , as shown in Figure 15-6 .
Figure 15-6. The compiled plan stub has become a compiled plan