sp_create_plan_guide (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstâ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'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 estar em conformidade com as regras para identificadores e não pode começar com o sinal de número (#). O comprimento máximo de plan_guide_name é de 124 caracteres.
[ @stmt = ] N'statement_text'
É uma instrução Transact-SQL na qual criar um guia de plano. Quando o otimizador de consulta 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 seja bem-sucedida, statement_text deve aparecer no contexto especificado pelos parâmetros @type, @module_or_batch e @params.
statement_text deve ser fornecido de uma forma que permita que o otimizador de consulta corresponda à instrução correspondente fornecida no lote ou módulo identificado por @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 do servidor.
[@type = ] N'{ OBJECT | SQL | TEMPLATE }'
É o tipo de entidade na qual statement_text aparece. Isso especifica o contexto para correspondência de statement_text a plan_guide_name.
OBJECT
Indica statement_text 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 statement_text aparece no contexto de uma instrução autônoma ou lote que pode ser enviado para SQL Server por meio de qualquer mecanismo. 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 que o guia de plano se aplica a qualquer consulta que parametrize para o 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 o 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 em lote no qual statement_text aparece. O texto em lote não pode incluir uma instrução USEdatabase .
Para que um guia de plano corresponda a um lote enviado de um aplicativo, batch_text deve ser fornecido no mesmo formato, caractere por caractere, pois é enviado para 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ários estados 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 o valor de @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 no 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_executesqle um valor para o parâmetro @params é especificado ou SQL Server envia internamente uma instrução após parametrizá-la. O envio de consultas parametrizadas de APIs de banco de dados (incluindo ODBC, OLE DB e ADO.NET) parece SQL Server como chamadas parasp_executesqlou para rotinas de cursor do servidor de API; portanto, elas também podem ser correspondidas por guias de plano SQL ou TEMPLATE.
@parameter_name data_type deve ser fornecido exatamente no mesmo formato que é enviado para 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 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 ser anexada a uma consulta que corresponda 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.
NULO
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ê fornece 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, então @stmt =, @type =, e assim por diante, também deverão ser especificados. Da mesma forma, se @name = for omitido e apenas o valor de parâmetro for fornecido, os nomes de parâmetro restantes também deverão ser omitidos e apenas os seus valores fornecidos. Os nomes de argumento são usados apenas para fins descritivos, para ajudar compreender a sintaxe. 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 cancelar ou modificar uma função, procedimento armazenado ou gatilho DML referenciado por um guia de plano, habilitado ou desabilitado, provoca 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 não podem ser usados em todas as edições do MicrosoftSQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, consulte Recursos com suporte nas edições do SQL Server 2016. 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 de Plano
Para guias de plano que especificam @type = 'SQL' ou @type = 'TEMPLATE' para corresponder com êxito a uma consulta, os valores de batch_text e @parameter_name data_type [,... n ] deve ser fornecido exatamente no mesmo formato que seus equivalentes enviados pelo aplicativo. 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 SQL Server Profiler. Para obter mais informações, consulte Usar 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 de statement_text deve ser fornecido exatamente no mesmo formato, caractere por caractere, que é enviado para SQL Server. Nenhuma conversão interna é executada para facilitar essa correspondência.
Quando SQL Server corresponde ao valor de statement_text a batch_text e @parameter_name data_type [,... n ], ou se @type = **'**OBJECT', para o texto da consulta correspondente dentro de object_name, os seguintes elementos de 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, SQL Server pode corresponder a cadeia de caracteres statement_textN'SELECT * FROM T WHERE a = 10' ao seguinte batch_text:
N'SELECT *
FROM T
WHERE a = 10'
No entanto, a mesma cadeia de caracteres não corresponderia a esta batch_text:
N'SELECT * FROM T WHERE b = 10'
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), exceto no caso de palavras-chave, no qual não há diferenciação. 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 guia de plano no cache do 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.
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
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 para corresponder a uma consulta em um lote enviado por um aplicativo que usa o procedimento armazenado do sp_executesql sistema.
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 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;
Este é o guia de plano na forma com parâmetros 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. 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 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 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 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.
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 as APIs ADO, OLE DB e ODBC frequentemente interagem com SQL Server usando cursores de servidor de API. Você pode ver a invocação de rotinas de cursor de servidor de API em rastreamentos SQL Server Profiler exibindo o evento de rastreamento RPC:Starting profiler.
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 XML Showplan para a consulta diretamente no parâmetro @hints para que o plano de consulta desejado para essa 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_texte 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 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
Consulte Também
Guias de 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)
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de