sp_create_plan_guide_from_handle (Transact-SQL)

Область применения: SQL Server

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

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

Синтаксис

sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

Аргументы

[ @name = ] N'name'

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

[ @plan_handle = ] plan_handle

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

[ @statement_start_offset = ] statement_start_offset

Определяет начальную позицию инструкции в пакете указанного @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
INNER 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. Другие значения, например Ad hoc или 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
INNER 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)