sp_create_plan_guide (Transact-SQL)

Cria uma guia de plano associando dicas de consulta ou planos de consulta reais a consultas em um banco de dados. Para obter mais informações sobre guias de plano, consulte Compreendendo os guias de plano.

Convenções de sintaxe Transact-SQL

Sintaxe

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      { 
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL } 
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' 
                 | N'XML_showplan'
                 | NULL }

Argumentos

• [ @name = ] N'plan_guide_name'
  É o nome da guia de plano. Os nomes de guia de plano são colocados no banco de dados atual. plan_guide_name deve estar em conformidade com as regras de identificadores e não pode iniciar com o símbolo de número (#). O comprimento máximo de plan_guide_name é de 124 caracteres.

• [ @stmt = ] N'statement_text'
  É uma instrução Transact-SQL para a qual deve ser criada um guia de plano. Quando o otimizador de consulta do SQL Server reconhece uma consulta que corresponde a statement_text, plan_guide_name entra em vigor. Para que a criação de um guia de plano tenha êxito, statement_text deve aparecer no contexto especificado pelos parâmetros @type, @module_or_batch e @params.

  statement_text deve ser fornecido de forma a permitir que o otimizador de consulta faça uma correspondência dele com a instrução fornecida correspondente dentro do lote ou módulo identificado pelos parâmetros @module_or_batch e @params. Para obter mais informações, consulte a seção “Comentários”. O tamanho de statement_text é limitado apenas pela memória disponível no servidor.

• [@type = ]N'{ OBJECT | SQL | TEMPLATE }'
  É o tipo de entidade na qual statement_text aparece. Especifica o contexto fazendo a correspondência de statement_text com plan_guide_name.

  • OBJECT
    Indica se statement_text aparece no contexto de um procedimento armazenado Transact-SQL, de uma função escalar, de uma função com valor de tabela com várias instruções ou do gatilho DML Transact-SQL no banco de dados atual.

  • SQL
    Indica se statement_text aparece no contexto de uma instrução ou lote autônomo que pode ser enviado ao SQL Server por meio de qualquer mecanismo. As instruções Transact-SQL enviadas por objetos CLR ou procedimentos armazenados estendidos ou pelo uso de EXEC N'sql_string' são processadas como lotes no servidor e, por isso, devem ser identificadas como @type = 'SQL'. Se SQL for especificado, a dica de consulta PARAMETERIZATION { FORCED | SIMPLE } não poderá ser especificada no parâmetro @hints.

  • TEMPLATE
    Indica se o guia de plano se aplica a qualquer consulta que aplica parâmetros ao formulário indicado em statement_text. Se TEMPLATE for especificado, apenas a dica de consulta PARAMETERIZATION { FORCED | SIMPLE } poderá ser especificada no parâmetro @hints. Para obter mais informações sobre guias de plano TEMPLATE, consulte Especificando comportamento de parametrização de consulta usando guias de plano.

• [@module_or_batch =]{ N'[ schema_name. ] object_name' | N'batch_text' | NULL }
  Especifica o nome do objeto no qual statement_text aparece ou o texto de lote em que statement_text aparece. O texto de lote não pode incluir uma instrução USEdatabase.

  Para que um guia de plano seja compatível com um lote enviado de um aplicativo, batch_tex deve ser fornecido no mesmo formato, caractere por caractere, quando for enviado ao SQL Server. Nenhuma conversão interna é executada para facilitar essa correspondência. Para obter mais informações, consulte a seção Comentários.

  [schema_name.]object_name especifica o nome de um procedimento armazenado Transact-SQL, função escalar, função com valor em tabela com várias instruções ou gatilho DML Transact-SQL que contém statement_text. Se schema_name não for especificado, schema_name usará o esquema do usuário atual. Se NULL for especificado e @type = 'SQL', o valor de @module_or_batch será definido como @stmt. Se @type = 'TEMPLATE**'**, @module_or_batch deverá ser NULL.

• [ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
  Especifica as definições de todos os parâmetros incorporados em statement_text. @params se aplicará somente quando uma das seguintes condições for verdadeira:

  • @type = 'SQL' ou 'TEMPLATE'. Se 'TEMPLATE', @params não deverá ser NULL.

  • statement_text é enviado usando sp_executesql, e um valor para o parâmetro @params é especificado, ou o SQL Server envia internamente uma instrução depois de aplicar-lhe parâmetros. O envio de consultas com parâmetros de APIs de banco de dados (incluindo ODBC, OLE DB e ADO.NET) é exibido para o SQL Server como chamadas para sp_executesql ou rotinas de cursor de servidor de API; portanto, a sua correspondência pode ser feita por guias de plano SQL ou TEMPLATE. Para obter mais informações sobre aplicação de parâmetros e guias de plano, consulte Como o SQL Server efetua a correspondência entre guias de plano e consultas.

  @parameter_name data_type deve ser fornecido exatamente no mesmo formato em que é enviado ao SQL Server usando sp_executesql ou enviado internamente após a aplicação de parâmetros. Para obter mais informações, consulte a seção Comentários. Se o lote não contiver parâmetros, NULL deverá ser especificado. O tamanho de @params é limitado apenas pela memória disponível no servidor.

• [@hints = ]{ N'OPTION ( query_hint [ ,...n ] )' | N'XML_showplan' | NULL }

  • N'OPTION ( query_hint [ ,...n ] )
    Especifica uma cláusula OPTION a anexar a uma consulta correspondente a @ stmt. @hints deve ser sintaticamente igual a uma cláusula OPTION em uma instrução SELECT e pode conter qualquer sequência válida de dicas de consulta.

  • N'XML_showplan'
    É o plano de consulta em formato XML a ser aplicado como dica.

    Recomendamos atribuir o Plano de execução XML a uma variável; caso contrário, você deve inserir um escape para quaisquer aspas simples no Plano de execução colocando antes delas outra aspa simples. Consulte o exemplo E.

  • NULL
    Indica que qualquer dica existente especificada na cláusula OPTION da consulta não é aplicada à consulta. Para obter mais informações, consulte Cláusula OPTION (Transact-SQL).

Comentários

Os argumentos para sp_create_plan_guide devem ser fornecidos na ordem em que aparecem. Quando você aplica valores para os parâmetros de sp_create_plan_guide, todos os nomes de parâmetros devem ser especificados explicitamente ou nenhum deles deve ser especificado. Por exemplo, se @name = for especificado, @stmt =, @type =, entre outros, também deverão ser. Da mesma forma, se @name = for omitido e apenas o valor de parâmetro for fornecido, os nomes de parâmetro restantes deverão ser omitidos também e apenas os seus valores, fornecidos. Os nomes de argumento são apenas para fins descritivos, para ajudar na compreensão da sintaxe. O SQL Server não verifica se o nome do parâmetro especificado corresponde ao nome do parâmetro na posição em que o nome é usado.

Você pode criar mais de um guia de plano OBJECT ou SQL para a mesma consulta e lote ou módulo. Porém, só um guia de plano pode ser ativado em um determinado momento.

Os guias de plano OBJECT não podem ser criados para um valor @module_or_batch que mencione um procedimento armazenado, função ou gatilho DML que especifique a cláusula WITH ENCRYPTION ou que seja temporário.

A tentativa de descartar ou modificar uma função, procedimento armazenado ou gatilho DML mencionado por um guia de plano, esteja ele habilitado ou não, causa um erro. A tentativa de descartar uma tabela com um gatilho definido nela que é mencionado por um guia de plano também causa um erro.

Observação

Os guias de plano só podem ser usados nas SQL Server Standard, Developer, Evaluation e Enterprise Editions. Por outro lado, eles podem ser visualizados 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 2008. Você deve verificar a finalidade dos guias de plano em cada banco de dados depois de executar uma atualização de servidor.

Guia de plano correspondente a requisitos

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.

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.

Permissões

A criação de um guia de plano do tipo OBJECT requer a permissão ALTER no objeto mencionado. A criação de um guia de plano do tipo SQL ou TEMPLATE requer a permissão ALTER no banco de dados atual.

Exemplos

A. Criando um guia de plano do tipo OBJECT para uma consulta em um procedimento armazenado

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

B. Criando um guia de plano do tipo SQL para uma consulta autônoma

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

C. Criando um guia de plano do tipo TEMPLATE para o formulário com parâmetros de uma consulta

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.

D. Criando um guia de plano em uma consulta enviada com o uso de uma solicitação de cursor API

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.

E. Criando um guia de plano por meio da obtenção do Plano de execução XML de um plano em cache.

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

Consulte também

Referência

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

Procedimentos armazenados do Mecanismo de Banco de Dados (Transact-SQL)

Procedimentos armazenados do sistema (Transact-SQL)

sys.dm_exec_sql_text (Transact-SQL)

sys.dm_exec_cached_plans (Transact-SQL)

sys.dm_exec_query_stats (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

sp_get_query_template (Transact-SQL)