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.

Caution:
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

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

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.

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

• sp_create_plan_guide
  
• sp_create_plan_guide_from_handle
  

To disable, enable, or drop plan guides

• sp_control_plan_guide
  

To obtain information about plan guides in the current database

• sys.plan_guides
  

To validate a plan guide

• sys.fn_validate_plan_guide
  

Reference

Other Resources

Help and Information