• Article

Designing and Implementing Plan Guides

You can use plan guides to optimize the performance of queries when you cannot or do not want to change the text of the query directly. Plan guides influence optimization of queries by attaching query hints or a fixed query plan to them. Plan guides can be created to match queries that are executed in the following contexts:

  • An OBJECT plan guide matches queries that execute in the context of Transact-SQL stored procedures, user-defined scalar functions, multi-statement user-defined table-valued functions, and DML triggers.

  • An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object. SQL-based plan guides can also be used to match queries that parameterize to a specified form.

  • A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

For more information, see Understanding Plan Guides.

The total number of plan guides you can create is limited only by available system resources. Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. Plan guides should not be used to influence most of the query load of a deployed application.

We recommend re-evaluating and testing plan guide definitions when you upgrade your application to a new release of SQL Server. Performance tuning requirements and plan guide matching behavior may change. Although an invalid plan guide will not cause a query to fail, the plan is compiled without using the plan guide. After upgrading a database we recommend that you perform the following tasks to validate existing plan guides by using the sys.fn_validate_plan_guide function. Alternatively, you can monitor for invalid plan guides by using the Plan Guide Unsuccessful event in SQL Server Profiler.

Note

Plan guides can be used only on the SQL Server Standard, Developer, Evaluation, and Enterprise editions; however, plan guides are visible in any edition. You can also attach a database that contains plan guides to any edition. Plan guides remain intact when you restore or attach a database to an upgraded version of SQL Server.

Attaching Query Hints to a Plan Guide

Any combination of valid query hints can be used in a plan guide. When a plan guide matches a query, the OPTION clause specified in the hints clause of a plan guide is added to the query before it compiles and optimizes. If a query that is matched to a plan guide already has an OPTION clause, the query hints specified in the plan guide replace those in the query. However, for a plan guide to match a query that already has an OPTION clause, you must include the OPTION clause of the query when you specify the text of the query to match in the sp_create_plan_guide statement. If you want the hints specified in the plan guide to be added to the hints that already exist on the query, instead of replacing them, you must specify both the original hints and the additional hints in the OPTION clause of the plan guide.

Warning

Plan guides that misuse query hints can cause compilation, execution, or performance problems. Plan guides should be used only by experienced developers and database administrators.

Common Query Hints Used in Plan Guides

Queries that can benefit from plan guides are generally parameter-based, and may be performing poorly because they use cached query plans whose parameter values do not represent a worst-case or most representative scenario. The OPTIMIZE FOR and RECOMPILE query hints can be used to address this problem. OPTIMIZE FOR instructs SQL Server to use a particular value for a parameter when the query is optimized. RECOMPILE instructs the server to discard a query plan after execution, forcing the query optimizer to recompile a new query plan the next time that the same query is executed. For an example, see Understanding Plan Guides.

In addition, you can specify the table hints INDEX and FORCESEEK as query hints. When specified as query hints, these hints behave like an inline table or view hint. The INDEX hint forces the query optimizer to use only the specified indexes to access the data in the referenced table or view. The FORCESEEK hint forces the optimizer to use only an index seek operation to access the data in the referenced table or view. These hints provide additional plan guide functionality and allow you to have more influence over the optimization of queries that use the plan guide. For an example, see Using the INDEX and FORCESEEK Query Hints in Plan Guides.

Attaching a Query Plan to a Plan Guide

Plan guides that apply a fixed query plan are useful when you are aware of an existing execution plan that performs better than the one selected by the optimizer for a particular query. Note that applying a fixed plan to a query means that the query optimizer can no longer adapt the plan for the query to changes in statistics and indexes. When you consider plan guides that use fixed query plans , make sure that you compare the benefits of applying a fixed plan with the inability to adapt the plan automatically as data distribution and available indexes change.

You can attach a specific query plan to a plan guide by specifying the XML Showplan of the plan in the xml_showplan parameter in the sp_create_plan_guide statement or by specifying the plan handle of a cached plan in the sp_create_plan_guide_from_handle statement. Both of these methods apply the fixed query plan to the targeted query.

Plan Guide Matching Requirements

Plan guides are scoped to the database in which they are created. Therefore, only plan guides that exist in the database that is current when a query executes can be matched to the query. For example, if AdventureWorks2008R2 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

Only plan guides in the AdventureWorks2008R2 database are eligible to be matched to this query.

However, if AdventureWorks2008R2 is the current database and the following statements are run:

USE DB1;

GO

SELECT FirstName, LastName FROM Person.Person;

Only plan guides in DB1 are eligible to be matched to the query, because the query is executing in the context of DB1.

For SQL- or TEMPLATE-based plan guides, SQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. This means you must provide the text exactly as SQL Server receives it in the actual batch.

When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in exactly the same format, character-for-character, as it is submitted to SQL Server. No internal conversion is performed to facilitate this match.

Generally, you should test plan guides using SQL Server Profiler to verify that your query is being matched to your plan guide. Testing SQL- or TEMPLATE-based plan guides by running batches from the SQL Server Management Studio may give unexpected results. For more information, see Using SQL Server Profiler to Create and Test Plan Guides.

Note

The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

Plan Guide Effect on the Plan Cache

Creating a plan guide on a module removes the query plan for that module from the plan cache. Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

Plan Guide Statements

To create a plan guide

To disable, enable, or drop plan guides

To obtain information about plan guides in the current database

To validate a plan guide