sp_create_plan_guide (Transact-SQL)

مقالة
05/16/2013

Creates a plan guide for associating query hints or actual query plans with queries in a database. For more information about plan guides, see Plan Guides.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      { 
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL } 
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' 
                 | N'XML_showplan'
                 | NULL }

Arguments

[ @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 Specify 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 USEdatabase 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.
@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).

Remarks

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 cannot be used in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012. 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. You should verify the desirability of the plan guides in each database after performing a server upgrade.

Plan Guide Matching Requirements

For plan guides that specify @type = 'SQL' or @type = 'TEMPLATE' to successfully match a query, the values for batch_text and @parameter\_name data_type [,...n ] must be provided in exactly the same format as their counterparts submitted by the application. This means you must provide the batch text exactly as the SQL Server compiler receives it. To capture the actual batch and parameter text, you can use SQL Server Profiler. For more information, see Use SQL Server Profiler to Create and Test Plan Guides.

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.

When SQL Server matches the value of statement_text to batch_text and @parameter\_name data_type [,...n ], or if @type = **'**OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:

White space characters (tabs, spaces, carriage returns, or line feeds) inside the string.
Comments (-- or /* */).
Trailing semicolons

For example, SQL Server can match the statement_text string N'SELECT * FROM T WHERE a = 10' to the following batch_text:

N'SELECT *

FROM T

WHERE a=10'

However, the same string would not be matched to this batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL Server ignores the carriage return, line feed, and space characters inside the first query. In the second query, the sequence WHERE b = 10 is interpreted differently from WHERE a = 10. Matching is case- and accent-sensitive (even when the collation of the database is case-insensitive), except in the case of keywords, where case is insensitive. Matching is insensitive to shortened forms of keywords. For example, the keywords EXECUTE, EXEC, and execute are considered equivalent.

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.

Permissions

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.

Examples

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

The following example creates a plan guide that matches a query executed in the context of an application-based stored procedure, and applies the OPTIMIZE FOR hint to the query.

Here is the 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

Here is the plan guide created on the query in the stored procedure:

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

The following example creates a plan guide to match a query in a batch submitted by an application that uses the sp_executesql system stored procedure.

Here is the batch:

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

To prevent a parallel execution plan from being generated on this query, create the following plan guide:

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 AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.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 AdventureWorks2012.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2012.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 AdventureWorks2012.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2012.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.

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

Plan guides can match queries that are submitted from API server cursor routines. These routines include sp_cursorprepare, sp_cursorprepexec, and sp_cursoropen. Applications that use the ADO, OLE DB, and ODBC APIs frequently interact with SQL Server by using API server cursors. You can see the invocation of API server cursor routines in SQL Server Profiler traces by viewing the RPC:Starting profiler trace event.

Suppose the following data appears in an RPC:Starting profiler trace event for a query you want to tune with a plan guide:

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 AS h INNER JOIN 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;

You notice that the plan for the SELECT query in the call to sp_cursorprepexec is using a merge join, but you want to use a hash join. The query submitted by using sp_cursorprepexec is parameterized, including both a query string and a parameter string. You can create the following plan guide to change the choice of plan by using the query and parameter strings exactly as they appear, character for character, in the call to sp_cursorprepexec.

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)';

Subsequent executions of this query by the application will be affected by this plan guide, and a hash join will be used to process the query.

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

The following example creates a plan guide for a simple ad hoc SQL statement. The desired query plan for this statement is provided in the plan guide by specifying the XML Showplan for the query directly in the @hints parameter. The example first executes the SQL statement to generate a plan in the plan cache. For the purposes of this example, it is assumed that the generated plan is the desired plan and no additional query tuning is required. The XML Showplan for the query is obtained by querying the sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_text_query_plan dynamic management views and is assigned to the @xml\_showplan variable. The @xml\_showplan variable is then passed to the sp_create_plan_guide statement in the @hints parameter. Or, you can create a plan guide from a query plan in the plan cache by using the sp_create_plan_guide_from_handle stored procedure.

USE AdventureWorks2012;
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

مشاركة عبر