Compartilhar via

sp_create_plan_guide (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

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 Plan Guides.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

@name [ = ] N'nome'

O nome do guia do plano. @name é sysname, sem padrão e com um comprimento máximo de 124 caracteres. Os nomes de guia de plano têm escopo no banco de dados atual. @name deve estar em conformidade com as regras de identificadores e não pode começar com o sinal de número (#).

@stmt [ = ] N'stmt'

Uma instrução Transact-SQL na qual criar um guia de plano. @stmt é nvarchar(max), com um padrão de NULL. Quando o otimizador de consulta do SQL Server reconhece uma consulta que corresponde a @stmt, @name entra em vigor. Para que a criação de um guia de plano seja bem-sucedida, @stmt deve aparecer no contexto especificado pelos parâmetros @type, @module_or_batch e @params .

@stmt devem ser fornecidos de forma a permitir que o otimizador de consulta corresponda com a instrução correspondente, fornecida no lote ou módulo identificado pelo @module_or_batch e @params. Para obter mais informações, consulte a seção Comentários. O tamanho do @stmt é limitado apenas pela memória disponível do servidor.

@type [ = ] { N'OBJETO' | N'SQL' | N'TEMPLATE' }

O tipo de entidade em que @stmt aparece. Isso especifica o contexto para corresponder @stmt a @name. @type é nvarchar(60) e pode ser um destes valores:

  • OBJECT

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

  • SQL

    Indica @stmt aparece no contexto de uma instrução autônoma ou lote que pode ser enviado ao SQL Server por meio 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 de SQL. Se SQL for especificado, a dica PARAMETERIZATION { FORCED | SIMPLE } de consulta não poderá ser especificada no parâmetro @hints .

  • TEMPLATE

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

@module_or_batch [ = ] { N' [ schema_name. ] object_name' | N'batch_text' }

Especifica o nome do objeto no qual @stmt aparece ou o texto em lote no qual @stmt aparece. @module_or_batch é nvarchar(max), com um padrão de NULL. O texto do lote não pode incluir uma USE <database> instrução.

Para que um guia de plano corresponda a um lote enviado de um aplicativo, @module_or_batch deve ser fornecido no mesmo formato, caractere por caractere, como é 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 de tabela de várias instruções ou gatilho DML Transact-SQL que contém @stmt. Se <schema_name> não for especificado, <schema_name> usa o esquema do usuário atual. Se NULL for especificado e @type for SQL, o valor de @module_or_batch será definido como o valor de @stmt. Se @type é TEMPLATE, @module_or_batch deve ser NULL.

@params [ = ] N'@parameter_name data_type [ ,... n ]'

Especifica as definições de todos os parâmetros incorporados no @stmt. @params é nvarchar(max), com um padrão de NULL. @params se aplica somente quando uma das seguintes opções é verdadeira:

  • @type é SQL ou TEMPLATE. Se TEMPLATE, @params não deve ser NULL.

  • @stmt é enviado usando sp_executesql e um valor para o parâmetro @params é especificado ou o SQL Server envia internamente uma instrução depois de parametrizá-la. O envio de consultas parametrizadas de APIs de banco de dados (incluindo ODBC, OLE DB e ADO.NET) aparece para o SQL Server como chamadas para sp_executesql ou para rotinas de cursor do servidor de API; portanto, elas também podem ser correspondidas por SQL guias de plano ou TEMPLATE .

@params deve ser fornecido exatamente no mesmo formato em que é enviado ao SQL Server usando sp_executesql ou enviado internamente após a parametrização. 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 do @params é limitado apenas pela memória disponível do servidor.

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

@hints é nvarchar(max), com um padrão de NULL.

  • OPTION ( <query_hint> [ , ...n ] )

    Especifica uma OPTION cláusula a ser anexada a uma consulta que corresponda a @stmt. @hints deve ser sintaticamente igual a uma OPTION cláusula em uma SELECT instrução e pode conter qualquer sequência válida de dicas de consulta.

  • <XML_showplan>'

    O plano de consulta no formato XML a ser aplicado como uma dica.

    Recomendamos atribuir o plano de execução XML a uma variável. Caso contrário, você deve escapar de aspas simples no plano de execução precedendo-as com outras aspas simples. Veja o exemplo E.

  • NULL

    Indica que qualquer dica existente especificada na OPTION cláusula da consulta não é aplicada à consulta. Para obter mais informações, consulte a cláusula OPTION.

Comentários

Os argumentos devem sp_create_plan_guide ser fornecidos na ordem mostrada. 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 do parâmetro especificado corresponde ao nome do parâmetro na posição em que o nome é usado.

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

Guias de plano do tipo OBJECT não podem ser criados para um valor @module_or_batch que faz referência a um procedimento armazenado, função ou gatilho DML que especifica a WITH ENCRYPTION cláusula ou que é temporário.

A tentativa de cancelar ou modificar uma função, procedimento armazenado ou gatilho DML referenciado por um guia de plano, habilitado ou desabilitado, provoca um erro. Tentar descartar uma tabela que é um gatilho definido nela que é referenciado por um guia de plano também causa um erro.

Os guias de plano não podem ser usados em todas as edições do SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022. 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.

Requisitos de correspondência do guia do plano

Para guias de plano que especificam @type ou SQL TEMPLATE para corresponder com êxito a uma consulta, os valores de @module_or_batch e @params [, ... n ] devem ser fornecidos exatamente no mesmo formato que seus equivalentes apresentados pelo pedido. Isso significa que você deve fornecer o texto em lote exatamente como o compilador do SQL Server o recebe. Para capturar o texto real do lote e do 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 é e @module_or_batch é definido como NULL, o valor de @module_or_batch é definido como o valor de @stmt. SQL Isso significa que o valor de @stmt deve ser fornecido exatamente no mesmo formato, caractere por caractere, que é enviado ao SQL Server. Nenhuma conversão interna é executada para facilitar essa correspondência.

Quando o SQL Server corresponde o valor de @stmt a @module_or_batch e @params [, ... n ], ou se @type for OBJECT, para o texto da consulta correspondente dentro <object_name>de , os seguintes elementos de cadeia de caracteres não serão considerados:

  • Caracteres de espaço em branco (tabulações, 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 corresponder a cadeia de caracteres N'SELECT * FROM T WHERE a = 10' @stmt à seguinte @module_or_batch:

 N'SELECT *
 FROM T
 WHERE a = 10'

No entanto, a mesma cadeia de caracteres não corresponderia a esta @module_or_batch:

N'SELECT * FROM T WHERE b = 10'

O SQL Server ignora o retorno de carro, a alimentação de linha e os 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 a ordenação do banco de dados não diferencia maiúsculas de minúsculas), exceto se houver palavras-chave, em que maiúsculas e minúsculas não diferenciam. A correspondência é sensível a espaços em branco. 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 de planos

Criar um guia de plano em um módulo remove o plano de consulta desse módulo do cache do esquema. A criação de um guia de plano do tipo OBJECT ou SQL em um lote remove o plano de consulta de um lote com o mesmo valor de hash. A criação de um guia de plano do tipo TEMPLATE remove todos os lotes de instrução única do cache de planos nesse banco de dados.

Permissões

Para criar um guia de plano do tipo OBJECT, é necessária ALTER permissão no objeto referenciado. Para criar um guia de plano do tipo SQL ou TEMPLATE, é necessária ALTER permissão no banco de dados atual.

Exemplos

R. Criar 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

Aqui está 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. Criar um guia de plano do tipo SQL para uma consulta autônoma

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

Aqui está 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. Criar 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 entre qualquer consulta parametrizada e 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 AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

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

Aqui está o guia de plano na forma parametrizada da consulta:

EXEC sp_create_plan_guide
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2022.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. A única maneira confiável de obter esse valor para uso é sp_create_plan_guide usar o procedimento armazenado do sistema sp_get_query_template . O script a seguir obtém a consulta parametrizada e cria um guia de plano nela.

DECLARE @stmt NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

EXEC sp_get_query_template N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2022.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 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. Talvez seja necessário criar mais de um guia de plano para lidar com diferentes intervalos de valores de parâmetro.

D. Criar um guia de plano em uma consulta enviada usando uma solicitação de cursor de 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_cursorprepexece sp_cursoropen. Os aplicativos que usam as APIs ADO, OLE DB e ODBC frequentemente interagem com o SQL Server usando cursores de servidor de API. Você pode ver a invocação de rotinas de cursor do servidor de API em rastreamentos do SQL Server Profiler exibindo o evento de rastreamento do RPC:Starting criador de perfil.

Suponha que os seguintes dados apareçam em um RPC:Starting evento de rastreamento do criador de perfil 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 são afetadas por este guia de plano e uma junção de hash é usada para processar a consulta.

E. Criar um guia de plano obtendo o plano de execução XML de um plano armazenado em cache

O exemplo a seguir cria um guia de plano para uma instrução ad hoc SQL simples. O plano de consulta desejado para essa instrução é fornecido no guia de plano, especificando o plano de execução XML para a consulta diretamente no @hints parâmetro. O exemplo primeiro executa a SQL instrução para gerar um plano no cache de planos. Para os fins deste exemplo, supõe-se que o plano gerado seja o plano desejado e nenhum ajuste de consulta adicional seja necessário. O plano de execução XML para a consulta é obtido consultando as sys.dm_exec_query_statsexibições de gerenciamento dinâmico e sys.dm_exec_sql_textsys.dm_exec_text_query_plan , e é atribuído à @xml_showplan variável. 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 com base em um plano de consulta no cache de plano por meio do procedimento armazenado sp_create_plan_guide_from_handle .

USE AdventureWorks2022;
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

Conteúdo relacionado