• Artigo

sp_create_plan_guide_from_handle (Transact-SQL)

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 Compreendendo os guias de plano.

Ícone de vínculo de tópicoConvenções de sintaxe Transact-SQL

Sintaxe

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

Argumentos

  • [ @name = ] N'plan_guide_name'
    É o nome da guia de plano. Os nomes de guia de plano são colocados no banco de dados atual. plan_guide_name deve estar em conformidade com as regras de identificadores e não pode iniciar com o símbolo de número (#). O comprimento máximo de plan_guide_name é de 124 caracteres.

  • [ @plan_handle = ] plan_handle
    Identifica um lote no cache de planos. plan_handle é varbinary(64). plan_handle pode ser obtido na exibição de gerenciamento dinâmico sys.dm_exec_query_stats.

  • [ @statement_start_offset = ] { statement_start_offset | NULL } ]
    Identifica a posição inicial da instrução no lote do plan_handle especificado. statement_start_offset é int, com um padrão de NULL.

    O deslocamento da instrução corresponde à coluna statement_start_offset 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 do identificador de plano especificado. Os guias de plano resultantes são equivalentes aos que usam a dica de consulta USE PLAN para forçar o uso de um determinado plano.

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 avançará para a próxima instrução do 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 sempre que você obtenha o identificador de plano na exibição de gerenciamento dinâmico sys.dm_exec_query_stats para ajudar a evitar a possibilidade de ocorrência desse erro.

Observação sobre segurançaObservação sobre segurança

sp_create_plan_guide_from_handle cria guias de plano com base em planos à medida que eles vão aparecendo no cache do plano. Isso significa que o texto do lote, as instruções Transact-SQL e o Plano de execução XML são obtidos caractere por caractere (incluindo qualquer valor literal enviado à consulta) do cache do plano no guia de plano resultante. Essas cadeias de caracteres de texto podem conter informações confidenciais 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 sys.plan_guides e a caixa de diálogo Propriedades do Guia de Plano no SQL Server Management Studio. Para garantir que as informações confidenciais não sejam divulgadas em um guia de plano, recomendamos revisar os guias criados no cache do plano.

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

Assim como sp_create_plan_guide, sp_create_plan_guide_from_handle remove o plano de consulta do lote ou módulo de destino do cache do plano. 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, são solicitadas permissões individuais para cada guia de plano criado, usando sp_create_plan_guide_from_handle. A criação de uma guia de plano do tipo OBJECT requer permissão ALTER no objeto mencionado. A criação de um guia de plano do tipo SQL ou TEMPLATE requer a permissão ALTER no banco de dados atual. Para determinar o tipo de guia de plano que será criado, execute a seguinte coluna:

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;

Na linha que contém a instrução para a qual está sendo criado o guia de plano, examine a coluna objtype no conjunto de resultados. Um valor de Proc indica que o guia de plano é do tipo OBJECT. Outros valores, como AdHoc ou Prepared, indicam que o guia de plano é do tipo SQL.

Exemplos

A. Criando um guia de plano de um plano de consulta no cache de plano

O exemplo a seguir cria um guia de plano para uma instrução SELECT única especificando um plano de consulta no cache do plano. 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 AdventureWorks2008R2;
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. Criando vários guias de plano para um lote com 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 de modo que o plano de consulta do lote não seja removido do cache do plano 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. Observe que 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 AdventureWorks2008R2;
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