sp_create_plan_guide_from_handle (Transact-SQL)sp_create_plan_guide_from_handle (Transact-SQL)

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Cria uma ou mais guias de plano de um plano de consulta no cache de plano.Creates one or more plan guides from a query plan in the plan cache. É 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.You can use this stored procedure to ensure the query optimizer always uses a specific query plan for a specified query. Para obter mais informações sobre guias de plano, consulte Plan Guides.For more information about plan guides, see Plan Guides.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

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

ArgumentosArguments

[ @name = ] N'plan_guide_name'[ @name = ] N'plan_guide_name'
É o nome da guia de plano.Is the name of the plan guide. Os nomes de guia de plano têm escopo no banco de dados atual.Plan guide names are scoped to the current database. plan_guide_name deve estar em conformidade com as regras para identificadores e não pode começar com o sinal de número (#).plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#). O comprimento máximo de plan_guide_name é de 124 caracteres.The maximum length of plan_guide_name is 124 characters.

[ @plan_handle = ] plan_handle[ @plan_handle = ] plan_handle
Identifica um lote no cache de plano.Identifies a batch in the plan cache. plan_handle está varbinary(64) .plan_handle is varbinary(64). plan_handle pode ser obtido de DM exec_query_stats exibição de gerenciamento dinâmico.plan_handle can be obtained from the sys.dm_exec_query_stats dynamic management view.

[ @statement_start_offset = ] { statement_start_offset | NULL } ][ @statement_start_offset = ] { statement_start_offset | NULL } ]
Identifica a posição inicial da instrução no lote do especificado plan_handle.Identifies the starting position of the statement within the batch of the specified plan_handle. statement_start_offset está int, com um padrão NULL.statement_start_offset is int, with a default of NULL.

O deslocamento da instrução corresponde à coluna statement_start_offset na DM exec_query_stats exibição de gerenciamento dinâmico.The statement offset corresponds to the statement_start_offset column in the sys.dm_exec_query_stats dynamic management view.

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.When NULL is specified or a statement offset is not specified, a plan guide is created for each statement in the batch using the query plan for the specified plan handle. 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.The resulting plan guides are equivalent to plan guides that use the USE PLAN query hint to force the use of a specific plan.

ComentáriosRemarks

Um guia de plano não pode ser criado para todos os tipos de instrução.A plan guide cannot be created for all statement types. 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.If a plan guide cannot be created for a statement in the batch, the stored procedure ignores the statement and continues to the next statement in the batch. 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.If a statement occurs multiple times in the same batch, the plan for the last occurrence is enabled and previous plans for the statement are disabled. Se nenhuma instrução do lote puder ser usada em um guia de plano, o erro 10532 será gerado e a instrução falhará.If no statements in the batch can be used in a plan guide, error 10532 is raised and the statement fails. 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.We recommend that you always obtain the plan handle from the sys.dm_exec_query_stats dynamic management view to help avoid the possibility of this error.

Importante

sp_create_plan_guide_from_handle cria guias de plano com base em planos à medida que eles vão aparecendo no cache do plano.sp_create_plan_guide_from_handle creates plan guides based on plans as they appear in the plan cache. Isso significa que o texto do lote, as instruções Transact-SQLTransact-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.This means that the batch text, Transact-SQLTransact-SQL statements, and XML Showplan are taken character-by-character (including any literal values passed to the query) from the plan cache into the resulting plan guide. Essas cadeias de caracteres de texto podem conter informações confidenciais armazenadas nos metadados do banco de dados.These text strings may contain sensitive information that is then stored in the metadata of the database. Os usuários com permissões apropriadas podem exibir essas informações por meio da exibição de catálogo plan_guides e a propriedades do guia de plano da caixa de diálogo SQL Server Management StudioSQL Server Management Studio.Users with appropriate permissions can view this information by using the sys.plan_guides catalog view and the Plan Guide Properties dialog box in SQL Server Management StudioSQL 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.To ensure that sensitive information is not disclosed through a plan guide, we recommend reviewing the plan guides created from the plan cache.

Criando guias de plano para várias instruções em um plano de consultaCreating Plan Guides for Multiple Statements Within a Query Plan

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.Like sp_create_plan_guide, sp_create_plan_guide_from_handle removes the query plan for the targeted batch or module from the plan cache. Isso é feito para assegurar que todos os usuários comecem a usar o novo guia de plano.This is done to ensure that all users begin using the new plan guide. 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.When creating a plan guide for multiple statements within a single query plan, you can postpone the removal of the plan from the cache by creating all the plan guides in an explicit transaction. 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.This method allows the plan to remain in the cache until the transaction is complete and a plan guide for each specified statement is created. Consulte o Exemplo B.See Example B.

PermissõesPermissions

Requer a permissão VIEW SERVER STAT.Requires VIEW SERVER STATE permission. Além disso, são solicitadas permissões individuais para cada guia de plano criado, usando sp_create_plan_guide_from_handle.In addition, individual permissions are required for each plan guide that is created by using sp_create_plan_guide_from_handle. A criação de uma guia de plano do tipo OBJECT requer permissão ALTER no objeto mencionado.To create a plan guide of type OBJECT requires ALTER permission on the referenced object. A criação de um guia de plano do tipo SQL ou TEMPLATE requer a permissão ALTER no banco de dados atual.To create a plan guide of type SQL or TEMPLATE requires ALTER permission on the current database. Para determinar o tipo de guia de plano que será criado, execute a seguinte consulta:To determine the plan guide type that will be created, run the following query:

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.In the row that contains the statement for which you are creating the plan guide, examine the objtype column in the result set. Um valor de Proc indica que o guia de plano é do tipo OBJECT.A value of Proc indicates the plan guide is of type OBJECT. Outros valores, como AdHoc ou Prepared, indicam que o guia de plano é do tipo SQL.Other values such as AdHoc or Prepared indicate the plan guide is of type SQL.

ExemplosExamples

A.A. Criando um guia de plano de um plano de consulta no cache de planoCreating a plan guide from a query plan in the plan cache

O exemplo a seguir cria um guia de plano para uma instrução SELECT única especificando um plano de consulta no cache do plano.The following example creates a plan guide for a single SELECT statement by specifying a query plan from the plan cache. O exemplo começa pela execução de uma instrução SELECT única para a qual o guia de plano será criado.The example begins by executing a simple SELECT statement for which the plan guide will be created. 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.The plan for this query is examined by using the sys.dm_exec_sql_text and sys.dm_exec_text_query_plan dynamic management views. 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.The plan guide is then created for the query by specifying the query plan in the plan cache that is associated with the query. A instrução final no exemplo verifica se o guia de plano existe.The final statement in the example verifies that the plan guide exists.

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  

B.B. Criando vários guias de plano para um lote com várias instruçõesCreating multiple plan guides for a multistatement batch

O exemplo a seguir cria um guia de plano para duas instruções de um lote com várias instruções.The following example creates a plan guide for two statements within a multistatement batch. 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.The plan guides are created within an explicit transaction so that the query plan for the batch is not removed from the plan cache after the first plan guide is created. O exemplo começa pela execução de um lote com várias instruções.The example begins by executing a multistatement batch. O plano do lote é examinado com o uso de exibições de gerenciamento dinâmico.The plan for the batch is examined by using dynamic management views. Observe que uma linha para cada instrução no lote é retornada.Notice that a row for each statement in the batch is returned. 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 plan guide is then created for the first and third statements in the batch by specifying the @statement_start_offset parameter. A instrução final no exemplo verifica se os guias de plano existem.The final statement in the example verifies that the plan guides exist.

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

Consulte tambémSee Also

Procedimentos armazenados do mecanismo de banco de dados (Transact-SQL) Database Engine Stored Procedures (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL) sys.dm_exec_query_stats (Transact-SQL)
Guias de plano Plan Guides
sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL) sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_text_query_plan (Transact-SQL) sys.dm_exec_text_query_plan (Transact-SQL)
sp_control_plan_guide (Transact-SQL)sp_control_plan_guide (Transact-SQL)