sp_create_plan_guide (Transact-SQL)

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

Синтаксические обозначения в 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 ] )' 
                 | N'XML_showplan'
                 | NULL }

Аргументы

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

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

  Аргумент statement_text должен быть представлен способом, позволяющим оптимизатору запросов сопоставить с соответствующей инструкцией, которая получена в рамках пакета или модуля, идентифицируемого по значениям @module\_or\_batch и @params. Дополнительные сведения см. в разделе «Примечания». Объем, занимаемый аргументом 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 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. Текст пакета не может содержать инструкцию USEdatabase.

  Чтобы структура плана совпадала с пакетом, переданным из приложения, необходимо, чтобы аргумент 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 ] )' | N'XML_showplan' | NULL }

  • N'OPTION ( query_hint [ ,...n ] )
    Указывает предложение OPTION, которое необходимо присоединить к запросу, соответствующему параметру @stmt. Аргумент @hints должен синтаксически совпадать с предложением OPTION инструкции SELECT и может содержать любую допустимую последовательность подсказок в запросе.

  • N'XML_showplan'
    План запроса в формате XML для применения в качестве подсказки.

    Значение аргумента XML_showplan рекомендуется присвоить переменной, иначе каждый символ одиночной кавычки необходимо предварять дополнительным символом одиночной кавычки. См. пример Д.

  • NULL
    Указывает, что любая существующая подсказка, заданная в предложении OPTION запроса, не применяется к запросу. Дополнительные сведения см. в разделе Предложение OPTION (Transact-SQL).

Замечания

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

Можно создать несколько структур планов OBJECT или SQL для одного и того же запроса и пакета либо модуля. Однако только одна структура плана может быть включена в данный момент времени.

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

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

Примечание

Структуры планов могут использоваться только в выпусках 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 необходимо разрешение 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_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''))';

Б. Создание структуры плана типа 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, определяют тип данных для аргумента, заменяющего указанные литералы. Это влияет на совпадение структур планов. Возможно, придется создать несколько структур планов для обработки различных диапазонов значений аргумента.

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

Д. Создание структуры плана с помощью получения данных XML Showplan из плана в кэш-памяти

В следующем примере создается структура плана для простой нерегламентированной инструкции SQL. Требуемый план запроса для этого оператора представлен в структуре плана путем указания XML Showplan для запроса непосредственно в параметре @hints. В примере сначала выполняется инструкция SQL для создания плана в кэше планов. В этом примере допустим, что созданный план является желаемым планом и не требуется дополнительной настройки запросов. Данные XML Showplan для запроса необходимо получить с помощью запроса к динамическим административным представлениям sys.dm_exec_query_stats, sys.dm_exec_sql_text и sys.dm_exec_text_query_plan и присвоить переменной @xml\_showplan. Переменная @xml\_showplan затем передается оператору sp_create_plan_guide в параметре @hints. Также можно создать структуру плана в кэше планов, используя хранимую процедуру sp_create_plan_guide_from_handle.

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

См. также

Справочник

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

Хранимые процедуры ядра СУБД (Transact-SQL)

Системные хранимые процедуры (Transact-SQL)

sys.dm_exec_sql_text (Transact-SQL)

sys.dm_exec_cached_plans (Transact-SQL)

sys.dm_exec_query_stats (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

sp_get_query_template (Transact-SQL)