sp_create_plan_guide_from_handle (Transact-SQL)

Статья
05/23/2023

Применимо к:SQL Server

Создает одну или несколько структур плана из плана запроса в кэше планов. Эту хранимую процедуру можно использовать для обеспечения использования оптимизатором запросов для конкретного запроса конкретного плана запроса. Дополнительные сведения о структурах планов см. в разделе Руководства планов.

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

Синтаксис

sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'  
    , [ @plan_handle = ] plan_handle  
    , [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]

Аргументы

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

[ @plan_handle = ] plan_handle
Определяет пакет в кэше планов. plan_handle — varbinary(64). plan_handle можно получить из динамического представления управления sys.dm_exec_query_stats.

[ @statement_start_offset = ] { statement_start_offset | NULL } ]
Определяет начальную позицию инструкции в пакете указанного plan_handle. statement_start_offset имеет значение int с значением NULL по умолчанию.

Смещение инструкции соответствует столбцу statement_start_offset в динамическом представлении управления sys.dm_exec_query_stats .

Если указано значение NULL или смещение инструкции не задано, структура плана создается для каждой инструкции в пакете с помощью плана запроса для указанного дескриптора плана. Полученные в результате структуры планов эквивалентны структурам планов, применяющим указание запроса USE PLAN для принудительного использования определенного плана.

Замечания

Структура плана не может быть создана для всех типов инструкций. Если структура плана не может быть создана для какой-либо инструкции в пакете, хранимая процедура пропускает эту инструкцию и переходит к следующей инструкции в пакете. Если инструкция повторяется в одном и том же пакете несколько раз, активируется план для последнего вхождения, а предыдущие планы для инструкции отключаются. Если ни одна из инструкций в пакете не может быть использована в структуре плана, выдается сообщение об ошибке 1053, а данная инструкция завершается неудачно. Рекомендуется всегда получать дескриптор плана из динамического административного представления sys.dm_exec_query_stats для снижения вероятности возникновения этой ошибки.

Внимание

Процедура sp_create_plan_guide_from_handle создает структуры планов на основе планов в том виде, в каком они находятся в кэше планов. Это означает, что пакетный текст, инструкции Transact-SQL и XML Showplan принимают символьные по символам (включая любые литеральные значения, передаваемые в запрос) из кэша плана в итоговом руководстве по плану. Эти текстовые строки могут содержать конфиденциальные сведения, которые затем сохраняются в метаданных базы данных. Пользователи с соответствующими разрешениями могут просматривать эти сведения с помощью представления каталога sys.plan_guides и диалогового окна "Свойства руководства по плану" в SQL Server Management Studio. Чтобы не допустить раскрытия конфиденциальных сведений через руководство плана, рекомендуется проверять руководства плана, созданные из кэша планов, на их наличие.

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

Как и sp_create_plan_guide, процедура sp_create_plan_guide_from_handle удаляет план запроса для целевого пакета или модуля из кэша планов. Таким образом обеспечивается использование всеми пользователями новой структуры плана. При создании структуры плана для нескольких инструкций в рамках одного плана запроса можно отложить удаление плана из кэша с помощью создания всех структур плана в явной транзакции. Этот метод позволяет сохранить план в кэше до завершения транзакции и создания структуры плана для каждой указанной инструкции. См. пример Б.

Разрешения

Требуется разрешение VIEW SERVER STATE. Кроме того, требуются отдельные разрешения на каждую структуру плана, создаваемую с помощью хранимой процедуры sp_create_plan_guide_from_handle. Для создания руководства по плану типа OBJECT требуется ALTER разрешение на объект, на который ссылается ссылка. Чтобы создать руководство по плану типа SQL или TEMPLATE, требуется ALTER разрешение на текущую базу данных. Чтобы определить тип создаваемой структуры плана, выполните следующий запрос.

SELECT cp.plan_handle, sql_handle, st.text, objtype   
FROM sys.dm_exec_cached_plans AS cp  
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;

В строке, содержащей инструкцию, для которой создается структура плана, исследуйте столбец objtype в результирующем наборе. Значение Proc указывает на то, что структура плана имеет тип OBJECT. Другие значения, например AdHoc или Prepared, указывают на принадлежность структуры плана к типу SQL.

Примеры

О. Создание руководства плана из плана запроса в кэше планов

В следующем примере создается структура плана для отдельной инструкции SELECT путем указания плана запроса из кэша планов. Пример начинается с выполнения простой инструкции SELECT, для которой была создана структура плана. План для этого запроса исследуется с помощью динамических административных представлений sys.dm_exec_sql_text и sys.dm_exec_text_query_plan. Затем структура плана создается для запроса с указанием плана запроса в кэше планов, связанном с данным запросом. Последняя инструкция в примере осуществляет проверку существования структуры плана.

USE AdventureWorks2022;  
GO  
SELECT WorkOrderID, p.Name, OrderQty, DueDate  
FROM Production.WorkOrder AS w   
JOIN Production.Product AS p ON w.ProductID = p.ProductID  
WHERE p.ProductSubcategoryID > 4  
ORDER BY p.Name, DueDate;  
GO  
-- Inspect the query plan by using dynamic management views.  
SELECT * FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO  
-- Create a plan guide for the query by specifying the query plan in the plan cache.  
DECLARE @plan_handle varbinary(64);  
DECLARE @offset int;  
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
  
EXECUTE sp_create_plan_guide_from_handle   
    @name =  N'Guide1',  
    @plan_handle = @plan_handle,  
    @statement_start_offset = @offset;  
GO  
-- Verify that the plan guide is created.  
SELECT * FROM sys.plan_guides  
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO

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

В следующем примере создается структура плана для двух инструкций, входящих в пакет из нескольких инструкций. Структуры планов создаются в явной транзакции, поэтому план запроса для пакета не удаляется из кэша планов после создания первой структуры плана. Пример начинается с выполнения пакета из нескольких инструкций. План для пакета исследуется с помощью динамических административных представлений. Обратите внимание, что возвращается строка для каждой инструкции в пакете. Затем создается структура плана для первой и третьей инструкций в пакете путем указания параметра @statement_start_offset. Последняя инструкция в примере осуществляет проверку существования структур плана.

USE AdventureWorks2022;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO

-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO

-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement1_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement3_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

COMMIT TRANSACTION
GO

-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO

См. также

Хранимые процедуры ядра СУБД (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
Руководства планов
sp_create_plan_guide (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_text_query_plan (Transact-SQL)
sp_control_plan_guide (Transact-SQL)