Plan Caching - Pro SQL Server Internals - page 498

There are three types of plan guides available:
An Object plan guide allows you to specify a hint for a query, which exists in a T-SQL object,
such as a stored procedure, trigger, or user-defined function.
A SQL plan guide allows you to specify a hint for a particular SQL query, either standalone
or as part of a batch.
A Template plan guide allows you to specify a type of parameterization—FORCED or
SIMPLE—for a particular query template overriding the database setting.
The code in Listing 26-23 removes the query hint from the dbo.GetAverageSalary stored procedure and creates
a plan guide with an OPTIMIZE FOR UNKNOWN hint. The @Stmt parameter should specify a query where a hint needs to
be added, and @module_or_batch should specify the name of the object.
Listing 26-23. Object plan guide
alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = @Country
end
go
exec sp_create_plan_guide
@type = N'OBJECT'
,@name = N'object_plan_guide_demo'
,@stmt = N'select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = @Country'
,@module_or_batch = N'dbo.GetAverageSalary'
,@params = null
,@hints = N'OPTION (OPTIMIZE FOR (@Country UNKNOWN))';
Now if you ran the stored procedure for @Country = 'Canada' , you would get the execution plan shown in
Figure 26-13 , similar to what you had with the query hint within the stored procedure. You can see that a plan
guide was used during optimization in the properties of the top operator in the graphical plan as well as in its XML
representation.