Partilhar via

  • Artigo

Compreendendo os guias de plano

Este tópico descreve os guias de plano e explica como podem ser usados para otimizar o desempenho das consultas, quando não é possível ou desejado alterar diretamente o texto da consulta. Os guias de plano podem ser úteis quando um subconjunto pequeno de consultas no aplicativo de banco de dados, oferecido por um fornecedor terceirizado, não estiver sendo executado como esperado. Os guias de plano influenciam a otimização das consultas, anexando dicas de consulta ou um plano de consulta fixo. 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.

ObservaçãoObservação

Os guias de plano só podem ser usados no SQL Server Standard, Developer, Evaluation e edições Enterprise; no entanto, eles são visíveis em qualquer edição. Também é possível anexar um banco de dados que contém 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 2008.

Correspondendo guias de plano com consultas

Os guias de plano podem ser criados para corresponder consultas executadas nos seguintes contextos:

  • 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.

  • 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.

  • 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. Para obter mais informações, consulte Parametrização simples e Parametrização forçada.

Para obter mais informações, consulte Como o SQL Server efetua a correspondência entre guias de plano e consultas.

Guias de plano OBJECT

Suponha que o procedimento armazenado a seguir, que toma o parâmetro @Country\_region, exista em um aplicativo de banco de dados implantado no banco de dados AdventureWorks:

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 é prejudicado 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 AdventureWorks.

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

Guias de plano SQL

Os guias de plano SQL se aplicam a instruções e lotes Transact-SQL autônomos. Freqüentemente, 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)';
ObservaçãoObservaçã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.
Observação importanteImportante

Os valores fornecidos pelos 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 Usando 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. Para obter mais informações, consulte Criando guias de plano para consultas com parâmetros.

Guias de plano TEMPLATE

Os guias de plano TEMPLATE são usados para substituir o comportamento da parametrização para formas de consultas específicas. É 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 feita em uma classe de consultas.

Para obter mais informações, consulte Especificando comportamento de parametrização de consulta usando guias de plano.

O exemplo a seguir cria um guia de plano que corresponde qualquer consulta que forneça parâmetros de uma forma especificada e direciona SQL Server para forçar a parametrização da consulta. As duas consultas a seguir são sintaticamente equivalentes, mas só diferem nos valores literais constantes.

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Esse é o guia de plano na forma com parâmetros da consulta:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

No exemplo anterior, o valor para o parâmetros @stmt é a forma com parâmetro da consulta. O único modo confiável de obter esse valor para uso em sp_create_plan_guide é por meio do procedimento armazenado de sistema sp_get_query_template. O script a seguir pode ser usado para obter a consulta com parâmetros e, em seguida, criar um guia de plano para ela.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';
Observação importanteImportante

O valor das literais constantes do parâmetro @stmt passadas para sp_get_query_template podem afetar o tipo de dados escolhido para o parâmetro que substitui a literal. Isso afetará a correspondência do guia de plano. Pode ser necessário criar mais de um guia de plano para lidar com diferentes intervalos de valores de parâmetros.

Também é possível usar os guias de plano TEMPLATE com os guias de plano SQL. Por exemplo, você pode criar um guia de plano TEMPLATE para ter certeza que uma classe de consultas contém parâmetros. Em seguida, é possível criar um guia de plano SQL na forma com parâmetros dessa consulta.

Aplicando um plano de consulta fixo a um guia de plano

É possível aplicar um plano de consulta fixo a um guia de plano do tipo OBJECT ou SQL. Os guias de plano que se aplicam a um plano de consulta fixo são úteis quando se conhece um plano de execução existente que é melhor do que aquele selecionado pelo otimizador para uma consulta específica.

O exemplo a seguir cria um guia de plano para uma instrução SQL ad hoc simples. Especifique o XML Showplan para a consulta diretamente no parâmetro @hints para que o plano de consulta desejado para essa instrução seja fornecido no guia de plano. O exemplo executa a instrução SQL primeiro para gerar um plano no cache de plano. Nesse exemplo, supõe-se que o plano gerado é o desejado e que nenhum ajuste de consulta adicional é necessário. Para obter o XML Showplan para a consulta e atribuí-lo à variável @xml\_showplan, consulte as exibições dinâmicas sys.dm_exec_query_stats, sys.dm_exec_sql_text e sys.dm_exec_text_query_plan. Em seguida, a variável @xml\_showplan é passada à instrução sp_create_plan_guide no parâmetro @hints. Também é possível criar um guia de plano a partir de um plano de consulta no cache de plano por meio do procedimento armazenado sp_create_plan_guide_from_handle.

USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = @xml_showplan;
GO

Validando guias de plano depois da atualização

Recomendamos que as definições do guia de plano sejam reavaliadas e testadas quando o aplicativo for atualizado para uma nova versão do SQL Server. Os requisitos de ajuste de desempenho e o comportamento da correlação do guia de plano podem variar. Embora um guia inválido não cause a falha da consulta, o plano é compilado sem usar o guia de plano e pode não ser a melhor escolha. Depois de atualizar o banco de dados para SQL Server 2008, nós recomendamos que as seguintes tarefas sejam executadas: