Compartilhar via

Guias de plano

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Guias de plano permitem otimizar o desempenho das consultas quando você não pode ou não quer alterar diretamente o texto da consulta real no SQL Server. As guias de plano influenciam a otimização das consultas, anexando dicas de consulta ou um plano de consulta fixo. Guias de plano podem ser úteis quando um subconjunto pequeno de consultas em um aplicativo de banco de dados fornecido por um terceiro não estiver executando como esperado. No guia de plano, especifique a instrução Transact-SQL que deve ser otimizada, e uma cláusula OPTION que contenha as dicas de consulta a serem usadas ou um plano de consulta específico a ser usado para otimizar a consulta. Quando a consulta é feita, o SQL Server corresponde a instrução Transact-SQL com o guia de plano e anexa a cláusula OPTION à consulta no tempo de execução ou usa o plano de consulta especificado. Como o Otimizador de Consulta do SQL Server seleciona, normalmente, o melhor plano de execução para uma consulta, recomendamos usar guias de plano apenas como último recurso para desenvolvedores e administradores de banco de dados experientes.

Observação

As dicas do Repositório de Consultas fornecem um método fácil de usar para formatar planos de consulta sem alterar o código do aplicativo. As dicas do Repositório de Consultas são mais simples do que os guias de plano. As dicas do Repositório de Consultas estão disponíveis no Banco de Dados SQL do Azure, na Instância Gerenciada de SQL do Azure e no SQL Server 2022 (16.x) e versões posteriores.

O número total de guias de plano que é possível criar só está limitado através de recursos do sistema disponíveis. De outro modo, guias de plano devem ser limitados para consultas de missão-crítica que são direcionados para aprimorar ou estabilizar o desempenho. Guias de plano não podem ser usados para influenciar a maioria da carga de consulta de um aplicativo implantado.

O plano de execução resultante forçado por esse recurso será o mesmo ou semelhante ao plano que está sendo forçado. Como o plano resultante pode não ser idêntico ao plano especificado pelo guia de plano, o desempenho dos planos pode variar. Em casos raros, a diferença de desempenho pode ser significativa e negativa. Nesse caso, o administrador deve remover o plano forçado.

Os guias de plano não podem ser usados em todas as edições do Microsoft SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, consulte Recursos com suporte nas edições do SQL Server 2016. As guias de plano são visíveis em qualquer edição. Também é possível anexar um banco de dados contendo guias de plano a qualquer edição. Os guias de plano permanecem intactos quando o banco de dados é restaurado ou anexado a uma versão atualizada do SQL Server.

Tipos de guias de plano

Podem ser criados os tipos de guias de plano a seguir.

Guia de plano OBJECT

O guia de plano OBJECT corresponde às consultas executadas no contexto dos procedimentos armazenados Transact-SQL, das funções escalares definidas pelo usuário, das funções com valor de tabela de várias instruções definidas pelo usuário e dos gatilhos DML.

Suponha que o procedimento armazenado a seguir, que usa o parâmetro @Country_region, esteja em um aplicativo de banco de dados implantado no banco de dados AdventureWorks2022:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;

Suponha que esse procedimento armazenado foi compilado e otimizado para @Country_region = N'AU' (Austrália). Entretanto, já que há relativamente poucas ordens de venda oriundas da Austrália, o desempenho cai quando a consulta é executada usando os valores de parâmetro de países/regiões com mais ordens de venda. Como a maioria dos pedidos de vendas tem origem nos Estados Unidos, um plano de consulta gerado para @Country_region = N'US' provavelmente teria execução melhor para todos os valores possíveis do parâmetro @Country_region .

É possível corrigir esse problema ao modificar o procedimento armazenado para adicionar a dica de consulta OPTIMIZE FOR à consulta. Porém, já que o procedimento armazenado está em um aplicativo implantado, não é possível modificar diretamente o código do aplicativo. Ao contrário, é possível criar o guia de plano a seguir no banco de dados AdventureWorks2022 .

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

Quando a consulta especificada na instrução sp_create_plan_guide é executada, a consulta é modificada antes da otimização para incluir a cláusula OPTIMIZE FOR (@Country = N''US'') .

Guia de plano SQL

Um guia de plano SQL corresponde a consultas executadas no contexto de instruções Transact-SQL autônomas e lotes que não fazem parte de um objeto de banco de dados. Os guias de plano com base em SQL também podem ser usados para corresponder consultas com parâmetros uma forma especificada. Os guias de plano SQL se aplicam a instruções e lotes Transact-SQL autônomos. Frequentemente, essas instruções são submetidas por um aplicativo por meio do procedimento armazenado do sistema sp_executesql . Por exemplo, considere o seguinte lote autônomo:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Para impedir que um plano de execução paralelo seja gerado nessa consulta, crie o guia de plano a seguir e defina a dica de consulta MAXDOP como 1 no parâmetro @hints .

sp_create_plan_guide   
@name = N'Guide2',   
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',  
@type = N'SQL',  
@module_or_batch = NULL,   
@params = NULL,   
@hints = N'OPTION (MAXDOP 1)';

Como outro exemplo, considere a seguinte instrução SQL enviada usando sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Para criar um plano exclusivo para cada execução dessa consulta, crie o seguinte guia de plano e use a dica de consulta OPTION (RECOMPILE) no parâmetro @hints.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Importante

Os valores fornecidos para os argumentos @module_or_batch e @params da instrução sp_create_plan guide devem coincidir com o texto correspondente submetido na consulta real. Para obter mais informações, veja sp_create_plan_guide (Transact-SQL) e Usar o SQL Server Profiler para criar e testar guias de plano.

Os guias de plano SQL também podem ser criados em consultas com parâmetros da mesma forma quando a opção de banco de dados PARAMETERIZATION for SET to FORCED ou quando um guia de plano TEMPLATE for criado especificando uma classe de consulta com parâmetros.

guia de plano TEMPLATE

O guia de plano TEMPLATE corresponde consultas autônomas com parâmetros com uma forma especificada. Esses guias de plano são usados para substituir a opção SET do banco de dados PARAMETERIZATION atual de um banco de dados para a classe de consultas.

É possível criar um guia de plano TEMPLATE em qualquer uma das seguintes situações:

  • A opção de banco de dados PARAMETERIZATION é SET to FORCED, mas há consultas que devem ser compiladas de acordo com as regras de Parametrização Simples.

  • A opção de banco de dados PARAMETERIZATION é SET to SIMPLE (configuração padrão), mas é preciso que a Parametrização Forçada seja testada em uma classe de consultas.

Guia de plano correspondente a requisitos

Guias de plano são aplicados ao banco de dados no qual eles são criados. Portanto, somente os guias de plano presentes no banco de dados se tornam atuais quando uma consulta pode ser combinada com outra. Por exemplo, se AdventureWorks2022 for o banco de dados atual e a consulta seguinte executa:

SELECT FirstName, LastName FROM Person.Person;

Somente guias de plano do banco de dados AdventureWorks2022 são elegíveis para corresponderem a essa consulta. Porém, se AdventureWorks2022 for o banco de dados atual e as instruções seguintes são executadas:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

Somente os guias de plano no DB1 são elegíveis para combinar com a consulta porque ela é executada no contexto de DB1.

Para guias de plano baseados em SQL ou em TEMPLATE, o SQL Server faz a correspondência dos valores dos argumentos @module_or_batch e @params com uma consulta, comparando os dois valores caractere por caractere. Isso significa o texto deve ser fornecido exatamente como o SQL Server o recebe no lote real.

Quando @type = 'SQL' e @module_or_batch é definido como NULL, o valor de @module_or_batch é definido como o valor de @stmt. Isso significa que o valor para statement_text deve ser fornecido no formato idêntico, caractere por caractere, como ele é enviado para o SQL Server. Nenhuma conversão interna é executada para facilitar essa correspondência.

Quando um guia de plano normal (SQL ou OBJECT) e um guia de plano de MODELO puderem ser aplicados a uma instrução, somente o guia de plano normal será usado.

Observação

O lote que contém a instrução na qual se quer criar o guia de plano não pode conter uma instrução de USE database .

Efeito do guia de plano no cache do esquema

Criar um guia de plano em um módulo remove o plano de consulta desse módulo do cache do esquema. Criar um guia de plano do tipo OBJECT ou SQL em um lote remove o plano de consulta de um lote que tem o mesmo valor de hash. Criar um guia de plano do tipo TEMPLATE remove todos os lotes da instrução única do cache do esquema dentro desse banco de dados.

Tarefa Tópico
Descreve como criar um guia de plano. Criar um novo guia de plano
Descreve como criar um guia de plano para consultas parametrizadas. Criar um guia de plano para consultas parametrizadas
Descreve como controlar o comportamento de parametrização da consulta usando guias de plano. Especificar comportamento de parametrização de consulta usando guias de plano
Descreve como incluir um plano de consulta fixo em um guia de plano. Aplicar um plano de consulta fixo a um guia de plano
Descreve como especificar dicas de consulta em um guia de plano. Anexar dicas de consulta a um guia de plano
Descreve como exibir propriedades do guia de plano. Exibir propriedades do guia de plano
Descreve como usar o SQL Server Profiler para criar e testar guias de plano. Usar o SQL Server Profiler para criar e testar guias de plano
Descreve como validar guias de plano. Validar guias de plano depois da atualização

Confira também

sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)