sys.sp_create_plan_guide_from_handle (Transact-SQL)

Aplica-se a: SQL Server

Cria um ou mais guias de planos a partir de um plano de consulta na cache do plano. Pode usar este procedimento armazenado para garantir que o otimizador de consultas utiliza sempre um plano de consulta específico para uma consulta específica. Para obter mais informações sobre guias de plano, consulte Guias de plano.

Transact-SQL convenções de sintaxe

Sintaxe

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

Arguments

[ @name = ] N'nome'

O nome do guia do plano. @name é sysname, sem padrão. Os nomes dos guias de planejamento têm como escopo o banco de dados atual. @name deve estar em conformidade com as regras para identificadores e não pode começar com o sinal numérico (#). O comprimento máximo de @name é de 124 caracteres.

[ @plan_handle = ] plan_handle

Identifica um lote no cache do plano. @plan_handle é varbinary(64), sem padrão. @plan_handle podem ser obtidos a partir da visão sys.dm_exec_query_stats gestão dinâmica.

[ @statement_start_offset = ] statement_start_offset

Identifica a posição inicial da instrução dentro do lote do @plan_handle especificado. @statement_start_offset é int, com um padrão de NULL.

O deslocamento da instrução corresponde à statement_start_offset coluna na vista sys.dm_exec_query_stats gestão dinâmica.

Quando NULL é especificado ou não é especificado um deslocamento da instrução, é criado um guia de plano para cada instrução do lote usando o plano de consulta para o handle do plano especificado. Os guias de planos resultantes são equivalentes a guias de plano que usam a USE PLAN dica de consulta para forçar a utilização de um plano específico.

Remarks

Um guia de planos não pode ser criado para todos os tipos de declarações. Se não for possível criar um guia de plano para uma instrução no lote, o procedimento armazenado ignora a instrução e continua para a próxima instrução do lote. Se uma instrução ocorrer várias vezes no mesmo lote, o plano para a última ocorrência é ativado e os planos anteriores para a instrução são desativados. Se nenhuma instrução do lote puder ser usada num guia de plano, o erro 10532 é apresentado e a instrução falha. Recomendamos que obtenha sempre a gestão do plano a partir da sys.dm_exec_query_stats perspetiva de gestão dinâmica para ajudar a evitar a possibilidade deste erro.

Crie guias de plano para múltiplas instruções dentro de um plano de consulta

Por exemplo sp_create_plan_guide, sp_create_plan_guide_from_handle remove o plano de consulta para o lote ou módulo alvo da cache do plano. Isto é feito para garantir que todos os utilizadores começam a usar o novo guia de planos. Ao criar um guia de plano para múltiplas instruções dentro de um único plano de consulta, pode adiar a remoção do plano da cache criando todos os guias de plano numa transação explícita. Este método permite que o plano permaneça na cache até que a transação esteja concluída e seja criado um guia de plano para cada extrato especificado. Ver Exemplo B.

Permissions

Requer permissão VIEW SERVER STATE. Além disso, são necessárias permissões individuais para cada guia de planos criado usando sp_create_plan_guide_from_handle. Criar um guia de planos do tipo OBJECT requer ALTER permissão sobre o objeto referenciado. Criar um guia de plano do tipo SQL ou TEMPLATE requer ALTER permissão na base de dados atual. Para determinar o tipo de guia de planos que será criado, execute a seguinte consulta:

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;

Na linha que contém a instrução para a qual cria o guia de planos, examine a objtype coluna no conjunto de resultados. Um valor de Proc indica que o guia de planta é do tipo OBJECTO. Outros valores, como Ad hoc ou Prepared indicam o guia do plano, são do tipo SQL.

Exemplos

A. Crie um guia de planos a partir de um plano de consulta na cache do plano

O exemplo seguinte cria um guia de plano para uma única SELECT instrução ao especificar um plano de consulta a partir da cache do plano. O exemplo começa por executar uma declaração simples SELECT para a qual o guia de planos será criado. O plano para esta consulta é analisado utilizando as sys.dm_exec_sql_text vistas de gestão dinâmica.sys.dm_exec_text_query_plan O guia de planos é então criado para a consulta, especificando o plano de consulta na cache de planos associada à consulta. A última afirmação no exemplo verifica que o guia do plano existe.

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. Crie múltiplos guias de planos para um lote multistatement

O exemplo seguinte cria um guia de plano para duas instruções dentro de um lote de múltiplas instruções. Os guias de planos são criados dentro de uma transação explícita para que o plano de consulta para o lote não seja removido da cache do plano após a criação do primeiro guia de plano. O exemplo começa por executar um lote de múltiplas instruções. O plano do lote é analisado utilizando vistas de gestão dinâmica. Uma linha para cada instrução no lote é devolvida. É então criado um guia de planos para a primeira e terceira instruções do lote, especificando o @statement_start_offset parâmetro. A última afirmação no exemplo verifica se os guias do plano existem.

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

Conteúdo relacionado

• Procedimentos armazenados do Mecanismo de Banco de Dados (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• Guias do Plano
• 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)