sp_create_plan_guide (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Cria um guia de plano para associar 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.

Transact-SQL convenções de sintaxe

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 um comprimento máximo de 124 caracteres. Os nomes dos guias de planejamento têm como escopo o banco de dados atual. @name deve estar em conformidade com as regras para identificadores e não pode começar com o sinal numérico (#).

@stmt [ = ] N'stmt'

Uma declaração Transact-SQL contra a 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 à instrução correspondente, fornecida dentro do lote ou módulo identificado por @module_or_batch e @params. Para obter mais informações, consulte a seção Observações. O tamanho do @stmt é limitado apenas pela memória disponível do servidor.

@type [ = ] { N'OBJECT' | N'SQL' | N'MODELO' }

O tipo de entidade na qual @stmt aparece. Isso especifica o contexto para a correspondência de @stmt com @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. Transact-SQL instruções enviadas por objetos CLR (Common Language Runtime) ou procedimentos armazenados estendidos, ou usando EXECUTE 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 em @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 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, que é enviado ao SQL Server. Nenhuma conversão interna é realizada para facilitar esta correspondência. Para obter mais informações, consulte a seção Observações.

[ <schema_name>. ] <object_name> especifica o nome de um Transact-SQL procedimento armazenado, função escalar, função com valor de tabela de várias instruções ou Transact-SQL gatilho DML 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_namedata_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 for 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 planejamento ou TEMPLATE de andamento.

@params devem ser fornecidos exatamente no mesmo formato em que são enviados ao SQL Server usando sp_executesql ou enviados internamente após a parametrização. Para obter mais informações, consulte a seção Observações. Se o lote não contiver parâmetros, NULL deve 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 o mesmo que 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 em formato XML a ser aplicado como uma dica.

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

• NULL

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

Observações

Os argumentos a sp_create_plan_guide apresentar devem ser apresentados pela ordem apresentada. Quando você fornece valores para os parâmetros do , todos os nomes de sp_create_plan_guideparâmetros devem ser especificados explicitamente ou nenhum. Por exemplo, se @name = é especificado, então @stmt =, @type =, e assim por diante, também deve ser especificado. Da mesma forma, se @name = for omitido e apenas o valor do parâmetro for fornecido, os nomes de parâmetros restantes também devem ser omitidos, e apenas seus valores fornecidos. Os nomes dos argumentos são apenas para fins descritivos, para ajudar a entender 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 planejar para a mesma consulta e lote ou módulo. No entanto, apenas um guia de planos pode ser ativado a qualquer momento.

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

Tentar remover ou modificar uma função, procedimento armazenado ou gatilho DML referenciado por um guia de plano, seja ele habilitado ou desativado, resulta num erro. Tentar soltar 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 funcionalidades suportadas pelas edições do SQL Server, consulte Edições e funcionalidades suportadas do SQL Server 2022. Os guias de planos são visíveis em qualquer edição. Você também pode anexar um banco de dados que contenha guias de planos para qualquer edição. Os guias de planejamento permanecem intactos quando você restaura ou anexa um banco de dados a uma versão atualizada do SQL Server. Você deve verificar a conveniência dos guias de plano em cada banco de dados depois de executar uma atualização do servidor.

Requisitos de correspondência do guia de plano

Para guias de plano que especificam @type de SQL ou TEMPLATE para corresponder com êxito a uma consulta, os valores para @module_or_batch e @params [, ... n ] devem ser fornecidos exatamente no mesmo formato que os seus homólogos apresentados pela candidatura. 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 é SQL e @module_or_batch é definido como NULL, o valor de @module_or_batch é definido como o valor de @stmt. Isso significa que o valor para @stmt deve ser fornecido exatamente no mesmo formato, caractere por caractere, que é enviado para o SQL Server. Nenhuma conversão interna é realizada para facilitar esta correspondência.

Quando o SQL Server corresponde ao valor de @stmt para @module_or_batch e @params [, ... n ], ou se @type for OBJECT, para o texto da consulta correspondente dentro <object_name>, os seguintes elementos de cadeia de caracteres não sã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írgula à direita

Por exemplo, o SQL Server pode corresponder a cadeia de caracteresN'SELECT * FROM T WHERE a = 10' de @stmt aos seguintes @module_or_batch:

 N'SELECT *
 FROM T
 WHERE a = 10'

No entanto, a mesma string não seria correspondida a este @module_or_batch:

N'SELECT * FROM T WHERE b = 10'

O SQL Server ignora o retorno de carro, o feed de linha e os caracteres de espaço dentro da primeira consulta. Na segunda consulta, a sequência WHERE b = 10 é interpretada de forma diferente 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 maiúsculas de minúsculas), exceto se houver palavras-chave, onde maiúsculas e minúsculas não diferencia maiúsculas de minúsculas. A correspondência é sensível a espaços em branco. A correspondência é insensível a 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 plano

A criação de um guia de plano em um módulo remove o plano de consulta para esse módulo do cache de planos. Criar um guia de plano do tipo OBJECT ou em um lote SQL remove o plano de consulta para um lote que é 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 plano dentro desse banco de dados.

Permissões

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

Exemplos

Um. 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 corresponde a uma consulta executada no contexto de um procedimento armazenado baseado 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:

EXECUTE 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 evitar que um plano de execução paralelo seja gerado nessa consulta, crie o seguinte guia de plano:

EXECUTE 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 a forma parametrizada de uma consulta

O exemplo a seguir cria um guia de plano que corresponde a qualquer consulta que se parametriza num formulário especificado e direciona o SQL Server para forçar a parametrização da consulta. As duas consultas a seguir são sintaticamente equivalentes, mas diferem apenas em seus 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:

EXECUTE 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 para o parâmetro @stmt é a forma parametrizada 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, em seguida, cria um guia de plano sobre ela.

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

EXECUTE 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;

EXECUTE 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 o literal. Isso afetará a correspondência do guia de planos. Talvez seja necessário criar mais de um guia de plano para lidar com diferentes intervalos de valores de parâmetros.

D. Criar um guia de plano em uma consulta enviada usando uma solicitação de cursor de API

Os guias de planejamento podem corresponder às consultas enviadas a partir de rotinas de cursor do servidor de API. Essas rotinas incluem sp_cursorprepare, sp_cursorprepexec, e 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 profiler.

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 AS INT;
SET @p1 = -1;

DECLARE @p2 AS INT;
SET @p2 = 0;

DECLARE @p5 AS INT;
SET @p5 = 4104;

DECLARE @p6 AS INT;
SET @p6 = 8193;

DECLARE @p7 AS INT;
SET @p7 = 0;

EXECUTE 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;

Você percebe que o plano para a SELECT consulta na chamada para sp_cursorprepexec está usando uma associação de mesclagem, mas deseja usar uma associação de hash. A consulta enviada usando sp_cursorprepexec é parametrizada, incluindo uma cadeia de caracteres de consulta e uma cadeia de caracteres de parâmetro. Você pode criar o seguinte guia de plano para alterar a escolha do plano usando as cadeias de caracteres de consulta e parâmetro exatamente como elas aparecem, caractere por caractere, na chamada para sp_cursorprepexec.

EXECUTE 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 esta 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 plano. Para os fins deste exemplo, presume-se que o plano gerado é o plano desejado e nenhum ajuste de consulta adicional é necessário. O plano de exibiçã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. A @xml_showplan variável é então passada para a sp_create_plan_guide instrução no @hints parâmetro. Ou, você pode criar um guia de plano a partir de um plano de consulta no cache de plano usando o 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 AS 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;%'
);

EXECUTE 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

• Guias do Plano
• 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)