sp_create_plan_guide (Transact-SQL)sp_create_plan_guide (Transact-SQL)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Cria uma guia de plano associando dicas de consulta ou planos de consulta reais a consultas em um banco de dados.Creates a plan guide for associating query hints or actual query plans with queries in a database. Para obter mais informações sobre guias de plano, consulte Plan Guides.For more information about plan guides, see Plan Guides.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

  
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 }  

ArgumentosArguments

[ @name = ] N'plan_guide_name'[ @name = ] N'plan_guide_name'
É o nome da guia de plano.Is the name of the plan guide. Os nomes de guia de plano têm escopo no banco de dados atual.Plan guide names are scoped to the current database. plan_guide_name deve estar em conformidade com as regras para identificadores e não pode começar com o sinal numérico (#).plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#). O comprimento máximo de plan_guide_name é de 124 caracteres.The maximum length of plan_guide_name is 124 characters.

[ @stmt = ] N'statement_text'[ @stmt = ] N'statement_text'
É uma instrução Transact-SQLTransact-SQL para a qual deve ser criada um guia de plano.Is a Transact-SQLTransact-SQL statement against which to create a plan guide. Quando o SQL ServerSQL Server otimizador de consulta reconhece uma consulta que corresponde a statement_text, plan_guide_name entra em vigor.When the SQL ServerSQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect. Para que a criação de um guia de plano seja bem sucedido, statement_text deve aparecer no contexto @especificado pelos @parâmetros Type, @module_or_batch e params.For the creation of a plan guide to succeed, statement_text must appear in the context specified by the @type, @module_or_batch, and @params parameters.

statement_text deve ser fornecido de forma que permita que o otimizador de consulta coincida com a instrução correspondente fornecida no lote ou no módulo identificado por @module_or_batch e @params.statement_text must be provided in a way that allows for the query optimizer to match it with the corresponding statement supplied within the batch or module identified by @module_or_batch and @params. Para obter mais informações, consulte a seção “Comentários”.For more information, see the "Remarks" section. O tamanho de statement_text é limitado apenas pela memória disponível do servidor.The size of statement_text is limited only by available memory of the server.

[@type = ]N'{ OBJECT | SQL | TEMPLATE }'[@type = ]N'{ OBJECT | SQL | TEMPLATE }'
É o tipo de entidade na qual statement_text é exibido.Is the type of entity in which statement_text appears. Especifica o contexto para correspondência de statement_text para plan_guide_name.This specifies the context for matching statement_text to plan_guide_name.

OBJECTOBJECT
Indica que statement_text aparece no contexto de um Transact-SQLTransact-SQL procedimento armazenado, função escalar, função com valor de tabela de várias instruções ou Transact-SQLTransact-SQL gatilho DML no banco de dados atual.Indicates statement_text appears in the context of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger in the current database.

SQLSQL
Indica statement_text aparece no contexto de uma instrução ou um lote autônomo que pode ser enviado SQL ServerSQL Server por meio de qualquer mecanismo.Indicates statement_text appears in the context of a stand-alone statement or batch that can be submitted to SQL ServerSQL Server through any mechanism. Transact-SQLTransact-SQLas instruções enviadas por objetos Common Language Runtime (CLR) ou procedimentos armazenados estendidos, ou usando exec N 'sql_string', são processadas como lotes no servidor e, portanto, devem ser identificadas @como = tipo ' SQL '.statements submitted by common language runtime (CLR) objects or extended stored procedures, or by using EXEC N'sql_string', are processed as batches on the server and, therefore, should be identified as @type = 'SQL'. Se SQL for especificado, a PARAMETRIZAção de dica de consulta {FORCEd | Simple} não pode ser especificado no @parâmetro hints.If SQL is specified, the query hint PARAMETERIZATION { FORCED | SIMPLE } cannot be specified in the @hints parameter.

TEMPLATETEMPLATE
Indica que o guia de plano se aplica a qualquer consulta que parametriza ao formulário indicado em statement_text.Indicates the plan guide applies to any query that parameterizes to the form indicated in statement_text. Se o modelo for especificado, somente a PARAMETRIZAção {FORCEd | Simple} a @dica de consulta pode ser especificada no parâmetro hints.If TEMPLATE is specified, only the PARAMETERIZATION { FORCED | SIMPLE } query hint can be specified in the @hints parameter. Para obter mais informações sobre guias de plano de modelo, consulte especificar comportamento de parametrização de consulta usando guias de plano.For more information about TEMPLATE plan guides, see Specify Query Parameterization Behavior by Using Plan Guides.

[@module_or_batch =] {N ' [ schema_name.[@module_or_batch =]{ N'[ schema_name. ] object_name' | N 'batch_text' | NULO] object_name' | N'batch_text' | NULL }
Especifica o nome do objeto no qual statement_text aparece ou o texto em lotes no qual statement_text é exibido.Specifies either the name of the object in which statement_text appears, or the batch text in which statement_text appears. O texto do lote não pode incluir uma instrução USEDatabase .The batch text cannot include a USEdatabase statement.

Para um guia de plano corresponder a um lote enviado de um aplicativo, batch_text deve ser fornecido no mesmo formato, caractere para caractere, à medida que é enviado para SQL ServerSQL Servero.For a plan guide to match a batch submitted from an application, batch_text must be provided in the same format, character-for-character, as it is submitted to SQL ServerSQL Server. Nenhuma conversão interna é executada para facilitar essa correspondência.No internal conversion is performed to facilitate this match. Para obter mais informações, consulte a seção Comentários.For more information, see the Remarks section.

[schema_name.] object_name especifica o nome de um Transact-SQLTransact-SQL procedimento armazenado, uma função escalar, uma função com valor de tabela de várias Transact-SQLTransact-SQL instruções ou um gatilho DML que contém statement_text.[schema_name.]object_name specifies the name of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger that contains statement_text. Se schema_name não for especificado, schema_name usará o esquema do usuário atual.If schema_name is not specified, schema_name uses the schema of the current user. 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 deve ser NULL.If NULL is specified and @type = 'SQL', the value of @module_or_batch is set to the value of @stmt. If @type = 'TEMPLATE**'**, @module_or_batch must be NULL.

[ @params =] {N ' @parameter_name data_type [, ... n ] ' | NULO[ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
Especifica as definições de todos os parâmetros inseridos em statement_text.Specifies the definitions of all parameters that are embedded in statement_text. @params aplica-se somente quando uma das seguintes opções é verdadeira:@params applies only when either of the following is true:

  • @Type = ' SQL ' ou ' TEMPLATE '.@type = 'SQL' or 'TEMPLATE'. Se "template", @params não devem ser nulos.If 'TEMPLATE', @params must not be NULL.

  • statement_text é enviado usando sp_executesql e um valor para o @parâmetro params é especificado ou SQL ServerSQL Server envia internamente uma instrução após parametrizar.statement_text is submitted by using sp_executesql and a value for the @params parameter is specified, or SQL ServerSQL Server internally submits a statement after parameterizing it. O envio de consultas com parâmetros de APIs de banco de dados (incluindo ODBC, OLE DB e ADO.NET) é exibido para o SQL ServerSQL 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.Submission of parameterized queries from database APIs (including ODBC, OLE DB, and ADO.NET) appear to SQL ServerSQL Server as calls to sp_executesql or to API server cursor routines; therefore, they can also be matched by SQL or TEMPLATE plan guides.

parameter_name data_type deve ser fornecido exatamente com o mesmo formato que é enviado para SQL ServerSQL Server o usando sp_executesql ou enviado internamente após a parametrização. @@parameter_name data_type must be supplied in the exact same format as it is submitted to SQL ServerSQL Server either by using sp_executesql or submitted internally after parameterization. Para obter mais informações, consulte a seção Comentários.For more information, see the Remarks section. Se o lote não contiver parâmetros, NULL deverá ser especificado.If the batch does not contain parameters, NULL must be specified. O tamanho dos @parâmetros é limitado apenas pela memória do servidor disponível.The size of @params is limited only by available server memory.

[@hints = ]{ N'OPTION (query_hint [ , ...n ] )' | N'XML_showplan' | NULL }[@hints = ]{ N'OPTION (query_hint [ ,...n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [ , ...n ] )N'OPTION (query_hint [ ,...n ] )
Especifica uma cláusula OPTION a ser anexada a uma consulta @que corresponda @a stmt. Hints deve ser sintaticamente o mesmo que uma cláusula OPTION em uma instrução SELECT e pode conter qualquer sequência válida de dicas de consulta.Specifies an OPTION clause to attach to a query that matches @stmt. @hints must be syntactically the same as an OPTION clause in a SELECT statement, and can contain any valid sequence of query hints.

N'XML_showplan'N'XML_showplan'
É o plano de consulta em formato XML a ser aplicado como dica.Is the query plan in XML format to be applied as a hint.

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.We recommend assigning the XML Showplan to a variable; otherwise, you must escape any single quotation marks in the Showplan by preceding them with another single quotation mark. Consulte o exemplo E.See example E.

NULLNULL
Indica que qualquer dica existente especificada na cláusula OPTION da consulta não é aplicada à consulta.Indicates that any existing hint specified in the OPTION clause of the query is not applied to the query. Para obter mais informações, consulte cláusula (Option Transact-)SQL.For more information, see OPTION Clause (Transact-SQL).

ComentáriosRemarks

Os argumentos para sp_create_plan_guide devem ser fornecidos na ordem em que aparecem.The arguments to sp_create_plan_guide must be provided in the order that is shown. 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.When you supply values for the parameters of sp_create_plan_guide, all parameter names must be specified explicitly, or none at all. Por exemplo, se @Name = for especificado, então @stmt = , @Type = e assim por diante, também deverá ser especificado.For example, if @name = is specified, then @stmt = , @type =, and so on, must also be specified. Da mesma forma, se @Name = for omitido e apenas o valor do parâmetro for fornecido, os nomes de parâmetro restantes também deverão ser omitidos e apenas seus valores fornecidos.Likewise, if @name = is omitted and only the parameter value is provided, the remaining parameter names must also be omitted, and only their values provided. Os nomes de argumento são usados apenas para fins descritivos, para ajudar compreender a sintaxe.Argument names are for descriptive purposes only, to help understand the syntax. SQL ServerSQL 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.does not verify that the specified parameter name matches the name for the parameter in the position where the name is used.

Você pode criar mais de um guia de plano OBJECT ou SQL para a mesma consulta e lote ou módulo.You can create more than one OBJECT or SQL plan guide for the same query and batch or module. Porém, só um guia de plano pode ser ativado em um determinado momento.However, only one plan guide can be enabled at any given time.

Os guias de plano do tipo objeto não podem ser @criados para um valor module_or_batch que referencie um procedimento armazenado, uma função ou um gatilho DML que especifique a cláusula WITH ENCRYPTION ou que seja temporário.Plan guides of type OBJECT cannot be created for an @module_or_batch value that references a stored procedure, function, or DML trigger that specifies the WITH ENCRYPTION clause or that is temporary.

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.Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error. A tentativa de descartar uma tabela com um gatilho definido nela que é mencionado por um guia de plano também causa um erro.Trying to drop a table that has a trigger defined on it that is referenced by a plan guide also causes an error.

Observação

Os guias de plano não podem ser usados em todas as edições do MicrosoftMicrosoftSQL ServerSQL Server.Plan guides cannot be used in every edition of MicrosoftMicrosoftSQL ServerSQL Server. Para obter uma lista de recursos com suporte nas edições do SQL ServerSQL Server, consulte Recursos com suporte nas edições do SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. As guias de plano são visíveis em qualquer edição.Plan guides are visible in any edition. Também é possível anexar um banco de dados contendo guias de plano a qualquer edição.You can also attach a database that contains plan guides to any edition. Os guias de plano permanecem intactos quando o banco de dados é restaurado ou anexado a uma versão atualizada do SQL ServerSQL Server.Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server. Você deve verificar a finalidade dos guias de plano em cada banco de dados depois de executar uma atualização de servidor.You should verify the desirability of the plan guides in each database after performing a server upgrade.

Guia de plano correspondente a requisitosPlan Guide Matching Requirements

Para guias de plano que @especificam o tipo = ' @SQL ' ou tipo = ' template ' para corresponder com êxito a uma consulta, os valores de batch_text e @parameter_name data_type [, ... n ] deve ser fornecido exatamente com o mesmo formato que suas contrapartes enviadas pelo aplicativo.For plan guides that specify @type = 'SQL' or @type = 'TEMPLATE' to successfully match a query, the values for batch_text and @parameter_name data_type [,...n ] must be provided in exactly the same format as their counterparts submitted by the application. Isso significa você deve fornecer o texto de lote exatamente como o compilador do SQL ServerSQL Server o recebe.This means you must provide the batch text exactly as the SQL ServerSQL Server compiler receives it. Para capturar o lote real e texto de parâmetro, você pode usar o SQL Server ProfilerSQL Server Profiler.To capture the actual batch and parameter text, you can use SQL Server ProfilerSQL Server Profiler. Para obter mais informações, consulte usar SQL Server Profiler para criar e testar guias de plano.For more information, see Use SQL Server Profiler to Create and Test Plan Guides.

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 com o mesmo formato, caractere-para-caractere, à medida que é enviado SQL ServerSQL Serverpara.When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in exactly the same format, character-for-character, as it is submitted to SQL ServerSQL Server. Nenhuma conversão interna é executada para facilitar essa correspondência.No internal conversion is performed to facilitate this match.

Quando SQL ServerSQL Server corresponde ao valor de statement_text para 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 são considerados:When SQL ServerSQL Server matches the value of statement_text to batch_text and @parameter_name data_type [,...n ], or if @type = ' OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:

  • Caracteres de espaço em branco (guias, espaços, retornos de carro ou alimentações de linha) dentro da cadeia de caracteres.White space characters (tabs, spaces, carriage returns, or line feeds) inside the string.

  • Comentários ( -- ou / * ).* /Comments (-- or /* */).

  • Ponto-e-vírgulas à direitaTrailing semicolons

Por exemplo, SQL ServerSQL Server pode corresponder à cadeia de N'SELECT * FROM T WHERE a = 10' caracteres statement_text para o seguinte batch_text:For example, SQL ServerSQL Server can match the statement_text string N'SELECT * FROM T WHERE a = 10' to the following batch_text:

N'SELECT *

FROM T

WHERE a=10'

No entanto, a mesma cadeia de caracteres não seria correspondida a este batch_text:However, the same string would not be matched to this batch_text:

N'SELECT * FROM T WHERE b = 10'

O SQL ServerSQL Server ignora o retorno de carro, a alimentação de linha e caracteres de espaço dentro da primeira consulta.SQL ServerSQL Server ignores the carriage return, line feed, and space characters inside the first query. Na segunda consulta, a sequência WHERE b = 10 é interpretada diferentemente de WHERE a = 10.In the second query, the sequence WHERE b = 10 is interpreted differently from 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.Matching is case- and accent-sensitive (even when the collation of the database is case-insensitive), except in the case of keywords, where case is insensitive. A correspondência não diferencia maiúsculas de minúsculas em formas abreviadas de palavras-chave.Matching is insensitive to shortened forms of keywords. Por exemplo, as palavras-chave EXECUTE, EXEC e execute são consideradas equivalentes.For example, the keywords EXECUTE, EXEC, and execute are considered equivalent.

Efeito do guia de plano no cache do esquemaPlan Guide Effect on the Plan Cache

Criar um guia de plano em um módulo remove o plano de consulta desse módulo do cache do esquema.Creating a plan guide on a module removes the query plan for that module from the plan cache. 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.Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. 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.Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

PermissõesPermissions

A criação de um guia de plano do tipo OBJECT requer a permissão ALTER no objeto mencionado.To create a plan guide of type OBJECT, requires ALTER permission on the referenced object. A criação de um guia de plano do tipo SQL ou TEMPLATE requer a permissão ALTER no banco de dados atual.To create a plan guide of type SQL or TEMPLATE, requires ALTER permission on the current database.

ExemplosExamples

A.A. Criando um guia de plano do tipo OBJECT para uma consulta em um procedimento armazenadoCreating a plan guide of type OBJECT for a query in a stored procedure

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.The following example creates a plan guide that matches a query executed in the context of an application-based stored procedure, and applies the OPTIMIZE FOR hint to the query.

Aqui está o procedimento armazenado:Here is the stored procedure:

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:Here is the plan guide created on the query in the stored procedure:

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.B. Criando um guia de plano do tipo SQL para uma consulta autônomaCreating a plan guide of type SQL for a stand-alone query

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.The following example creates a plan guide to match a query in a batch submitted by an application that uses the sp_executesql system stored procedure.

Este é o lote:Here is the batch:

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:To prevent a parallel execution plan from being generated on this query, create the following plan guide:

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.C. Criando um guia de plano do tipo TEMPLATE para o formulário parametrizado de uma consultaCreating a plan guide of type TEMPLATE for the parameterized form of a query

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 ServerSQL Server para forçar a aplicação de parâmetros da consulta.The following example creates a plan guide that matches any query that parameterizes to a specified form, and directs SQL ServerSQL Server to force parameterization of the query. As duas consultas a seguir são sintaticamente equivalentes, mas só diferem nos valores literais constantes.The following two queries are syntactically equivalent, but differ only in their constant literal values.

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:Here is the plan guide on the parameterized form of the query:

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.In the previous example, the value for the @stmt parameter is the parameterized form of the query. 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 .The only reliable way to obtain this value for use in sp_create_plan_guide is to use the sp_get_query_template system stored procedure. O script a seguir pode ser usado para obter a consulta parametrizada e, em seguida, criar um guia de plano para ela.The following script can be used both to obtain the parameterized query and then create a plan guide on it.

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

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.The value of the constant literals in the @stmt parameter passed to sp_get_query_template might affect the data type that is chosen for the parameter that replaces the literal. Isso afetará a correspondência do guia de plano.This will affect plan guide matching. Pode ser necessário criar mais de um guia de plano para lidar com diferentes intervalos de valores de parâmetros.You may have to create more than one plan guide to handle different parameter value ranges.

D.D. Criando um guia de plano em uma consulta enviada com o uso de uma solicitação de cursor APICreating a plan guide on a query submitted by using an API cursor request

Os guias de plano podem ser correspondentes a consultas enviadas das rotinas de cursor de servidor de API.Plan guides can match queries that are submitted from API server cursor routines. Essas rotinas incluem sp_cursorprepare, sp_cursorprepexec e sp_cursoropen.These routines include sp_cursorprepare, sp_cursorprepexec, and sp_cursoropen. Os aplicativos que usam APIs ADO, OLE DB e ODBC interagem frequentemente com o SQL ServerSQL Server usando cursores de servidor de API.Applications that use the ADO, OLE DB, and ODBC APIs frequently interact with SQL ServerSQL Server by using API server cursors. É possível verificar a chamada das rotinas de cursor de servidor de API nos rastreamentos do SQL Server ProfilerSQL Server Profiler por meio da exibição do evento de rastreamento do profiler RPC:Starting.You can see the invocation of API server cursor routines in SQL Server ProfilerSQL Server Profiler traces by viewing the RPC:Starting profiler trace event.

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:Suppose the following data appears in an RPC:Starting profiler trace event for a query you want to tune with a plan guide:

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.You notice that the plan for the SELECT query in the call to sp_cursorprepexec is using a merge join, but you want to use a hash join. A consulta enviada com o uso de sp_cursorprepexec tem parâmetros, incluindo uma cadeia de caracteres de consulta e outra de parâmetros.The query submitted by using sp_cursorprepexec is parameterized, including both a query string and a parameter string. 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.You can create the following plan guide to change the choice of plan by using the query and parameter strings exactly as they appear, character for character, in the call to 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.Subsequent executions of this query by the application will be affected by this plan guide, and a hash join will be used to process the query.

E.E. Criando um guia de plano por meio da obtenção do plano de execução XML de um plano em cache.Creating a plan guide by obtaining the XML Showplan from a cached plan

O exemplo a seguir cria um guia de plano para uma instrução SQL ad hoc simples.The following example creates a plan guide for a simple ad hoc SQL statement. 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.The desired query plan for this statement is provided in the plan guide by specifying the XML Showplan for the query directly in the @hints parameter. O exemplo executa a instrução SQL primeiro para gerar um plano no cache do esquema.The example first executes the SQL statement to generate a plan in the plan cache. Nesse exemplo, supõe-se que o plano gerado é o desejado e que nenhum ajuste de consulta adicional é necessário.For the purposes of this example, it is assumed that the generated plan is the desired plan and no additional query tuning is required. 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 .The XML Showplan for the query is obtained by querying the sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_text_query_plan dynamic management views and is assigned to the @xml_showplan variable. Em seguida, a variável @xml_showplan é passada à instrução sp_create_plan_guide no parâmetro @hints .The @xml_showplan variable is then passed to the sp_create_plan_guide statement in the @hints parameter. 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 .Or, you can create a plan guide from a query plan in the plan cache by using the sp_create_plan_guide_from_handle stored procedure.

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émSee Also

Guias de plano Plan Guides
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
Mecanismo de Banco de Dados procedimentos (armazenados TRANSACT-SQL) Database Engine Stored Procedures (Transact-SQL)
Procedimentos armazenados do sistema (Transact-SQL) System Stored Procedures (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL) sys.dm_exec_sql_text (Transact-SQL)
exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL) sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL) sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template (Transact-SQL)sp_get_query_template (Transact-SQL)