Advanced Query Tuning Hints and Plan Guides - Microsoft SQL Server 2012 Performance Tuning - page 373

Because we had an XML execution plan for when ProductID is 806 , the plan was generated
having an index seek operation performed on the index defined in the ProductID column.
We wanted to force the index seek operation by forcing the same execution plan for every
product. Hence, we have specified a parameter @ProductID in our query text that we
passed to the @stmt variable and defined parameter @ProductID int for @params
variable in the sp_create_plan_guide stored procedure call. Because we wanted to
create a SQL plan guide, we passed a literal value SQL to the @type parameter and NULL
value to the @module_or_batch parameter.
We then verified that our plan guide was created by opening Databases |
AdventureWorks2012 | Programmability | Plan Guides and then selecting
Properties in Object Explorer.
Once the plan guide was created, we executed the same parameterized query for ProductID
equal to 800 with a sp_executesql system stored procedure and observed it execution
plan. When we executed our query, the plan guide's query template was matched against
the executed query and the plan guide was used for query optimization, which forced the
execution plan that we had specified. We could see that the execution plan of the query
resulted in an index seek operation and not in an index scan, this time.
Finally, we verified that the plan guide MySQLFixedPlanGuide was indeed used by looking
at the execution plan property and by examining the value of the PlanGuideName property in
the Properties window.