sp_create_plan_guide (Transact-SQL)sp_create_plan_guide (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Создает структуру плана для связывания указаний запроса или фактических планов запросов с запросами в базе данных.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.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQLTopic 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

[ @имя =] N'plan_guide_name"[ @name = ] N'plan_guide_name'
Имя структуры плана.Is the name of the plan guide. Имена структур планов ограничены областью текущей базы данных.Plan guide names are scoped to the current database. plan_guide_name должны соответствовать требованиям, предъявляемым к идентификаторы и не может начинаться со знака номера (#).plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#). Максимальная длина plan_guide_name равна 124 символам.The maximum length of plan_guide_name is 124 characters.

[ @stmt =] N'statement_text"[ @stmt = ] N'statement_text'
Инструкция языка Transact-SQLTransact-SQL, для которой создается структура плана.Is a Transact-SQLTransact-SQL statement against which to create a plan guide. Когда SQL ServerSQL Server запроса, оптимизатор распознает запроса, который соответствует statement_text, plan_guide_name вступает в силу.When the SQL ServerSQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect. Для создания структуры плана для успешной работы statement_text должен указываться в контексте, определяемом @типа, @module_or_batch, и @параметров params.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 должно быть указано в виде, позволяет оптимизатору запросов сопоставить ее с соответствующей инструкцией, в рамках пакета или модуля, идентифицируемого по @module_or_batch и @params.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. Размер statement_text ограничено только объемом доступной памяти сервера.The size of statement_text is limited only by available memory of the server.

[@тип =] N'{объект | SQL | ШАБЛОН} "[@type = ]N'{ OBJECT | SQL | TEMPLATE }'
Тип сущности, в которой statement_text отображается.Is the type of entity in which statement_text appears. Таким образом задается контекст для сопоставления statement_text для plan_guide_name.This specifies the context for matching statement_text to plan_guide_name.

OBJECTOBJECT
Указывает statement_text появляется в контексте Transact-SQLTransact-SQL хранимые процедуры, скалярные функции, многооператорной функции с табличным или Transact-SQLTransact-SQL триггер DML в текущей базе данных.Indicates statement_text appears in the context of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger in the current database.

SQLSQL
Указывает statement_text появляется в контексте изолированной инструкции или пакета, который может быть передан в SQL ServerSQL Server любым способом.Indicates statement_text appears in the context of a stand-alone statement or batch that can be submitted to SQL ServerSQL Server through any mechanism. Transact-SQLTransact-SQL инструкциях, отправленных с объекта (CLR) среды CLR или расширенных хранимых процедур, или с помощью инструкции EXEC EXEC N'sql_string", обрабатываются как пакеты на сервере и, следовательно, могут быть определены как @тип = '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'. Если указан SQL, указание запроса, PARAMETERIZATION {FORCED | ПРОСТОЙ} не может указываться в @указания параметра.If SQL is specified, the query hint PARAMETERIZATION { FORCED | SIMPLE } cannot be specified in the @hints parameter.

TEMPLATETEMPLATE
Указывает, что структура плана применяется к любым запросам, параметризированным в форме, заданной в statement_text.Indicates the plan guide applies to any query that parameterizes to the form indicated in statement_text. Если ШАБЛОН указан, только PARAMETERIZATION {FORCED | Указание ПРОСТОГО} запроса можно указать в @указания параметра.If TEMPLATE is specified, only the PARAMETERIZATION { FORCED | SIMPLE } query hint can be specified in the @hints parameter. Дополнительные сведения о структуры плана TEMPLATE, см. в разделе укажите параметризации запросов с с помощью структур плана.For more information about TEMPLATE plan guides, see Specify Query Parameterization Behavior by Using Plan Guides.

[@module_or_batch =] {N "[ schema_name.[@module_or_batch =]{ N'[ schema_name. ] object_name"| N'batch_text"| NULL}] object_name' | N'batch_text' | NULL }
Указывает имя объекта, в котором statement_text отображается, или текст пакета, в котором statement_text отображается.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.

Структура плана совпадала с пакетом, переданным из приложения batch_text должно быть указано в том же формате, символ к символу, так как оно передается в SQL ServerSQL Server.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 ServerSQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются.No internal conversion is performed to facilitate this match. Дополнительные сведения см. в разделе «Примечания».For more information, see the Remarks section.

[schema_name.] object_name имя Transact-SQLTransact-SQL хранимые процедуры, скалярные функции, многооператорной функции с табличным или Transact-SQLTransact-SQL триггера DML, содержащего statement_text.[schema_name.]object_name specifies the name of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger that contains statement_text. Если schema_name не указан, schema_name используется схема текущего пользователя.If schema_name is not specified, schema_name uses the schema of the current user. Если указано значение NULL и @тип = «SQL», значение @module_or_batch присваивается значение @stmt. Если @тип = "ШАБЛОН " , @module_or_batch должен иметь значение NULL.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}[ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
Указывает определения всех параметров, внедренных в statement_text.Specifies the definitions of all parameters that are embedded in statement_text. @params применяется, только если одно из следующих имеет значение true:@params applies only when either of the following is true:

  • @Тип = 'SQL' или 'TEMPLATE'.@type = 'SQL' or 'TEMPLATE'. Если «TEMPLATE», @params не должен иметь значение NULL.If 'TEMPLATE', @params must not be NULL.

  • statement_text передается при помощи процедуры sp_executesql и значение @указан параметр params, или SQL ServerSQL Server выполняет внутреннюю отправку инструкции после ее параметризации.statement_text is submitted by using sp_executesql and a value for the @params parameter is specified, or SQL ServerSQL Server internally submits a statement after parameterizing it. Отправка параметризованных запросов через API-интерфейсы базы данных (включая ODBC, OLE DB и ADO.NET) в SQL ServerSQL Server выглядит как вызов процедуры sp_executesql либо API-процедуры серверного курсора, поэтому они также могут совпадать со структурой плана SQL или TEMPLATE.Submission of parameterized queries from database APIs (including ODBC, OLE DB, and ADO.NET) appear to SQL ServerSQL Server as calls to sp_executesql or to API server cursor routines; therefore, they can also be matched by SQL or TEMPLATE plan guides.

@имя_параметра data_type должны быть указаны в формате, так как оно передается в SQL ServerSQL Server либо с помощью процедуры sp_executesql или автоматической отправки после параметризации.@parameter_name data_type must be supplied in the exact same format as it is submitted to SQL ServerSQL Server either by using sp_executesql or submitted internally after parameterization. Дополнительные сведения см. в разделе «Примечания».For more information, see the Remarks section. Если пакет не содержит параметров, необходимо указать значение NULL.If the batch does not contain parameters, NULL must be specified. Размер @params ограничивается только доступной памяти на сервере.The size of @params is limited only by available server memory.

[@указания =] {N'OPTION (query_hint [, ... n ]) "| N'XML_showplan"| NULL}[@hints = ]{ N'OPTION (query_hint [ ,...n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [, ... n ])N'OPTION (query_hint [ ,...n ] )
Указывает предложение OPTION, чтобы присоединить к запросу, который соответствует @stmt. @подсказки должен синтаксически так же, как предложение OPTION в инструкции SELECT и может содержать любую допустимую последовательность указаний запроса.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"N'XML_showplan'
План запроса в формате XML для применения в качестве указания.Is the query plan in XML format to be applied as a hint.

Значение аргумента XML_showplan рекомендуется присвоить переменной, иначе каждый символ одиночной кавычки необходимо предварять дополнительным символом одиночной кавычки.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.

NULLNULL
Указывает, что любое существующее указание, заданное в предложении OPTION запроса, не применяется к запросу.Indicates that any existing hint specified in the OPTION clause of the query is not applied to the query. Дополнительные сведения см. в разделе предложение OPTION (Transact-SQL).For more information, see OPTION Clause (Transact-SQL).

ПримечанияRemarks

Аргументы процедуры sp_create_plan_guide должны задаваться в указанном порядке.The arguments to sp_create_plan_guide must be provided in the order that is shown. При задании значений параметрам процедуры sp_create_plan_guideвсе имена параметров необходимо указывать явно или вообще не указывать.When you supply values for the parameters of sp_create_plan_guide, all parameter names must be specified explicitly, or none at all. Например если @имя = указан, то @stmt = , @тип = , и т. д., также должен быть указан.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 ServerSQL Server не проверяет соответствие указанных имен параметров их позициям.does not verify that the specified parameter name matches the name for the parameter in the position where the name is used.

Можно создать несколько структур планов OBJECT или SQL для одного и того же запроса и пакета либо модуля.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.

Структуры планов типа, объект не может быть создан для @module_or_batch значение, которое ссылается на хранимую процедуру, функцию или триггер DML, который задает предложение WITH ENCRYPTION или является временным.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.

Попытка удаления или изменения функции, хранимой процедуры или триггера DML, на которые имеется ссылка в структуре плана (как включенных, так и отключенных), приводит к ошибке.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.

Примечание

Структуру планов можно использовать не во всех выпусках MicrosoftMicrosoftSQL ServerSQL Server.Plan guides cannot be used in every edition of MicrosoftMicrosoftSQL ServerSQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL ServerSQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. Структуры планов видны в любом выпуске.Plan guides are visible in any edition. Можно также присоединить базу данных, содержащую структуры планов, к любой версии.You can also attach a database that contains plan guides to any edition. Структуры планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL ServerSQL Server.Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server. Следует тщательно взвешивать необходимость использования структур планов в каждой базе данных после выполнения обновления сервера.You should verify the desirability of the plan guides in each database after performing a server upgrade.

Требования по соответствию для структур плановPlan Guide Matching Requirements

Для структур планов, которые указывают @тип = 'SQL' или @тип = 'TEMPLATE' для должного соответствия запросу, значения для batch_text и @parameter_name data_type [, ... n ] должно быть указано в том же формате, совпадают с аналогичными приложения.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. Это означает, что необходимо предоставить текст пакета в точном соответствии с текстом, получаемым компилятором SQL ServerSQL Server.This means you must provide the batch text exactly as the SQL ServerSQL Server compiler receives it. Для захвата действительного текста пакета и параметра используется приложение Приложение SQL Server ProfilerSQL Server Profiler.To capture the actual batch and parameter text, you can use Приложение SQL Server ProfilerSQL Server Profiler. Дополнительные сведения см. в разделе использования SQL Server Profiler для создания и проверки руководств планов.For more information, see Use SQL Server Profiler to Create and Test Plan Guides.

Когда @тип = 'SQL' и @module_or_batch имеет значение NULL, значение @module_or_batch присваивается значение @stmt. Это означает, что значение statement_text должно быть указано в формате, символ к символу, так как оно передается в SQL ServerSQL Server.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 ServerSQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются.No internal conversion is performed to facilitate this match.

Когда SQL ServerSQL Server совпадает со значением statement_text для batch_text и @parameter_name data_type [, ... n ], или если @тип = " объект", к тексту соответствующего запроса внутри object_name, не учитываются следующие элементы строки:When SQL ServerSQL 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

Например SQL ServerSQL Server может соответствовать statement_text строка N'SELECT * FROM T WHERE a = 10' следующим batch_text:For example, SQL ServerSQL 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'

Однако та же строка не совпадет к этому batch_text:However, the same string would not be matched to this batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL ServerSQL Server пропускает знаки возврата каретки, перевода строки и пробелы внутри первого запроса.ignores the carriage return, line feed, and space characters inside the first query. При рассмотрении второго запроса строки WHERE b = 10 и WHERE a = 10 считаются различными.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. Например, ключевые слова EXECUTE, EXEC и execute являются эквивалентными.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. Создание структуры плана типа OBJECT или SQL в потоке стирает план запроса для потока, который имеет такое же значение хеш-функции.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. Создание структуры плана типа TEMPLATE стирает все потоки с одним оператором из кэша планов через базу данных.Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

РазрешенияPermissions

Для создания структуры плана типа OBJECT необходимо разрешение ALTER на соответствующий объект.To create a plan guide of type OBJECT, requires ALTER permission on the referenced object. Чтобы создать структуру плана типа SQL или TEMPLATE, необходимо обладать разрешением ALTER на текущую базу данных.To create a plan guide of type SQL or TEMPLATE, requires ALTER permission on the current database.

ПримерыExamples

A.A. Создание структуры плана типа OBJECT для запроса в хранимой процедуреCreating a plan guide of type OBJECT for a query in a stored procedure

В приведенном ниже примере создается структура плана, с которой сопоставляется запрос, выполняемый в контексте хранимой процедуры приложения, а также происходит применение к запросу указания OPTIMIZE FOR.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. Создание структуры плана типа SQL для изолированного запросаCreating a plan guide of type SQL for a stand-alone query

В приведенном ниже примере создается структура плана, с которой сопоставляется запрос из пакета, переданного приложением, использующим системную хранимую процедуру sp_executesql.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. Создание структуры плана типа TEMPLATE для параметризованного запросаCreating a plan guide of type TEMPLATE for the parameterized form of a query

В приведенном ниже примере создается структура плана, которой сопоставляется запрос заданной параметризованной формы и которое вынуждает SQL ServerSQL Server параметризовать поступающие запросы.The following example creates a plan guide that matches any query that parameterizes to a specified form, and directs SQL ServerSQL 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)';  

В предыдущем примере значение параметра @stmt является параметризованной формой запроса.In the previous example, the value for the @stmt parameter is the parameterized form of the query. Единственным надежным способом выяснения указанного значения для использования в процедуре sp_create_plan_guide является использование системной хранимой процедуры sp_get_query_template .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)';  

Важно!

Значения постоянных литералов аргумента @stmt, передаваемого в процедуру sp_get_query_template, определяют тип данных для аргумента, заменяющего указанные литералы.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. Создание структуры плана на основе запроса, переданного с помощью запроса курсора APICreating a plan guide on a query submitted by using an API cursor request

Структуры планов могут совпадать с запросами, передаваемыми с помощью процедур серверных курсоров API.Plan guides can match queries that are submitted from API server cursor routines. К указанным процедурам относятся sp_cursorprepare, sp_cursorprepexec и sp_cursoropen.These routines include sp_cursorprepare, sp_cursorprepexec, and sp_cursoropen. Приложения, использующие API-интерфейсы ADO, OLE DB и ODBC, часто связываются с SQL ServerSQL Server при помощи серверных курсоров API.Applications that use the ADO, OLE DB, and ODBC APIs frequently interact with SQL ServerSQL Server by using API server cursors. Вызов процедур серверного курсора API-интерфейса можно увидеть в трассировках приложения Приложение SQL Server ProfilerSQL Server Profiler, просматривая событие трассировки SQL Profiler RPC:Starting.You can see the invocation of API server cursor routines in Приложение SQL Server ProfilerSQL Server Profiler traces by viewing the RPC:Starting profiler trace event.

Допустим, что для запроса, настраиваемого с помощью структуры плана, в событии трассировки RPC:Starting приложения SQL Profiler имеются следующие данные.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;  

Пусть необходимо изменить следующую настройку запроса SELECT вызова процедуры sp_cursorprepexec: используемое соединение слиянием необходимо заменить хэш-соединением.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. Запрос, передаваемый с помощью процедуры sp_cursorprepexec, является параметризованным, включая строку запроса и строку параметров.The query submitted by using sp_cursorprepexec is parameterized, including both a query string and a parameter string. В точности копируя строки запроса и параметров вызова процедуры sp_cursorprepexec, можно создать следующую структуру плана.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. Создание структуры плана с помощью получения данных XML Showplan из плана в кэш-памятиCreating a plan guide by obtaining the XML Showplan from a cached plan

В следующем примере создается структура плана для простой нерегламентированной инструкции SQL.The following example creates a plan guide for a simple ad hoc SQL statement. Требуемый план запроса для этого оператора представлен в структуре плана путем указания XML Showplan для запроса непосредственно в параметре @hints .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. В примере сначала выполняется инструкция SQL для создания плана в кэше планов.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. Данные XML Showplan для запроса необходимо получить с помощью запроса к динамическим административным представлениям sys.dm_exec_query_stats, sys.dm_exec_sql_textи sys.dm_exec_text_query_plan и присвоить переменной @xml_showplan .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. Переменная @xml_showplan затем передается оператору sp_create_plan_guide в параметре @hints .The @xml_showplan variable is then passed to the sp_create_plan_guide statement in the @hints parameter. Также можно создать структуру плана на основе плана запроса в кэше планов, используя хранимую процедуру sp_create_plan_guide_from_handle .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  

См. такжеSee Also

Структуры планов Plan Guides
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
Хранимым процедурам ядра СУБД (Transact-SQL) Database Engine Stored Procedures (Transact-SQL)
Системные хранимые процедуры (Transact-SQL) System Stored Procedures (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL) sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL) sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL) sys.fn_validate_plan_guide (Transact-SQL)
Хранимая процедура sp_get_query_template (Transact-SQL)sp_get_query_template (Transact-SQL)