sp_create_plan_guide (Transact-SQL)

Статья
12/15/2008

Изменения: 15 сентября 2007 г.

Создает руководство плана для связывания подсказок в запросе с запросами в базе данных. Дополнительные сведения о руководстве плана см. в разделе Оптимизация запросов в используемых приложениях с помощью руководств планов.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

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 ] )' | NULL }

Аргументы

[ @name= ] N'plan_guide_name'
Указывает имя для обозначения руководства плана. Имена руководства плана принадлежат области текущей базы данных. Аргумент plan_guide_name должен соответствовать правилам для идентификаторов и не может начинаться со знака числа (#).

[ @stmt= ] N'statement_text'
Инструкция языка Transact-SQL, для которой создается руководство плана. Когда на оптимизатор запросов SQL Server поступает запрос, удовлетворяющий аргументу statement_text, активируется аргумент plan_guide_name. Для успешного создания руководства плана аргумент statement_text должен указываться в контексте параметров @type, @module_or_batch и @params.

Аргумент statement_text должен быть задан таким образом, чтобы SQL Server смог сравнить его с соответствующей инструкцией, заданной внутри пакета или модуля с помощью аргументов @module_or_batch и @params. Прежде чем SQL Server попытается провести указанное сопоставление, аргумент statement_text приводится к стандартной внутренней форме (пробельные символы, комментарии и регистр ключевых слов не учитываются). Дополнительные сведения см. в разделе «Примечания». Объем, занимаемый аргументом statement_text, ограничивается только размерами доступной памяти на сервере.

[ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
Тип сущности, в которой задается аргумент statement_text. Таким образом задается контекст для сравнения аргумента statement_text с аргументом plan_guide_name.
- OBJECT
  Указывает, что аргумент statement_text появляется в контексте хранимой процедуры языка Transact-SQL, скалярной функции, многострочной возвращающей табличное значение функции или внутри DML-триггера языка Transact-SQL в текущей базе данных.
- SQL
  Показывает, что аргумент statement_text находится в контексте изолированной инструкции или пакета, который может быть передан в SQL Server с помощью любого механизма. Инструкции языка Transact-SQL, включенные либо с помощью объектов среды CLR или расширенных хранимых процедур, либо с помощью инструкции EXEC N'sql_string', обрабатываются сервером как пакеты и поэтому должны задаваться следующим образом: @type ='SQL'. Если указан параметр SQL, в параметре запроса @hints нельзя указывать подсказку запроса PARAMETERIZATION { FORCED | SIMPLE }.
- TEMPLATE
  Указывает, что руководство плана применяется к любым запросам, параметризированным в форме, заданной в аргументе statement_text. Если указан параметр TEMPLATE, то в аргументе @hints может быть указана только подсказка в запросе PARAMETERIZATION { FORCED | SIMPLE }. Дополнительные сведения о руководствах плана с параметром TEMPLATE см. в разделе Указание механизма параметризации запросов с помощью руководств плана.

[ @module_or_batch = ] { N'[ schema_name**.** ] object_name**'** | N'batch_text' | NULL }
Указывает имя объекта либо текст пакета, в котором появляется аргумент statement_text. Текст пакета не может содержать инструкцию USE database.

Чтобы руководство плана совпадало с пакетом, переданным из приложения, необходимо, чтобы аргумент batch_tex предоставлялся в том же самом формате (с точностью до символа), в котором он передается в SQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются. Дополнительные сведения см. в разделе «Примечания».

[schema_name.] Аргумент object_name указывает имя хранимой процедуры языка Transact-SQL, скалярной или многострочной возвращающей табличное значение функции или DML-триггера языка Transact-SQL, в которых содержится аргумент statement_text. Если аргумент schema_name не указан, то в аргументе schema_name используется схема текущего пользователя. Если указано значение NULL и @type='SQL', то аргументу @module_or_batch присваивается значение @stmt. Если аргумент @type='TEMPLATE', то аргумент @module_or_batch должен иметь значение NULL.

[ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
Указывает определения всех аргументов, встроенных в аргумент statement_text. Аргумент @params применяется только при выполнении следующих условий.
- @type='SQL' или 'TEMPLATE'. Если указано значение 'TEMPLATE', то аргумент @params не может иметь значение NULL.
- Аргумент statement_text передается с помощью хранимой процедуры sp_executesql при указании значения аргумента @params, или SQL Server выполняет внутреннюю отправку инструкции после ее параметризации. Отправка параметризованных запросов через интерфейс API базы данных (включая ODBC, OLE DB и ADO.NET) в SQL Server выглядит как вызов процедуры sp_executesql либо API-процедуры курсора; поэтому они также могут совпадать с руководствами плана SQL или TEMPLATE. Дополнительные сведения о параметризации и руководствах плана см. в разделе Как SQL Server сопоставляет руководство планов запросам.
Аргумент @parameter_name data_type необходимо указать в формате, совпадающем с форматом отправки на SQL Server. Этого можно добиться либо с помощью процедуры sp_executesql, либо путем автоматической отправки после параметризации. Дополнительные сведения см. в разделе «Примечания». Если пакет не содержит параметров, необходимо указать значение NULL. Объем, занимаемый аргументом @params, ограничен только размерами доступной памяти на сервере.

[@hints = ] { **N'**OPTION **(**query_hint [ ,...n ] )' | NULL }
Указывает предложение OPTION, которое необходимо присоединить к запросу, совпадающему с параметром @stmt. Аргумент @hints должен синтаксически совпадать с предложением OPTION инструкции SELECT и может содержать любую допустимую последовательность подсказок в запросе. Значение NULL означает отсутствие предложения OPTION. Дополнительные сведения см. в разделе Предложение OPTION (Transact-SQL).

Замечания

Аргументы процедуры sp_create_plan_guide должны задаваться в указанном порядке. При задании значений параметрам процедуры sp_create_plan_guide все имена параметров необходимо указывать явно или вообще не указывать. Например, если указан параметр @name =, необходимо также указать параметры @stmt =, @type = и т. д. Подобным образом, если параметр @name = пропущен и указано только его значение, имена остальных параметров должны быть также пропущены и должны быть указаны только их значения. Имена аргументов приводятся только в описательных целях, для понимания синтаксиса. SQL Server не проверяет соответствие указанных имен параметров их позициям.

Для каждого сочетания @module_or_batch и @stmt может быть создано только одно руководство плана.

Нельзя создавать руководства плана типа OBJECT для значения @module_or_batch, ссылающегося на хранимую процедуру, функцию или триггер DML, который задает предложение WITH ENCRYPTION или является временным.

Попытка удаления или изменения функции, хранимой процедуры или DML-триггера, на которые ссылается руководство плана (как включенное, так и отключенное), вызывает ошибку. Попытка удалить таблицу, для которой определен триггер, имеющий соответствующую ссылку в руководстве плана, также вызывает ошибку.

Примечание.
Руководства планов могут использоваться только в выпусках SQL Server Standard, Developer, Evaluation и Enterprise, однако они видны в любой версии. Можно также присоединить базу данных, содержащую руководства планов, к любой версии. Руководства планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL Server 2008. Следует тщательно взвешивать необходимость использования руководств планов в каждой базе данных после выполнения обновления сервера.

Руководства планов могут использоваться только в выпусках SQL Server Standard, Developer, Evaluation и Enterprise, однако они видны в любой версии. Можно также присоединить базу данных, содержащую руководства планов, к любой версии. Руководства планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL Server 2008. Следует тщательно взвешивать необходимость использования руководств планов в каждой базе данных после выполнения обновления сервера.

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

Чтобы запрос совпадал с руководствами плана, которые указывают @type='SQL' или @type='TEMPLATE', значения аргументов batch_text и @parameter_name data_type [,...n ] необходимо задавать в формате, используемом для определения соответствующих им параметров приложения. Это означает, что необходимо предоставить текст пакета в точном соответствии с текстом, получаемым компилятором SQL Server. Для захвата действительного текста пакета и параметра используется приложение SQL Server Profiler. Дополнительные сведения см. в разделе Использование приложения SQL Server Profiler для создания и проверки руководств планов.

Если параметры @type = 'SQL' и @module\_or\_batch имееют значение NULL, то значение параметра @module\_or\_batch устанавливается равным @stmt. Это значит, что значение statement_text должно быть объявлено в точно таком же формате, символ за символом, как предложено в SQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются.

Когда SQL Server сравнивает значение аргумента statement_text с аргументом batch_text и с аргументом @parameter_name data_type [,...n ] или (в случае, когда @type='OBJECT') с текстом соответствующего запроса внутри аргумента object_name, не рассматриваются следующие элементы строки.

Пробельные символы (знаки табуляции, пробелы, возвраты каретки и переводы строки) внутри строки.
Комментарии (-- или /* */).
Точка с запятой (;) в конце строки.

Например, в SQL Server могут совпадать строка N'SELECT * FROM T WHERE a = 10' аргумента statement_text и значение следующего аргумента batch_text.

N'SELECT *

FROM T

WHERE a=10'

Однако та же строка не совпадет с этим значением аргумента batch_text.

N'SELECT * FROM T WHERE b = 10'

SQL Server игнорирует знаки возврата каретки, перевода строки и пробелы внутри первого запроса. При рассмотрении второго запроса строки WHERE b = 10 и WHERE a = 10 считаются различными. Результат сравнения учитывает регистр и наличие диакритических знаков (даже в случае, когда в параметрах сортировки базы данных задана сортировка без учета регистра), за исключением тех случаев, когда используются ключевые слова, игнорирующие регистр. Сравнение выполняется без учета сокращенных форм ключевых слов. Например, ключевые слова EXECUTE, EXEC и execute являются эквивалентными.

Дополнительные сведения о том, как руководство плана сравнивается с запросами, см. в разделе Оптимизация запросов в используемых приложениях с помощью руководств планов.

Влияние руководств планов на кэш планов

Создание руководства плана в модуле стирает план запроса для этого модуля из кэша планов. Создание руководства плана типа OBJECT или SQL в пакете стирает план запроса для пакета, который имеет такое же значение хэш-функции. Создание руководства плана типа TEMPLATE стирает все пакеты с одним оператором из кэша планов через базу данных.

Разрешения

Чтобы создать руководство плана типа OBJECT (с помощью указания @type='OBJECT'), необходимо обладать разрешением ALTER на указанный объект. Чтобы создать руководство плана типа SQL или TEMPLATE, необходимо обладать разрешением ALTER на текущую базу данных.

Примеры

A. Создание руководства плана типа OBJECT для запроса в хранимой процедуре

В приведенном ниже примере создается руководство плана, которому сопоставляется запрос, выполняемый в контексте хранимой процедуры приложения, а также происходит применение к запросу подсказки OPTIMIZE FOR.

Ниже приведена хранимая процедура.

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country 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;
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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';

Б. Создание руководства плана типа SQL для изолированного запроса

В приведенном ниже примере создается руководство плана, которому сопоставляется запрос из пакета, переданного приложением, использующим системную хранимую процедуру sp_executesql.

Ниже представлен пакет.

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

В. Создание руководства плана типа TEMPLATE для параметризованного запроса

В приведенном ниже примере создается руководство плана, которому сопоставляется запрос заданной параметризованной формы и которое вынуждает SQL Server параметризовать поступающие запросы. Два приведенных ниже запроса являются синтаксическими эквивалентами, однако различаются своими значениями постоянных литералов.

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;

Ниже представлено руководство плана для параметризованного запроса.

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

В предыдущем примере значение аргумента @stmt является параметризованной формой запроса. Единственным надежным способом выяснения указанного значения для использования в процедуре sp_create_plan_guide является использование системной хранимой процедуры sp_get_query_template. С помощью следующего сценария можно выполнить параметризованный запрос, а затем создать на его основе руководство плана.

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

Важно!
Значения постоянных литералов аргумента `@stmt`, передаваемого в процедуру sp_get_query_template, определяют тип данных для аргумента, заменяющего указанные литералы. Это влияет на совпадение руководств планов. Возможно, придется создать несколько руководств плана для обработки различных диапазонов значений аргумента.

Важно!

Значения постоянных литералов аргумента @stmt, передаваемого в процедуру sp_get_query_template, определяют тип данных для аргумента, заменяющего указанные литералы. Это влияет на совпадение руководств планов. Возможно, придется создать несколько руководств плана для обработки различных диапазонов значений аргумента.

Дополнительные сведения о выполнении параметризованных запросов при использовании руководства плана, основанного на TEMPLATE, см. в разделе Конструирование руководств планов для параметризованных запросов.

Г. Создание руководства плана на основе запроса, переданного с помощью запроса курсора API

Руководства плана могут совпадать с запросами, передаваемыми с помощью процедур серверных курсоров API. К указанным процедурам относятся sp_cursorprepare, sp_cursorprepexec и sp_cursoropen. Приложения, использующие интерфейс ADO, OLE DB и ODBC, обычно связываются с SQL Server при помощи серверных курсоров API. Дополнительные сведения см. в разделе Серверные курсоры API. Вызов процедур серверного курсора API можно увидеть в трассировках приложения SQL Server Profiler, просматривая событие трассировки SQL Profiler RPC:Starting.

Допустим, что для запроса, настраиваемого с помощью руководства плана, в событии трассировки RPC:Starting приложения SQL Profiler появляются следующие данные.

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;

Пусть необходимо изменить следующую настройку запроса SELECT вызова процедуры sp_cursorprepexec: используемое соединение слиянием необходимо заменить хэш-соединением. Запрос, передаваемый с помощью процедуры sp_cursorprepexec, является параметризованным, включая строку запроса и строку параметров. В точности копируя строки запроса и параметров вызова процедуры 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)';

При последующем выполнении приложением данный запрос будет соответствовать руководству плана, и для обработки запроса будет использоваться хэш-соединение.

Дополнительные сведения об использовании подсказки в запросе USE PLAN в руководстве плана для выполнения запросов, переданных курсором, см. в разделе Использование подсказки USE PLAN в запросах с курсорами.

См. также

Справочник

Хранимая процедура sp_control_plan_guide (Transact-SQL)
sys.plan_guides
Хранимые процедуры ядра СУБД (Transact-SQL)
Системные хранимые процедуры (Transact-SQL)

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
5 декабря 2005 г.	Добавления: Добавлено, что подсказку запроса PARAMETERIZATION { FORCED \| SIMPLE } нельзя указывать для структур планов SQL. В подразделе «Примечания» уточнены правила порядка и согласованности синтаксиса. Измененное содержимое: Уточнено, что руководства планов OBJECT не могут ссылаться на зашифрованные или временные объекты.
15 сентября 2007 г.	Обновления Пояснены требования для сопоставления руководства плана запросу statement_text, если параметр @type = 'SQL', а значение параметра @module_or_batch установлено равным NULL. Добавлены сведения о влиянии создания руководств планов на кэш планов.

5 декабря 2005 г.

Добавления:

Добавлено, что подсказку запроса PARAMETERIZATION { FORCED | SIMPLE } нельзя указывать для структур планов SQL.
В подразделе «Примечания» уточнены правила порядка и согласованности синтаксиса.

Измененное содержимое:

Уточнено, что руководства планов OBJECT не могут ссылаться на зашифрованные или временные объекты.

15 сентября 2007 г.

Обновления

Пояснены требования для сопоставления руководства плана запросу statement_text, если параметр @type = 'SQL', а значение параметра @module_or_batch установлено равным NULL.
Добавлены сведения о влиянии создания руководств планов на кэш планов.

Share via