sp_create_plan_guide (Transact-SQL)

Em guias de plano que especificam @type = 'SQL' ou @type = 'TEMPLATE' para que se possa fazer uma correspondência com êxito, os valores de batch_text e @parameter_name data_type [,...n ] devem ser fornecidos exatamente no mesmo formato dos seus equivalentes enviados pelo aplicativo. Isso significa você deve fornecer o texto de lote exatamente como o compilador do SQL Server o recebe. Para capturar o lote real e texto de parâmetro, você pode usar o SQL Server Profiler. Para obter mais informações, consulte Usando o SQL Server Profiler para criar e testar guias de plano.

Quando @type = 'SQL' e @module_or_batch são configurados 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 o SQL Server fizer a correspondência do valor de statement_text com batch_text e @parameter_name data_type [,...n ], ou se @type = **'**OBJECT', com o texto da consulta correspondente em object_name, os seguintes elementos da cadeia de caracteres não serão considerados:

• Caracteres de espaço em branco (guias, espaços, retornos de carro ou alimentações de linha) dentro da cadeia de caracteres.

• Comentários (-- ou /* * /).

• Ponto-e-vírgulas à direita

Por exemplo, o SQL Server pode fazer a correspondência da cadeia de caracteres statement_textN'SELECT * FROM T WHERE a = 10' com o seguinte batch_text:

N'SELECT *

FROM T

WHERE a=10'

Entretanto, a mesma cadeia de caracteres não deve corresponder a esse batch_text:

N'SELECT * FROM T WHERE b = 10'

O SQL Server ignora o retorno de carro, a alimentação de linha e caracteres de espaço dentro da primeira consulta. Na segunda consulta, a sequência WHERE b = 10 é interpretada diferentemente de WHERE a = 10. A correspondência diferencia maiúsculas de minúsculas e acentos (mesmo quando o agrupamento do banco de dados não diferencia), exceto no caso de palavras-chave, no qual não há diferenciação. A correspondência não diferencia maiúsculas de minúsculas em formas abreviadas de palavras-chave. Por exemplo, as palavras-chave EXECUTE, EXEC e execute são consideradas equivalentes.

Para obter mais informações sobre como é feita a correspondência entre os guias de plano e as consultas, consulte Otimizando consultas em aplicações implantadas com guias de plano.

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.

O exemplo a seguir cria um guia de plano que faz a correspondência de uma consulta executada no contexto de um procedimento armazenado com base em aplicativo e aplica a dica OPTIMIZE FOR à consulta.

Aqui está o procedimento armazenado:

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

Este é o guia de plano criado na consulta no procedimento armazenado:

EXEC sp_create_plan_guide 
    @name =  N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.Customer AS c 
                 ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t 
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

O exemplo a seguir cria um guia de plano que faz a correspondência de uma consulta em um lote enviado por um aplicativo que usa o procedimento armazenado do sistema sp_executesql.

Este é o lote:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Para impedir que um plano de execução paralelo seja gerado nesta consulta, crie o seguinte guia de plano:

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @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)';

O exemplo a seguir cria um guia de plano que faz a correspondência de qualquer consulta com parâmetros com um formulário especificado e direciona o SQL Server para forçar a aplicação de parâmetros da consulta. As duas consultas a seguir são sintaticamente equivalentes, mas só diferem nos valores literais constantes.

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

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

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

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.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 do parâmetro @stmt é a forma com parâmetros da consulta. O único modo confiável de obter esse valor para uso em sp_create_plan_guide é por meio do procedimento armazenado do 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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.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 passado para sp_get_query_template pode 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.

Para obter mais informações sobre como obter o formulário com parâmetros de uma consulta a ser usada em um guia de plano baseado em TEMPLATE, consulte Criando guias de plano para consultas com parâmetros.

Os guias de plano podem ser correspondentes a consultas enviadas das rotinas de cursor de servidor de API. Essas rotinas incluem sp_cursorprepare, sp_cursorprepexec e sp_cursoropen. Aplicativos que usam ADO, OLE DB e ODBC APIs frequentemente interagem com o SQL Server usando cursores de servidor API. Para obter mais informações, consulte Cursores de servidor de API. É possível verificar a chamada das rotinas de cursor de servidor de API nos rastreamentos do SQL Server Profiler por meio da exibição do evento de rastreamento do profiler RPC:Starting.

Suponha que os dados a seguir apareçam em um evento de rastreamento do profiler RPC:Starting para uma consulta que você deseja ajustar com um guia de plano:

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @P1 AND <= @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

Observe que o plano da consulta SELECT na chamada de sp_cursorprepexec está usando uma junção de mesclagem, mas você deseja usar uma junção de hash. A consulta enviada com o uso de sp_cursorprepexec tem parâmetros, incluindo uma cadeia de caracteres de consulta e outra de parâmetros. Você pode criar o seguinte guia de plano para alterar a opção de plano usando as cadeias de caracteres de consulta e de parâmetro exatamente como elas são exibidas, caractere por caractere, na chamada de sp_cursorprepexec.

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate >= @P1 AND <= @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

As execuções subsequentes dessa consulta pelo aplicativo serão afetadas por esse guia de plano, e uma junção de hash será usada para processar a consulta.

Para obter mais informações sobre como usar a dica de consulta USE PLAN em um guia de plano de uma consulta enviada com um cursor, consulte Usando a dica de consulta USE PLAN em consultas com cursores.

O exemplo a seguir cria um guia de plano para uma instrução SQL ad hoc simples. Especifique o Plano de execução XML para a consulta diretamente no parâmetro @hints para que o plano de consulta desejado para esta instrução seja fornecido no guia de plano. O exemplo executa a instrução SQL primeiro para gerar um plano no cache do esquema. Nesse exemplo, supõe-se que o plano gerado é o desejado e que nenhum ajuste de consulta adicional é necessário. O Plano de execução XML da consulta é obtido por meio da consulta das exibições de gerenciamento dinâmico sys.dm_exec_query_stats, sys.dm_exec_sql_text e sys.dm_exec_text_query_plan e é atribuído à variável @xml_showplan. 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 AdventureWorks2008R2;
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