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 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.

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

ПримечаниеПримечание

Структуру планов можно использовать не во всех выпусках Microsoft SQL Server. Список функций, поддерживаемых в разных выпусках SQL Server, см. в разделе Возможности, поддерживаемые различными выпусками SQL Server 2012. Структуры планов видны в любом выпуске. Можно также присоединить базу данных, содержащую структуры планов, к любой версии. Структуры планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL Server. Следует тщательно взвешивать необходимость использования структур планов в каждой базе данных после выполнения обновления сервера.

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

Для структур планов, содержащих @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 на текущую базу данных.

Примеры

А.Создание структуры плана типа 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 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;

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

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 является параметризованной формой запроса. Единственным надежным способом получения указанного значения для использования в процедуре sp_create_plan_guide является вызов системной хранимой процедуры sp_get_query_template. Следующий скрипт можно использовать как для получения параметризированного запроса, так и для дальнейшего создания по нему структуры плана:

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

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

Структуры планов могут совпадать с запросами, передаваемыми с помощью процедур серверных курсоров API. В число этих подпрограмм входят sp_cursorprepare, sp_cursorprepexec и sp_cursoropen. Приложения, использующие API-интерфейсы ADO, OLE DB и ODBC, часто связываются с SQL Server при помощи серверных курсоров API. Вызов процедур серверного курсора API-интерфейса вы можете увидеть в трассировках приложения Приложение SQL Server 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 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: используемое соединение слиянием необходимо заменить хэш-соединением. Запрос, передаваемый с помощью процедуры 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)';

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

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

См. также

Справочник

Хранимая процедура 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)

Основные понятия

Руководства планов