Guias de Planos

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Os guias de planejamento permitem otimizar o desempenho de consultas quando você não pode ou não deseja alterar diretamente o texto da consulta real no SQL Server. Os guias de plano influenciam a otimização das consultas anexando dicas de consulta ou um plano de consulta fixo a elas. Os guias de planejamento podem ser úteis quando um pequeno subconjunto de consultas em um aplicativo de banco de dados fornecido por um fornecedor terceirizado não está funcionando conforme o esperado. No guia do plano, o/a utilizador/a especifica a instrução Transact-SQL que pretende otimizar, bem como uma cláusula OPTION que contém os parâmetros de consulta que pretende usar, ou um plano de consulta específico que vai utilizar para otimizar a consulta. Quando a consulta é executada, o SQL Server faz a correspondência da instrução Transact-SQL com o 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 Consultas do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, recomendamos usar apenas guias de plano 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 disponíveis do sistema. No entanto, os guias de plano devem ser limitados a consultas de missão crítica direcionadas para melhorar ou estabilizar o desempenho. Os guias de planejamento não devem ser usados para influenciar a maior parte da carga de consulta de um aplicativo implantado.

O plano de execução resultante forçado por este 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 do 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 suportados pelas edições do SQL Server, consulte Recursos com suporte nas edições do SQL Server 2016. Os guias de planos são visíveis em qualquer edição. Você também pode anexar um banco de dados que contenha guias de planos para qualquer edição. Os guias de planejamento permanecem intactos quando você restaura ou anexa um banco de dados 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

Um guia de plano OBJECT corresponde a consultas que são executadas no contexto de procedimentos armazenados Transact-SQL, funções escalares definidas pelo utilizador, funções definidas pelo utilizador com múltiplas instruções de valor de tabela e gatilhos DML.

Suponha que o seguinte procedimento armazenado, 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 este procedimento armazenado foi compilado e otimizado para @Country_region = N'AU' (Austrália). No entanto, como há relativamente poucas ordens de venda originárias da Austrália, o desempenho diminui quando a consulta é executada usando valores de parâmetros de países/regiões com mais ordens de venda. Como a maioria das ordens de venda tem origem nos Estados Unidos, um plano de consulta gerado para @Country_region = N'US' provavelmente teria um desempenho melhor para todos os valores possíveis do parâmetro @Country_region.

Você pode resolver esse problema modificando o procedimento armazenado para adicionar a dica de consulta OPTIMIZE FOR à consulta. No entanto, como o procedimento armazenado está em um aplicativo implantado, você não pode modificar diretamente o código do aplicativo. Em vez disso, você pode 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 autônomas e 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 ajustar consultas que se parametrizam para uma forma especificada. As orientações de plano SQL aplicam-se a declarações Transact-SQL individuais e a conjuntos de instruções. Freqüentemente, essas instruções são enviadas por um aplicativo usando o 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 evitar que um plano de execução paralela seja gerado nessa consulta, crie o seguinte guia de plano 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)';

Os guias de plano SQL também podem ser criados para consultas que se parametrizam na mesma forma quando a opção de base de dados PARAMETERIZATION está definida como FORCED, ou quando um guia de plano TEMPLATE é criado especificando uma classe de consultas parametrizadas.

Guia do plano TEMPLATE

Um guia de plano TEMPLATE corresponde a consultas autônomas que parametrizam para um formulário especificado. Esses guias de plano são usados para substituir a opção SET atual do banco de dados PARAMETERIZATION de um banco de dados para uma classe de consultas.

Você pode criar um guia de plano TEMPLATE em qualquer uma das seguintes situações:

• A opção de base de dados PARAMETERIZATION está definida como FORCED, mas há consultas que tu queres compilar de acordo com as regras de Parâmetrização Simples .

• A opção de base de dados PARAMETERIZATION está definida como SIMPLE (a configuração padrão), mas pretendes que Forçamento de Parametrização seja aplicada a um conjunto de consultas.

Guia de Plano Requisitos de correspondência

Os planos de orientação estão restritos ao âmbito do banco de dados em que são criados. Portanto, somente as guias de planejamento que estão no banco de dados atual quando uma consulta é executada podem ser correspondidas à consulta. Por exemplo, se AdventureWorks2025 for o banco de dados atual e a seguinte consulta for executada:

SELECT FirstName, LastName FROM Person.Person;

Somente guias de plano no banco de dados AdventureWorks2025 são elegíveis para corresponder a esta consulta. No entanto, se AdventureWorks2025 é o banco de dados atual e as seguintes instruções são executadas:

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

Apenas as guias de plano em DB1 são elegíveis para corresponder à consulta, pois a consulta está a ser executada no contexto de DB1.

Para guias de plano baseados em SQL ou TEMPLATE, o SQL Server faz a correspondência entre os valores dos argumentos @module_or_batch e @params a uma consulta comparando os dois valores caractere por caractere. Isso significa que você deve fornecer o texto 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 mesmo formato, caractere por caractere, conforme é enviado ao SQL Server. Nenhuma conversão interna é realizada para facilitar esta correspondência.

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

Efeito do Guia de Planejamento no Cache de Plano

A criação de um guia de plano em um módulo remove o plano de consulta para esse módulo do cache de planos. Criar um guia de plano do tipo OBJECT ou SQL em um lote remove o plano de consulta para um lote que tem o mesmo valor de hash. A criação de um guia de plano do tipo TEMPLATE remove todos os lotes de instruções simples do cache de plano dentro do banco de dados.

Tarefas relacionadas

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