Partilhar via

sp_create_plan_guide (Transact-SQL)

  • Artigo

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 Guias de plano.

Ícone de vínculo de tópico Convenções da 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 têm escopo no banco de dados atual. plan_guide_name deve obedecer às regras de identificadores e não pode iniciar com o símbolo numérico (#). 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 que statement_text aparece no contexto de um lote ou uma instrução autônoma que pode ser enviado a SQL Server através de qualquer mecanismo. As instruções Transact-SQL enviadas por objetos CLR (Common Language Runtime) ou procedimentos armazenados estendidos, ou usando EXEC N'sql_string', são processadas como lotes no servidor e, portanto, 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 Especificar 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 inseridos 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 parametrizadas de APIs de banco de dados (incluindo ODBC, OLE DB e ADO.NET) aparece no 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.

    @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 usados apenas para fins descritivos, para ajudar compreender a sintaxe. O SQL Server não verifica se o nome de 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çãoObservaçã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. 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 bem sucedida com uma consulta, 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 Usar o SQL Server Profiler para criar e testar guias de plano.

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 SQL Server corresponde o valor de statement_text a batch_text e a @parameter\_name data_type [,...n ] ou se @type = **'**OBJECT' ao texto da consulta correspondente em object_name, os seguintes elementos de cadeia de caracteres não sã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_text N'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.

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 parametrizado 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 AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.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 AdventureWorks2012.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2012.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 usar em sp_create_plan_guide é usar o procedimento armazenado do sistema sp_get_query_template. O script a seguir pode ser usado para obter a consulta parametrizada 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 AdventureWorks2012.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2012.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 dos literais constantes no 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.

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. Os aplicativos que usam APIs ADO, OLE DB e ODBC interagem frequentemente com o SQL Server usando 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 criador de perfil 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 AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @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 BETWEEN @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.

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

Conceitos

Guias de plano