Guias de plano

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 2012. 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 a guia de plano e anexa a cláusula OPTION à consulta em tempo de execução ou usa o plano de consulta especificado.

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.

Observação

Não é possível usar guias de plano 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 compatíveis com as edições do SQL Server 2012. 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 as consultas executadas no contexto dos procedimentos armazenados do Transact-SQL, funções escalares definidas pelo usuário, funções com valor de tabela de várias instruções definidas pelo usuário e 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 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 esse procedimento armazenado foi compilado e otimizado para @Country\_region = N'AU' (Austrália). Entretanto, já que há relativamente poucas vendas oriundas da Austrália, o desempenho cai quando a consulta é executada com os valores de parâmetro de países com mais pedidos de vendas. 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 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 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
  O guia de plano SQL correlaciona consultas que são executadas no contexto de instruções e lotes do Transact-SQL autônomo que não fazem parte de um objeto do 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 de 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)';
  

  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 consulte 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 AdventureWorks2012 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 AdventureWorks2012 são elegíveis para corresponderem a essa consulta. Porém, se AdventureWorks2012 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 com base em TEMPLATE ou SQL, o SQL Server coincide os valores dos argumentos @module\_or\_batch e @params para uma consulta, comparando os dois valores, caractere por caractere. Isso significa você deve fornecer o texto exatamente como SQL Server recebe no lote atual.

Quando @type = 'SQL' e @module\_or\_batch estão definidos como NULL, o valor de @module\_or\_batch é definido como o valor de @stmt. Isso significa que o valor de statement_text deve ser fornecido exatamente no mesmo formato, caractere a caractere dígito, como 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 database USE.

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.

Tarefas relacionadas

Consulte também

Referência

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)