Query Recompilation - SQL Server Query Performance Tuning - page 342

Now assume for a moment that this query is part of a third-party application and you are not able to modify it to
include OPTION (OPTIMIZE FOR) . To provide it with the query hint, OPTIMIZE FOR , create a plan guide as follows:
sp_create_plan_guide @name = N'MyGuide',
@stmt = N'SELECT soh.SalesOrderNumber,
soh.OrderDate,
sod.OrderQty,
sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerID;',@type = N'OBJECT',
@module_or_batch = N'dbo.CustomerList',@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@CustomerID = 1))';
Now, when the procedure is executed with each of the different parameters, even with the RECOMPILE being
forced as shown next, the OPTIMIZE FOR hint is applied. Figure 17-19 shows the resulting execution plan.
EXEC dbo.CustomerList
@CustomerID = 7920
WITH RECOMPILE;
EXEC dbo.CustomerList
@CustomerID = 30118
WITH RECOMPILE;
Figure 17-19. Using a plan guide to apply the OPTIMIZE FOR query hint