Compreendendo os guias de plano

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

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çã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.

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

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

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