Guia de Planos

Os guias de plano permitem otimizar o desempenho das consultas quando você não puder ou não quiser alterar diretamente o texto da consulta real no SQL Server 2014. Diretrizes de plano influenciam a otimização de consultas adicionando dicas de consulta ou um plano de execução fixo a elas. Guias de plano podem ser úteis quando um pequeno subconjunto de consultas em um aplicativo de banco de dados fornecido por um fornecedor de terceiros não está sendo executado conforme o esperado. No guia de plano, você especifica a instrução Transact-SQL que você deseja otimizar e uma cláusula OPTION contendo as dicas de consulta que você quer usar ou um plano de consulta específico que você deseja usar para otimizar a consulta. Quando a consulta é executada, o SQL Server associa a instrução Transact-SQL ao guia de plano e adiciona a cláusula OPTION à consulta no momento da execução ou utiliza o plano de consulta especificado.

O número total de guias de plano que você pode criar é limitado apenas pelos recursos do sistema disponíveis. No entanto, os guias de plano devem ser limitados a consultas essenciais para a missão que são direcionadas para um desempenho aprimorado ou estabilizado. Guias de plano não devem ser usados para influenciar a maior parte da carga de consulta de um aplicativo implantado.

Tipos de guias de planos

Os seguintes tipos de guias de plano podem ser criados.

Guia do plano OBJECT
Um guia de plano OBJECT é associado às consultas que são executadas no contexto de procedimentos armazenados Transact-SQL, funções escalares definidas pelo usuário, funções definidas pelo usuário com valor de tabela de várias instruções e gatilhos DML.

Suponha que o seguinte procedimento armazenado, que usa o @Countryparâmetro _region , esteja em um aplicativo de banco de dados implantado no banco de dados AdventureWorks2012 :

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 tenha sido compilado e otimizado para @Country_region = N'AU' (Austrália). No entanto, como há relativamente poucos pedidos de vendas originários da Austrália, o desempenho diminui quando a consulta é executada usando valores de parâmetro de países/regiões com mais pedidos de vendas. Como a maioria dos pedidos de vendas se origina 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 @Countryparâmetro _region .

Você pode resolver esse problema modificando o procedimento armazenado para adicionar a OPTIMIZE FOR sugestão de consulta à 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 guia de plano a seguir no banco de dados AdventureWorks2012 .

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 sp_create_plan_guide instrução é executada, a consulta é modificada antes da otimização para incluir a OPTIMIZE FOR (@Country = N''US'') cláusula.

Guia do plano SQL
Um guia de plano SQL corresponde a consultas que são executadas no contexto de instruções Transact-SQL independentes e conjuntos de operações 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 corresponder a consultas que são parametrizadas para 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 enviadas por um aplicativo usando o 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 paralela seja gerado nessa consulta, crie o guia do 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)';  

Guias de plano SQL também podem ser criados em consultas que são parametrizadas para a mesma forma quando a opção de banco de dados PARAMETERIZATION é DEFINIDA como FORCED, ou quando um guia de plano TEMPLATE é criado especificando um conjunto parametrizado de consultas.

Guia do plano TEMPLATE
Um guia de plano TEMPLATE corresponde a consultas autônomas que parametrizam com um formulário especificado. Essas orientações de plano são usadas para substituir a opção de banco de dados SET PARAMETERIZATION atual 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 banco de dados PARAMETERIZATION está definida como FORÇADA, mas há consultas que você deseja compilar segundo as regras de parametrização simples.

• A opção de banco de dados PARAMETERIZATION é SET to SIMPLE (a configuração padrão), mas você deseja que a parametrização forçada seja testada em uma classe de consultas.

Requisitos de correspondência do guia de plano

Os guias de plano têm como escopo o banco de dados no qual são criados. Portanto, somente os guias de plano que estão no banco de dados vigente no momento da execução de uma consulta podem ser associados à consulta. Por exemplo, se AdventureWorks2012 for o banco de dados atual e a seguinte consulta for executada:

SELECT FirstName, LastName FROM Person.Person;

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

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

Somente os guias de plano DB1 são elegíveis para serem associados à consulta, já que a consulta está sendo executada no contexto de DB1.

Para guias de plano baseados em SQL ou TEMPLATE, o SQL Server corresponde os valores dos argumentos @module_or_batch e @params a uma consulta comparando ambos os 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 formato idêntico, exatamente como é enviado ao SQL Server. Nenhuma conversão interna é executada para facilitar essa 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 Plano no Cache de Planos

A criação de um guia de planos 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ção única do cache de plano dentro desse 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)