Plan Caching - Pro SQL Server Internals - page 500

from
sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) t
where
t.[text] like '%Employees%'
order by
p.objtype desc
option (recompile);
Figure 26-14. Plan cache before the Template plan guide is created
Listing 26-26 shows you how to create a template plan guide and override the PARAMETERIZATION database option.
Listing 26-26. Template plan guide: Creating a plan guide
declare
@stmt nvarchar(max)
,@params nvarchar(max)
-- Getting template for the query
exec sp_get_query_template
@querytext =
N'select top 1 ID, Number, Name from dbo.Employees where ID = 5;'
,@templatetext = @stmt output
,@params = @params output;
-- Creating plan guide
exec sp_create_plan_guide
@type = N'TEMPLATE'
,@name = N'template_plan_guide_demo'
,@stmt = @stmt
,@module_or_batch = null
,@params = @params
,@hints = N'OPTION (PARAMETERIZATION FORCED)'
Now if you ran the code from Listing 26-25, the statement would be parameterized, as shown in Figure 26-15 .
Figure 26-15. Plan cache after Template plan guide is created
As a final option, you can force SQL Server to use a specific execution plan by specifying it in the plan guide, or
use it with the USE PLAN query hint. Listing 26-27 shows an example of both approaches. The full XML plan is omitted
to conserve space in the topic.