[ @name = ] N'plan_guide_name'

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

[ @stmt = ] N'statement_text'

Is a Transact-SQL statement against which to create a plan guide. When the SQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect. For the creation of a plan guide to succeed, statement_text must appear in the context specified by the @type, @module_or_batch, and @params parameters.

statement_text must be provided in a way that allows for the query optimizer to match it with the corresponding statement supplied within the batch or module identified by @module_or_batch and @params. For more information, see the "Remarks" section. The size of statement_text is limited only by available memory of the server.

[ @type = ] N'{ OBJECT | SQL | TEMPLATE }'

Is the type of entity in which statement_text appears. This specifies the context for matching statement_text to plan_guide_name.

OBJECT

Indicates statement_text appears in the context of a Transact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQL DML trigger in the current database.

SQL

Indicates statement_text appears in the context of a stand-alone statement or batch that can be submitted to SQL Server through any mechanism. Transact-SQL statements submitted by common language runtime (CLR) objects or extended stored procedures, or by using EXEC N'sql_string', are processed as batches on the server and, therefore, should be identified as @type = 'SQL'. If SQL is specified, the query hint PARAMETERIZATION { FORCED | SIMPLE } cannot be specified in the @hints parameter.

TEMPLATE

Indicates the plan guide applies to any query that parameterizes to the form indicated in statement_text. If TEMPLATE is specified, only the PARAMETERIZATION { FORCED | SIMPLE } query hint can be specified in the @hints parameter. For more information about TEMPLATE plan guides, see Specifying Query Parameterization Behavior by Using Plan Guides.

[ @module_or_batch = ] { N'[ schema_name. ] object_name' | N'batch_text' | NULL }

Specifies either the name of the object in which statement_text appears, or the batch text in which statement_text appears. The batch text cannot include a USE database statement.

For a plan guide to match a batch submitted from an application, 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. For more information, see the Remarks section.

[schema_name.]object_name specifies the name of a Transact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQL DML trigger that contains statement_text. If schema_name is not specified, schema_name uses the schema of the current user. If NULL is specified and @type = 'SQL', the value of @module_or_batch is set to the value of @stmt. If @type = 'TEMPLATE', @module_or_batch must be NULL.

[ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }

Specifies the definitions of all parameters that are embedded in statement_text. @params applies only when either of the following is true:

• @type = 'SQL' or 'TEMPLATE'. If 'TEMPLATE', @params must not be NULL.
  
• statement_text is submitted by using sp_executesql and a value for the @params parameter is specified, or SQL Server internally submits a statement after parameterizing it. Submission of parameterized queries from database APIs (including ODBC, OLE DB, and ADO.NET) appear to SQL Server as calls to sp_executesql or to API server cursor routines; therefore, they can also be matched by SQL or TEMPLATE plan guides. For more information about parameterization and plan guides, see How SQL Server Matches Plan Guides to Queries.
  

@parameter_name 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. For more information, see the Remarks section. If the batch does not contain parameters, NULL must be specified. The size of @params is limited only by available server memory.

[@hints = ] { N'OPTION ( query_hint [ ,...n ] )' | N'XML_showplan' | NULL }

N'OPTION ( query_hint [ ,...n ] )

Specifies an OPTION clause to attach to a query that matches @stmt. @hints must be syntactically the same as an OPTION clause in a SELECT statement, and can contain any valid sequence of query hints.

N' XML_showplan '

Is the query plan in XML format to be applied as a hint.

We recommend assigning the XML Showplan to a variable; otherwise, you must escape any single quotation marks in the Showplan by preceding them with another single quotation mark. See example E.

NULL

Indicates that any existing hint specified in the OPTION clause of the query is not applied to the query. For more information, see OPTION Clause (Transact-SQL).

The arguments to sp_create_plan_guide must be provided in the order that is shown. When you supply values for the parameters of sp_create_plan_guide, all parameter names must be specified explicitly, or none at all. For example, if @name = is specified, then @stmt = , @type =, and so on, must also be specified. Likewise, if @name = is omitted and only the parameter value is provided, the remaining parameter names must also be omitted, and only their values provided. Argument names are for descriptive purposes only, to help understand the syntax. SQL Server does not verify that the specified parameter name matches the name for the parameter in the position where the name is used.

You can create more than one OBJECT or SQL plan guide for the same query and batch or module. However, only one plan guide can be enabled at any given time.

Plan guides of type OBJECT cannot be created for an @module_or_batch value that references a stored procedure, function, or DML trigger that specifies the WITH ENCRYPTION clause or that is temporary.

Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error. Trying to drop a table that has a trigger defined on it that is referenced by a plan guide also causes an error.

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 2008. You should verify the desirability of the plan guides in each database after performing a server upgrade.

Plan Guide Matching Requirements

Plan Guide Effect on the Plan Cache

To create a plan guide of type OBJECT, requires ALTER permission on the referenced object. To create a plan guide of type SQL or TEMPLATE, requires ALTER permission on the current database.

A. Creating a plan guide of type OBJECT for a query in a stored procedure

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

EXEC sp_create_plan_guide 
    @name =  N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.Customer AS c 
                 ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t 
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

B. Creating a plan guide of type SQL for a stand-alone query

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT TOP 1 * 
              FROM Sales.SalesOrderHeader 
              ORDER BY OrderDate DESC', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (MAXDOP 1)';

C. Creating a plan guide of type TEMPLATE for the parameterized form of a query

The following example creates a plan guide that matches any query that parameterizes to a specified form, and directs SQL Server to force parameterization of the query. The following two queries are syntactically equivalent, but differ only in their constant literal values.

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Here is the plan guide on the parameterized form of the query:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

In the previous example, the value for the @stmt parameter is the parameterized form of the query. The only reliable way to obtain this value for use in sp_create_plan_guide is to use the sp_get_query_template system stored procedure. The following script can be used both to obtain the parameterized query and then create a plan guide on it.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

Important:
The value of the constant literals in the @stmt parameter passed to sp_get_query_template might affect the data type that is chosen for the parameter that replaces the literal. This will affect plan guide matching. You may have to create more than one plan guide to handle different parameter value ranges.

For more information about how to obtain the parameterized form of a query to use in a TEMPLATE-based plan guide, see Designing Plan Guides for Parameterized Queries.

D. Creating a plan guide on a query submitted by using an API cursor request

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate BETWEEN @P1 AND @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

E. Creating a plan guide by obtaining the XML Showplan from a cached plan

USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (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;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints =@xml_showplan;
GO

Reference

Help and Information