sp_create_plan_guide (Transact-SQL)

Применимо к: SQL Server (все поддерживаемые версии) Azure SQL Управляемый экземпляр SQL Azure базы данных

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

Topic link iconСинтаксические обозначения в 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_textplan_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 , или пакетный текст, в котором отображается statement_text . Пакетный текст не может содержать инструкциюUSE database .

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

Примечание

Структуры планов можно использовать не во всех выпусках MicrosoftSQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016. Структуры планов видны в любом выпуске. Можно также присоединить базу данных, содержащую структуры планов, к любой версии. Структуры планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии 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_textbatch_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 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 profiler.

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