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

Optimizing a query using an object plan
guide
Plan guide is a feature in SQL Server that you can use to tune queries, which are developed/
deployed by third parties and for which you are not allowed to modify the code. With a plan
guide, it's possible to attach query hints to the queries that are executed against the database
server. A SQL Server attaches the query hints, as specified by the plan guide, to the query
before executing it. In this way, an ad-hoc query or a query in the stored procedure can be
tuned without changing it in the source code.
There are three types of plan guides, as follows:
F Object plan guide: Used with stored procedures and user-defined functions
F SQL plan guide: Used with ad-hoc SQL queries
F Template plan guide: Used with ad-hoc SQL queries
We will learn how to use an object plan guide to optimize a query for a particular value. An
object plan guide is created upon a stored procedure or user defined functions. A SQL query
statement specified in the plan guide is matched against the query found within the stored
procedure for optimization, and if it is matched, the query is optimized before it gets executed.
In this recipe, we will create a stored procedure named Sales.
GetSalesOrderByCountry_TestPlanGuide , which retrieves the data from the Sales.
SalesOrderHeader table, based on parameter @Country_region , representing a specific
country region by joining it with Sales.Customer and Sales.SalesTerritory . Knowing
in advance that most of the orders are from the US country region, we will apply a plan guide
to the stored procedure, so that the query that retrieves sales data in the stored procedure is
optimized for the US country region, with the OPTIMIZE FOR query hint.
Getting ready
The following are the prerequisites for this recipe:
F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.
F A sample AdventureWorks2012 database on the SQL Server instance. For more
details on how to install the AdventureWorks2012 database, please refer to the
Preface of this topic.