Using SQL Server Plan Guides to Influence Query Execution

Optimization hints are explicitly specified in the SELECT statement to influence the work of the query optimizer. Sometimes you cannot or do not want to change the text of the SELECT statement directly. In that case, it is still possible to influence the execution of queries by using plan guides. In other words, plan guides allow you to use a particular optimization hint without changing the syntax of the SELECT statement.

NOTE

The main purpose of plan guides is to avoid hard-coding of hints in cases where it is not recommended or not possible (for third-party application code, for instance).

Plan guides are created using the sp_create_plan_guide system procedure. This procedure creates a plan guide for associating query hints or actual query plans with queries in a database. Another system procedure, sp_control_plan_guide, enables, fm, disables, or drops an existing plan guide.

NOTE

There are no Transact-SQL DDL statements for creation and deletion of plan guides. A subsequent SQL Server version will hopefully support such statements.

The Database Engine supports three types of plan guides:

• SQL   Matches queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object
• OBJECT   Matches queries that execute in the context of routines and DML triggers
• TEMPLATE   Matches stand-alone queries that are parameterized to a specified form

Example 1 shows how you can create an optimization hint without the modification of the corresponding SELECT statement.

EXAMPLE 1

 

As you can see from Example 1, the sp_create_plan_guide system procedure has several parameters. The @name parameter specifies the name of the new plan guide. The @stmt parameter comprises the T-SQL statement, while the @type parameter specifies the type of the plan guide (SQL, OBJECT, or TEMPLATE). The optimization hint is specified in the @hints parameter. (You can also use SQL Server Management Studio to create plan guides.)

To edit information related to plan guides, use the sys.plan_guides catalog view. This view contains a row for each plan guide in the current database. The most important columns are plan_guide_id, name, and query_text. The plan_guide_id column specifies the unique identifier of the plan guide, while the name column defines its name. The query_text column specifies the text of the query on which the plan guide is created.