• Article

New Plan Guide

Use this page to create a plan guide in the selected database. This page is accessed by right-clicking Plan Guides in Object Explorer (in the Programmability folder) and selecting New Plan Guide.

Plan guides influence query optimization by attaching query hints or a fixed query plan to them. In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. When the query executes, the query optimizer matches the Transact-SQL statement to the plan guide and either attaches the OPTION clause to the query at run time or uses the specified query plan.

Options

  • Name
    Enter the name of the plan guide.

    Plan guide names are scoped to the current database. The name must comply with the rules for identifiers and cannot start with the number sign (#). The maximum length is 124 characters.

  • Statement
    Enter the Transact-SQL statement against which the plan guide is to be applied. The size of the statement is limited only by available memory of the server.

    The statement must be provided in a way that allows for the query optimizer to match it with the corresponding statement supplied within the batch or object. For more information, see Designing and Implementing Plan Guides.

  • Scope type
    Select the type of entity in which the Transact-SQL statement appears. This specifies the context for matching the Transact-SQL statement to the plan guide. Possible values are OBJECT, SQL, and TEMPLATE.

  • Scope batch
    If applicable, enter the batch text in which the Transact-SQL statement appears. The batch text cannot include a USE database statement.

    For a plan guide to match a batch submitted from an application, the batch text must be provided in the same format, character-for-character, as it is submitted to SQL Server. No internal conversion is performed to facilitate this match.

    When the scope type is SQL, and a batch text is not specified, the value of the batch text is set to the value of Statement. When the scope type is TEMPLATE, batch text is not allowed.

  • Scope schema name
    When the scope type is OBJECT, enter the name of the schema in which the object is contained.

  • Scope object name
    When the scope type is OBJECT, enter the name of the Transact-SQL stored procedure, user-defined scalar function, multistatement table-valued function, or DML trigger in which the Transact-SQL statement appears.

  • Parameters
    Enter the parameter name and data type of all parameters that are embedded in the Transact-SQL statement.

    Parameters apply only when either of the following is true:

    • The scope type is SQL or TEMPLATE. If TEMPLATE, parameters must not be NULL.

    • The Transact-SQL statement is submitted by using sp_executesql and a value for the parameter is specified, or SQL Server internally submits a statement after parameterizing it. For more information about parameterization and plan guides, see How SQL Server Matches Plan Guides to Queries.

    The parameter name and data type must be supplied in the exact same format as it is submitted to SQL Server either by using sp_executesql or submitted internally after parameterization.

  • Hints
    Enter the query hints or query plan to be applied to the Transact-SQL statement.

    To specify one or more query hints, enter a valid OPTION clause. For example, the following clause applies the MERGE JOIN and MAXDOP hints to the statement.

    OPTION (MERGE JOIN, MAXDOP 2)

    To specify a query plan, enter the XML Showplan output for the plan. You can obtain the XML Showplan for a query by querying the sys.dm_exec_query_stats dynamic management view. For example, the following query returns the query plan for the Transact-SQL statement, SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC.

    SELECT query_plan
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');