sp_create_plan_guide_from_handle (Transact-SQL)

  • Статья

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

Значок ссылки на раздел Синтаксические обозначения 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_cached_plans.

  • [ @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 AdventureWorks2012;
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

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

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

USE AdventureWorks2012;
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)

Основные понятия

Руководства планов