Guias de Planejamento

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

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. Os guias de plano influenciam a otimização de consultas ao associar dicas de consulta ou um plano de consulta fixo a elas. 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 é executada, o SQL Server associa a instrução Transact-SQL ao guia de plano e anexa a cláusula OPTION à consulta em 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.

O número total de guias de plano que você pode criar é limitado apenas pelos recursos de sistema disponíveis. No entanto, os guias de plano devem se limitar a consultas essenciais à missão, destinadas a melhorar 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

Os seguintes tipos de guias de plano podem ser criados.

Guia do 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 AdventureWorks2025:

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. Em vez disso, é possível criar o seguinte guia de plano no banco de dados AdventureWorks2025.

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 que são executadas no contexto de instruções Transact-SQL independentes e de lotes que não fazem parte de um objeto de banco de dados. Os guias de plano baseados em SQL também podem ser usados para fazer corresponder consultas que são parametrizadas em 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 independente:

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)';

Guias de plano SQL também podem ser criados em consultas que são parametrizadas na mesma forma quando a opção PARAMETERIZATION do banco de dados está SET definida como FORCED, ou quando um guia de plano TEMPLATE é criado especificando uma classe parametrizada de consultas.

guia do plano TEMPLATE

Um guia de plano TEMPLATE corresponde a consultas independentes que são parametrizadas em uma forma especificada. Esses guias de plano são usados para substituir a opção de banco de dados PARAMETERIZATION SET atual de um banco de dados para uma 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 FORCED, mas há consultas que você deseja compilar de acordo com as regras de Parametrização Simples.

• A opção de banco de dados PARAMETERIZATION está SET definida como SIMPLE (a configuração padrão), mas você deseja que a Parametrização Forçada seja testada em uma classe de consultas.

Guia do plano correspondente aos requisitos

Guias de plano têm escopo restrito ao banco de dados no qual são criados. Portanto, somente os guias de plano que estão no banco de dados atual quando uma consulta é executada podem ser associados à consulta. Por exemplo, se AdventureWorks2025 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 AdventureWorks2025 são elegíveis para corresponderem a essa consulta. Porém, se AdventureWorks2025 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 em DB1 são elegíveis para corresponder à consulta, porque a consulta está sendo 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 tanto um guia de plano regular (SQL ou OBJECT) quanto um guia de plano TEMPLATE puderem se aplicar a uma instrução SQL, somente o guia de plano regular será usado.

Efeito do guia de plano sobre o cache de planos

A criação de um guia de plano em um módulo remove do cache de planos o plano de execução de consulta desse módulo. A criação de um guia de plano do tipo OBJECT ou SQL para um lote remove o plano de consulta de um lote com o mesmo valor de hash. Criar um guia de plano do tipo TEMPLATE remove todos os lotes de instrução única do cache de planos nesse banco de dados.

Tarefas Relacionadas

Consulte 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)