sp_create_plan_guide_from_handle (Transact-SQL)

Aplica-se: SQL Server

Cria uma ou mais guias de plano de um plano de consulta no cache de plano. É possível usar esse procedimento armazenado para garantir que o otimizador de consulta use sempre um plano de consulta específico para uma consulta específica. Para obter mais informações sobre guias de plano, consulte Plan Guides.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

@name [ = ] N'nome'

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

@plan_handle [ = ] plan_handle

Identifica um lote no cache de plano. @plan_handle é varbinary(64), sem padrão. @plan_handle podem ser obtidos na exibição de gerenciamento dinâmico sys.dm_exec_query_stats.

@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 statement_start_offset instrução corresponde à coluna na exibição de gerenciamento dinâmico sys.dm_exec_query_stats .

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

Comentários

Um guia de plano não pode ser criado para todos os tipos de instrução. Se um guia de plano não puder ser criado para uma instrução no lote, o procedimento armazenado ignorará a instrução e continuará para a próxima instrução no lote. Caso uma instrução ocorra várias vezes no mesmo lote, o plano da última ocorrência será habilitado, e os planos anteriores da instrução serão desabilitados. Se nenhuma instrução do lote puder ser usada em um guia de plano, o erro 10532 será gerado e a instrução falhará. Recomendamos que você sempre obtenha o identificador de plano da sys.dm_exec_query_stats exibição de gerenciamento dinâmico para ajudar a evitar a possibilidade desse erro.

Importante

sp_create_plan_guide_from_handle Cria guias de plano com base nos planos conforme eles aparecem no cache de planos. Isso significa que o texto em lote, as instruções Transact-SQL e o Plano de Execução XML são obtidos caractere por caractere (incluindo quaisquer valores literais passados para a consulta) do cache de planos para o guia de plano resultante. Essas cadeias de caracteres de texto podem conter informações confidenciais que são armazenadas nos metadados do banco de dados. Os usuários com permissões apropriadas podem exibir essas informações usando a exibição do catálogo e a sys.plan_guides caixa de diálogo Propriedades do Guia de Planos no SQL Server Management Studio. Para garantir que informações confidenciais não sejam divulgadas por meio de um guia de plano, recomendamos examinar os guias de plano criados a partir do cache de planos.

Criar guias de plano para várias instruções em um plano de consulta

Como sp_create_plan_guide, sp_create_plan_guide_from_handle remove o plano de consulta para o lote ou módulo de destino do cache de planos. Isso é feito para assegurar que todos os usuários comecem a usar o novo guia de plano. Ao criar um guia de plano para várias instruções em um único plano de consulta, você pode adiar a remoção do plano do cache criando todos os guias de plano em uma transação explícita. Esse método permite que o plano permaneça no cache até que a transação seja concluída e um guia de plano para cada instrução especificada seja criado. Consulte o Exemplo B.

Permissões

Requer a permissão VIEW SERVER STATE. Além disso, permissões individuais são necessárias para cada guia de plano criado usando sp_create_plan_guide_from_handleo . A criação de um guia de plano do tipo OBJECT requer ALTER permissão no objeto referenciado. Criar um guia de plano do tipo SQL ou TEMPLATE requer ALTER permissão no banco de dados atual. Para determinar o tipo de guia de plano 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 você cria o guia de plano, examine a objtype coluna no conjunto de resultados. Um valor de Proc indica que o guia de plano é do tipo OBJECT. Outros valores, como Ad hoc ou Prepared, indicam que o guia de plano é do tipo SQL.

Exemplos

R. Criar um guia de plano a partir de um plano de consulta no cache de planos

O exemplo a seguir cria um guia de plano para uma única SELECT instrução especificando um plano de consulta do cache de planos. O exemplo começa pela execução de uma instrução SELECT única para a qual o guia de plano será criado. O plano para esta consulta é examinado usando as exibições de gerenciamento dinâmico sys.dm_exec_sql_text e sys.dm_exec_text_query_plan. Em seguida, o guia de plano é criado para a consulta por meio da especificação do plano de consulta no cache do plano associado à consulta. A instrução final no exemplo verifica se o guia de 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. Criar vários guias de plano para um lote de várias instruções

O exemplo a seguir cria um guia de plano para duas instruções de um lote com várias instruções. Os guias de plano são criados em uma transação explícita para que o plano de consulta do lote não seja removido do cache de planos após a criação do primeiro guia de plano. O exemplo começa pela execução de um lote com várias instruções. O plano do lote é examinado com o uso de exibições de gerenciamento dinâmico. Uma linha para cada instrução no lote é retornada. Um guia de plano é criado para a primeira e a terceira instruções no lote por meio da especificação do parâmetro @statement_start_offset. A instrução final no exemplo verifica se os guias de 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 de 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)