sp_create_plan_guide (Transact-SQL)
Применяется к:
SQL ServerAzure SQL DatabaseAzure, управляемому экземпляру SQL Azure
Создает структуру плана для связывания указаний запроса или фактических планов запросов с запросами в базе данных. Дополнительные сведения о структурах планов см. в разделе Руководства планов.
Соглашения о синтаксисе 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, скалярной функции, функции с табличным значением или триггера Transact-SQL DML в текущей базе данных.
SQL
Указывает, что statement_text отображается в контексте автономной инструкции или пакета, который можно отправить в SQL Server с помощью любого механизма. Инструкции Transact-SQL, отправленные объектами среды CLR или расширенными хранимыми процедурами или с помощью EXEC N'sql_string", обрабатываются как пакеты на сервере и, следовательно, должны быть определены как @type = "SQL". Если указан SQL, параметризация указания запроса { ПРИНУДИТЕЛЬНО | Simple } нельзя указать в параметре @hints.
ШАБЛОН
Указывает, что руководство по плану применяется к любому запросу, который параметризует форму, указанную в statement_text. Если задан шаблон, параметризация { ПРИНУДИТЕЛЬНО | Указание запроса SIMPLE } можно указать в параметре @hints. Дополнительные сведения о руководствах по плану TEMPLATE см. в разделе "Указание поведения параметризации запросов" с помощью руководств по плану.
[@module_or_batch =] { N'[ schema_name. ] object_name' | N'batch_text' | NULL }
Указывает имя объекта, в котором отображается statement_text , или пакетный текст, в котором отображается statement_text . Пакетный текст не может содержать инструкцию USEdatabase .
Чтобы руководство по плану соответствовало пакету, отправленному из приложения, 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 применяется только в том случае, если одно из следующих значений имеет значение true:
@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 2016. Структуры планов видны в любом выпуске. Можно также присоединить базу данных, содержащую структуры планов, к любой версии. Руководства по планированию остаются неизменными при восстановлении или присоединении базы данных к обновленной версии SQL Server. Следует тщательно взвешивать необходимость использования структур планов в каждой базе данных после выполнения обновления сервера.
Требования к сопоставлению руководства по плану
Для руководств по плану, указывающих @type = "SQL" или @type = TEMPLATE, чтобы успешно соответствовать запросу, значения для batch_text и @parameter_name data_type [,... n ] должен быть предоставлен точно в том же формате, что и их коллеги, отправленные приложением. Это означает, что необходимо указать пакетный текст точно так же, как компилятор SQL Server получает его. Чтобы записать фактический текст пакета и параметра, можно использовать SQL Server Profiler. Дополнительные сведения см. в руководстве по созданию и тестированию планов с помощью профилировщика SQL Server.
Если @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''))';
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 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 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_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)
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по