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 can be created to match queries that are executed in the following contexts:

  • OBJECT plan guides match queries that execute in the context of Transact-SQL stored procedures, scalar functions, multistatement table-valued functions, and DML triggers.
  • SQL plan guides match 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.
  • TEMPLATE plan guides match 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.

Note

Plan guides can be used only on the SQL Server 2005 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 2005. You should verify the desirability of the plan guides in each database after performing a server upgrade.

For SQL- or TEMPLATE-based plan guides that specify @type = N'SQL' or @type = N'TEMPLATE' in the sp_create_plan_guide statement, 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. To capture the actual batch text, you can use SQL Server Profiler. 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.

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.

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 AdventureWorks is the current database and the following query executes:

SELECT * FROM Person.Contact

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

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

USE DB1;
GO
SELECT * FROM Person.Contact;

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

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 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.

The total number of plan guides you can create is limited only by available system resources. Nevertheless, plan guides should be used sparingly to address only individual 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. In particular, plan guides that apply the USE PLAN query hint apply a fixed plan for the targeted query. Therefore, 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 the USE PLAN query, 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.

We recommend reevaluating 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.

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.

To create a plan guide

To disable, re-enable, or drop plan guides

To obtain information about plan guides in the current database

See Also

Concepts

Optimizing Queries in Deployed Applications by Using Plan Guides

Other Resources

Query Performance

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

15 September 2007

Updated content:
  • Clarified the plan guide matching requirements for statement_text when @type = 'SQL' and @module_or_batch is set to NULL.
  • Added information about the effect of creating plan guides on the plan cache.