sp_create_plan_guide (Transact-SQL)

Статья
07/17/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

sp_create_plan_guide
    [ @name = ] N'name'
    [ , [ @stmt = ] N'stmt' ]
    , [ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }
    [ , [ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' } ]
    [ , [ @params = ] N'@parameter_name data_type [ ,... n ]' ]
    [ , [ @hints = ] { N'OPTION ( query_hint [ , ...n ] )' | N'XML_showplan' } ]
[ ; ]

Аргументы

[ @name = ] N'name'

Имя руководства по плану. @name — sysname, без значения по умолчанию и максимальная длина 124 символов. Имена структур планов ограничены областью текущей базы данных. @name должны соответствовать правилам идентификаторов и не могут начинаться с знака номера (#).

[ @stmt = ] N'stmt'

Инструкция Transact-SQL, для которой создается руководство по плану. @stmt — nvarchar(max), с значением по умолчаниюNULL. Когда оптимизатор запросов SQL Server распознает запрос, соответствующий @stmt, @name вступает в силу. Для успешного создания руководства по плану @stmt должен отображаться в контексте, указанном в параметрах @type, @module_or_batch и @params.

@stmt необходимо предоставить таким образом, чтобы оптимизатор запросов соответствовал ему с соответствующей инструкцией, предоставленной в пакете или модуле, определяемой @module_or_batch и @params. Дополнительные сведения см. в разделе с примечаниями. Размер @stmt ограничен только доступной памятью сервера.

[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }

Тип сущности, в которой отображается @stmt . Это указывает контекст сопоставления @stmt с @name. @type является nvarchar(60) и может быть одним из следующих значений:

OBJECT

Указывает, что @stmt отображается в контексте хранимой процедуры Transact-SQL, скалярной функции, функции с табличным значением или триггера Transact-SQL DML в текущей базе данных.
SQL

Указывает, что @stmt отображается в контексте автономной инструкции или пакета, которую можно отправить в SQL Server с помощью любого механизма. Инструкции Transact-SQL, отправленные объектами среды CLR или расширенными хранимыми процедурами, или с помощьюEXEC N'<sql_string>', обрабатываются в виде пакетов на сервере и, следовательно, должны быть определены как @typeSQL. Если SQL задано, указание PARAMETERIZATION { FORCED | SIMPLE } запроса невозможно указать в параметре @hints .
TEMPLATE

Указывает, что руководство по плану применяется к любому запросу, который параметризирует форму, указанную в @stmt. Если TEMPLATE задано, в параметре @hints можно указать только PARAMETERIZATION { FORCED | SIMPLE } указание запроса. Дополнительные сведения о руководствах по плану см. в TEMPLATE разделе "Указание поведения параметризации запросов" с помощью руководств по плану.

[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }

Указывает имя объекта, в котором отображается @stmt , или пакетный текст, в котором отображается @stmt . @module_or_batch — nvarchar(max), с значением по умолчаниюNULL. Пакетный текст не может содержать инструкцию USE <database> .

Чтобы руководство по плану соответствовало пакету, отправленному из приложения, @module_or_batch должны быть предоставлены в том же формате, символе для символов, что и в SQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются. Дополнительные сведения см. в разделе с примечаниями.

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

[ @params = ] N'@parameter_name data_type [ ,... n ]'

Задает определения всех параметров, внедренных в @stmt. @params — nvarchar(max), с значением по умолчаниюNULL. @params применяется только в том случае, если один из следующих параметров имеет значение true:

@type есть SQL или TEMPLATE. Если TEMPLATE@params не должно бытьNULL.
@stmt отправляется с помощью и sp_executesql указывается значение параметра @params, или SQL Server внутренне отправляет инструкцию после параметризации. Отправка параметризованных запросов из API базы данных (включая ODBC, OLE DB и ADO.NET) появляется в SQL Server в качестве вызовов sp_executesql или подпрограмм курсоров сервера API, поэтому они также могут соответствовать руководствам SQL по планам.TEMPLATE

@params должны быть предоставлены в том же формате, что и в SQL Server, с помощью sp_executesql или внутренней отправкой после параметризации. Дополнительные сведения см. в разделе с примечаниями. Если пакет не содержит параметров, NULL необходимо указать. Размер @params ограничен только доступной памятью сервера.

[ @hints = ] { N'OPTION ( query_hint [ , ... n ] )' | N'XML_showplan' }

@hints — nvarchar(max), с значением по умолчаниюNULL.

OPTION ( <query_hint> [ , ...n ] )

Указывает предложение для присоединения к запросуOPTION, который соответствует @stmt. @hints должны быть синтаксически совпадают с OPTION предложением в SELECT инструкции и могут содержать любую допустимую последовательность подсказок запроса.
<XML_showplan>'

План запроса в формате XML, который будет применяться в качестве указания.

Рекомендуется назначить xml-шоуплан переменной. В противном случае необходимо экранировать любые одинарные кавычки в шоуплане, выполнив перед ними другую одну кавычку. См . пример E.
NULL

Указывает, что к запросу не применяется любое существующее указание, указанное в OPTION предложении запроса. Дополнительные сведения см . в предложении OPTION.

Замечания

Аргументы, которые 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. Список функций, поддерживаемых выпусками SQL Server, см. в выпусках и поддерживаемых функциях SQL Server 2022. Структуры планов видны в любом выпуске. Можно также присоединить базу данных, содержащую структуры планов, к любой версии. Руководства по планированию остаются неизменными при восстановлении или присоединении базы данных к обновленной версии SQL Server. Следует тщательно взвешивать необходимость использования структур планов в каждой базе данных после выполнения обновления сервера.

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

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

Если задано значение @type и @module_or_batch, значение @module_or_batch SQL NULL присваивается значению @stmt. Это означает, что значение для @stmt должно быть предоставлено точно в том же формате, символе для символов, что и в SQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются.

Если SQL Server соответствует значению @stmt @module_or_batch и @params [, ... n ], или если @type является OBJECT, текст соответствующего запроса внутри<object_name>, следующие строковые элементы не считаются:

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

Например, SQL Server может соответствовать строке N'SELECT * FROM T WHERE a = 10' @stmt следующим @module_or_batch:

 N'SELECT *
 FROM T
 WHERE a = 10'

Однако та же строка не будет соответствовать этой @module_or_batch:

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

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

C. Создание руководства по плану типа TEMPLATE для параметризованной формы запроса

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

SELECT *
FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT *
FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2022.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 , определяют тип данных для аргумента, заменяющего указанные литералы. Это влияет на совпадение структур планов. Для обработки различных диапазонов значений параметров может потребоваться создать несколько руководств по плану.

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

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

Предположим, что следующие данные отображаются в RPC:Starting событии трассировки профилировщика для запроса, который вы хотите настроить с помощью руководства по плану:

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-шоуплана из кэшированного плана

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

USE AdventureWorks2022;
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_create_plan_guide (Transact-SQL)

Синтаксис

Аргументы

[ @name = ] N'name'

[ @stmt = ] N'stmt'

[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }

[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }

[ @params = ] N'@parameter_name data_type [ ,... n ]'

[ @hints = ] { N'OPTION ( query_hint [ , ... n ] )' | N'XML_showplan' }

Замечания

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

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

Разрешения

Примеры

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

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

C. Создание руководства по плану типа TEMPLATE для параметризованной формы запроса

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

Е. Создание руководства по плану путем получения XML-шоуплана из кэшированного плана

Обратная связь

Дополнительные ресурсы

Поделиться через

sp_create_plan_guide (Transact-SQL)

Синтаксис

Аргументы

[ @name = ] N'name'

[ @stmt = ] N'stmt'

[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }

[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }

[ @params = ] N'@parameter_name data_type [ ,... n ]'

[ @hints = ] { N'OPTION ( query_hint [ , ... n ] )' | N'XML_showplan' }

Замечания

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

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

Разрешения

Примеры

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

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

C. Создание руководства по плану типа TEMPLATE для параметризованной формы запроса

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

Е. Создание руководства по плану путем получения XML-шоуплана из кэшированного плана

Связанный контент

Обратная связь

Дополнительные ресурсы