Guias de planoPlan Guides

Guias de plano permitem otimizar o desempenho das consultas quando você não pode ou não quer alterar diretamente o texto da consulta real no SQL Server 2017SQL Server 2017.Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2017SQL Server 2017. As guias de plano influenciam a otimização das consultas, anexando dicas de consulta ou um plano de consulta fixo.Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. Guias de plano podem ser úteis quando um subconjunto pequeno de consultas em um aplicativo de banco de dados fornecido por um terceiro não estiver executando como esperado.Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected. No guia de plano, especifique a instrução Transact-SQL que deve ser otimizada, e uma cláusula OPTION que contenha as dicas de consulta a serem usadas ou um plano de consulta específico a ser usado para otimizar a consulta.In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. Quando a consulta é feita, o SQL ServerSQL Server corresponde a instrução Transact-SQL com a guia de plano e anexa a cláusula OPTION à consulta em tempo de execução ou usa o plano de consulta especificado.When the query executes, SQL ServerSQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

O número total de guias de plano que é possível criar só está limitado através de recursos do sistema disponíveis.The total number of plan guides you can create is limited only by available system resources. De outro modo, guias de plano devem ser limitados para consultas de missão-crítica que são direcionados para aprimorar ou estabilizar o desempenho.Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. Guias de plano não podem ser usados para influenciar a maioria da carga de consulta de um aplicativo implantado.Plan guides should not be used to influence most of the query load of a deployed application.

Observação

Os guias de plano não podem ser usados em todas as edições do MicrosoftMicrosoft SQL ServerSQL Server.Plan guides cannot be used in every edition of MicrosoftMicrosoft SQL 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.

Tipos de guias de planoTypes of Plan Guides

Podem ser criados os tipos de guias de plano a seguir.The following types of plan guides can be created.

Guia de plano OBJECTOBJECT plan guide

O guia de plano OBJECT corresponde as consultas executadas no contexto dos procedimentos armazenados do Transact-SQLTransact-SQL , funções escalares definidas pelo usuário, funções com valor de tabela de várias instruções definidas pelo usuário e gatilhos DML.An OBJECT plan guide matches queries that execute in the context of Transact-SQLTransact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

Suponha que o procedimento armazenado a seguir, que usa o parâmetro @Country_region, esteja em um aplicativo de banco de dados implantado no banco de dados AdventureWorks2012AdventureWorks2012:Suppose the following stored procedure, which takes the @Country_region parameter, is in a database application that is deployed against the AdventureWorks2012AdventureWorks2012 database:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;  

Suponha que esse procedimento armazenado foi compilado e otimizado para @Country_region = N'AU' (Austrália).Assume that this stored procedure has been compiled and optimized for @Country_region = N'AU' (Australia). Entretanto, já que há relativamente poucas vendas oriundas da Austrália, o desempenho cai quando a consulta é executada com os valores de parâmetro de países com mais pedidos de vendas.However, because there are relatively few sales orders that originate from Australia, performance decreases when the query executes using parameter values of countries with more sales orders. Como a maioria dos pedidos de vendas tem origem nos Estados Unidos, um plano de consulta gerado para @Country_region = N'US' provavelmente teria execução melhor para todos os valores possíveis do parâmetro @Country_region .Because the most sales orders originate in the United States, a query plan that is generated for @Country_region = N'US' would likely perform better for all possible values of the @Country_region parameter.

É possível corrigir esse problema ao modificar o procedimento armazenado para adicionar a dica de consulta OPTIMIZE FOR à consulta.You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. Porém, já que o procedimento armazenado está em um aplicativo implantado, não é possível modificar diretamente o código do aplicativo.However, because the stored procedure is in a deployed application, you cannot directly modify the application code. Ao contrário, é possível criar o guia de plano a seguir no banco de dados AdventureWorks2012AdventureWorks2012 .Instead, you can create the following plan guide in the AdventureWorks2012AdventureWorks2012 database.

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';  

Quando a consulta especificada na instrução sp_create_plan_guide é executada, a consulta é modificada antes da otimização para incluir a cláusula OPTIMIZE FOR (@Country = N''US'') .When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause.

Guia de plano SQLSQL plan guide

O guia de plano SQL correlaciona consultas que são executadas no contexto de instruções e lotes do Transact-SQLTransact-SQL autônomo que não fazem parte de um objeto do banco de dados.An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQLTransact-SQL statements and batches that are not part of a database object. Os guias de plano com base em SQL também podem ser usados para corresponder consultas com parâmetros uma forma especificada.SQL-based plan guides can also be used to match queries that parameterize to a specified form. Os guias de plano SQL se aplicam a instruções e lotes Transact-SQLTransact-SQL autônomos.SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. Frequentemente, essas instruções são submetidas por um aplicativo por meio do procedimento armazenado do sistema sp_executesql .Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. Por exemplo, considere o seguinte lote autônomo:For example, consider the following stand-alone batch:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;  

Para impedir que um plano de execução paralelo seja gerado nessa consulta, crie o guia de plano a seguir e defina a dica de consulta MAXDOP como 1 no parâmetro @hints .To prevent a parallel execution plan from being generated on this query, create the following plan guide and set the MAXDOP query hint to 1 in the @hints parameter.

sp_create_plan_guide   
@name = N'Guide2',   
@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)';  

Importante

Os valores fornecidos para os argumentos @module_or_batch e @params da instrução sp_create_plan guide devem coincidir com o texto correspondente submetido na consulta real.The values that are supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text submitted in the actual query. Para obter mais informações, veja sp_create_plan_guide (Transact-SQL) e Usar o SQL Server Profiler para criar e testar guias de plano.For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

Os guias de plano SQL também podem ser criados em consultas com parâmetros da mesma forma quando a opção de banco de dados PARAMETERIZATION for SET to FORCED ou quando um guia de plano TEMPLATE for criado especificando uma classe de consulta com parâmetros.SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a parameterized class of queries.

guia de plano TEMPLATETEMPLATE plan guide

O guia de plano TEMPLATE corresponde consultas autônomas com parâmetros com uma forma especificada.A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. Esses guias de plano são usados para substituir a opção SET do banco de dados PARAMETERIZATION atual de um banco de dados para a classe de consultas.These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

É possível criar um guia de plano TEMPLATE em qualquer uma das seguintes situações:You can create a TEMPLATE plan guide in either of the following situations:

  • A opção de banco de dados PARAMETERIZATION é SET to FORCED, mas há consultas que devem ser compiladas de acordo com as regras de Parametrização Simples.The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of Simple Parameterization.

  • A opção de banco de dados PARAMETERIZATION é SET to SIMPLE (configuração padrão), mas é preciso que a Parametrização Forçada seja testada em uma classe de consultas.The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want Forced Parameterization to be tried on a class of queries.

Guia de plano correspondente a requisitosPlan Guide Matching Requirements

Guias de plano são aplicados ao banco de dados no qual eles são criados.Plan guides are scoped to the database in which they are created. Portanto, somente os guias de plano presentes no banco de dados se tornam atuais quando uma consulta pode ser combinada com outra.Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. Por exemplo, se AdventureWorks2012AdventureWorks2012 for o banco de dados atual e a consulta seguinte executa:For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

Somente guias de plano do banco de dados AdventureWorks2012AdventureWorks2012 são elegíveis para corresponderem a essa consulta.Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. Porém, se AdventureWorks2012AdventureWorks2012 for o banco de dados atual e as instruções seguintes são executadas:However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

Somente os guias de plano no DB1 são elegíveis para combinar com a consulta porque ela é executada no contexto de DB1.Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

Para guias de plano baseados em SQL ou em MODELO, o SQL ServerSQL Server faz a correspondência dos valores dos argumentos @module_or_batch and @params com uma consulta, comparando os dois valores, caractere por caractere.For SQL- or TEMPLATE-based plan guides, SQL ServerSQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. Isso significa você deve fornecer o texto exatamente como SQL ServerSQL Server recebe no lote atual.This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

Quando @type = 'SQL' e @module_or_batch forem definidos como NULL, o valor @module_or_batch será definido com o valor @stmt. Isso significa que o valor de statement_text deve ser fornecido exatamente no mesmo formato, caractere a caractere, como enviado para o SQL ServerSQL Server.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 the identical 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 um guia de plano normal (SQL ou OBJECT) e um guia de plano de MODELO puderem ser aplicados a uma instrução, somente o guia de plano normal será usado.When both a regular (SQL or OBJECT) plan guide and a TEMPLATE plan guide can apply to a statement, only the regular plan guide will be used.

Observação

O lote que contém a instrução na qual se quer criar o guia de plano não pode conter uma instrução de USE database .The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

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.

TarefaTask TópicoTopic
Descreve como criar um guia de plano.Describes how to create a plan guide. Criar um novo guia de planoCreate a New Plan Guide
Descreve como criar uma guia de plano para consultas parametrizadas.Describes how to create a plan guide for parameterized queries. Criar um guia de plano para consultas parametrizadasCreate a Plan Guide for Parameterized Queries
Descreve como controlar o comportamento de parametrização da consulta usando guias de plano.Describes how to control query parameterization behavior by using plan guides. Especificar comportamento de parametrização de consulta usando guias de planoSpecify Query Parameterization Behavior by Using Plan Guides
Descreve como incluir um plano de consulta fixo em um guia de plano.Describes how to include a fixed query plan in a plan guide. Aplicar um plano de consulta fixo a um guia de planoApply a Fixed Query Plan to a Plan Guide
Descreve como especificar dicas de consulta em um guia de plano.Describes how to specify query hints in a plan guide. Anexar dicas de consulta para um guia de planoAttach Query Hints to a Plan Guide
Descreve como exibir propriedades do guia de plano.Describes how to view plan guide properties. Exibir propriedades de guia de planoView Plan Guide Properties
Descreve como usar o SQL Server Profiler para criar e testar guias de plano.Describes how to use SQL Server Profiler to create and test plan guides. Usar o SQL Server Profiler para criar e testar guias de planoUse SQL Server Profiler to Create and Test Plan Guides
Descreve como validar guias de plano.Describes how to validate plan guides. Validar guias de plano depois da atualizaçãoValidate Plan Guides After Upgrade

Consulte tambémSee Also

sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)sys.fn_validate_plan_guide (Transact-SQL)