Plan Caching - Pro SQL Server Internals - page 499

Figure 26-13. Execution plan with Object plan guide
Listing 26-24 shows an example of a SQL plan guide, which set the MAXDOP option for the query. In this mode,
the @module_or_batch parameter should be set to null.
Listing 26-24. SQL plan guide
exec sp_create_plan_guide
@type = N'SQL'
,@name = N'SQL_plan_guide_demo'
,@stmt = N'select Country, count(*) as [Count]
from dbo.Employees
group by Country'
,@module_or_batch = NULL
,@params = null
,@hints = N'OPTION (MAXDOP 2)' ;
Working with Template plan guides is a bit more complex. Unlike SQL and Object plan guides, where the @stmt
parameter should be a character-for-character match with the queries, a template plan guide requires you to provide the
template for the query. Fortunately, you can use another system stored procedure, sp_get_query_template , to prepare it.
Let's look at an example and assume that we want SQL Server to autoparameterize the query from Listing 26-25.
Even though the execution plan for the query is safe—a Clustered Index Seek on a unique index would always return one
row, the TOP clause prevents SQL Server from parameterizing it. You can see the ad-hoc cached plan in Figure 26-14 .
Listing 26-25. Template plan guide: Sample Query
dbcc freeproccache
go
select top 1 ID, Number, Name from dbo.Employees where ID = 5;
go
select
p.usecounts, p.cacheobjtype, p.objtype, p.size_in_bytes,
t.[text]