Guide di pianoPlan Guides

Le guide di piano consentono di ottimizzare le prestazioni delle query quando non è possibile o non si desidera modificare direttamente il testo della query corrente in 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. Le guide di piano influiscono sull'ottimizzazione delle query mediante l'aggiunta di hint per la query o di un piano di query fisso.Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. Le guide di piano risultano utili quando le prestazioni di un piccolo subset di query eseguite su un database di terze parti sono inferiori a quelle previste.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. In una guida di piano, viene specificata l'istruzione Transact-SQL da ottimizzare e la clausola OPTION che contiene gli hint per la query da utilizzare o un piano di query specifico da utilizzare per ottimizzare la query.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 viene eseguita la query, SQL ServerSQL Server associa l'istruzione Transact-SQL alla guida di piano e, in fase di esecuzione, associa la clausola OPTION alla query oppure utilizza il piano di query specificato.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.

Il numero totale di guide di piano che è possibile creare è limitato solo dalle risorse di sistema disponibili.The total number of plan guides you can create is limited only by available system resources. È comunque consigliabile utilizzare le guide di piano per le sole query critiche di cui si desidera migliorare o stabilizzare le prestazioni.Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. Le guide di piano non vanno utilizzate per modificare la maggior parte del carico di query di un'applicazione distribuita.Plan guides should not be used to influence most of the query load of a deployed application.

Nota

Le guide di piano sono supportate solo in alcune edizioni di MicrosoftMicrosoft SQL ServerSQL Server.Plan guides cannot be used in every edition of MicrosoftMicrosoft SQL ServerSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL ServerSQL Server, vedere Funzionalità supportate dalle edizioni di 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. Le guide di piano sono visibili in qualsiasi edizione.Plan guides are visible in any edition. È inoltre possibile collegare un database che contiene guide di piano a qualsiasi edizione.You can also attach a database that contains plan guides to any edition. Quando si ripristina o si collega un database a una versione aggiornata di SQL ServerSQL Server, le guide di piano non vengono modificate.Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server.

Tipi di guide di pianoTypes of Plan Guides

È possibile creare i seguenti tipi di guide di piano:The following types of plan guides can be created.

guida di piano di tipo OBJECTOBJECT plan guide

Una guida di piano di tipo OBJECT corrisponde alle query eseguite nel contesto di stored procedure Transact-SQLTransact-SQL , funzioni scalari definite dall'utente, funzioni con valori di tabella definite dall'utente con istruzioni multiple e trigger 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.

Si supponga che la stored procedure seguente, che accetta il parametro @Country_region, si trovi in un'applicazione di database distribuita sul database 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;  

Si supponga che questa stored procedure è stata compilata e ottimizzata per @Country_region = N'AU' (Australia).Assume that this stored procedure has been compiled and optimized for @Country_region = N'AU' (Australia). Tuttavia, poiché sono presenti relativamente pochi ordini di vendita con origine in Australia, le prestazioni diminuiscono quando viene eseguita la query utilizzando i valori del parametro dei paesi con più ordini di vendita.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. Poiché la maggior parte degli ordini di vendita proviene dagli Stati Uniti, un piano di query generato per @Country_region = N'US' offrirebbe probabilmente prestazioni migliori per tutti i possibili valori del parametro @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.

Per risolvere il problema è possibile modificare la stored procedure aggiungendo alla query l'hint OPTIMIZE FOR .You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. Poiché la stored procedure si trova in un'applicazione distribuita, non è possibile modificare direttamente il codice dell'applicazione.However, because the stored procedure is in a deployed application, you cannot directly modify the application code. È invece possibile creare la guida di piano seguente nel database 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''))';  

Al momento dell'esecuzione della query specificata nell'istruzione sp_create_plan_guide , la query viene modificata prima dell'ottimizzazione per includere la clausola 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.

Guida di piano di tipo SQLSQL plan guide

Una guida di piano di tipo SQL corrisponde alle query eseguite nel contesto di batch e istruzioni Transact-SQLTransact-SQL autonome che non fanno parte di un oggetto di database.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. Le guide di piano basate su SQL possono inoltre essere utilizzate per query con parametrizzazioni specifiche.SQL-based plan guides can also be used to match queries that parameterize to a specified form. Le guide di piano di tipo SQL vengono applicate a istruzioni Transact-SQLTransact-SQL autonome e batch.SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. Spesso tali istruzioni vengono inoltrate da un'applicazione mediante la stored procedure di sistema sp_executesql .Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. Ad esempio, si consideri il batch autonomo seguente:For example, consider the following stand-alone batch:

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

Per impedire la generazione di un piano di esecuzione parallelo su questa query, creare la seguente guida di piano e impostare l'hint per la query MAXDOP su 1 nel parametro @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

I valori specificati per gli argomenti @module_or_batch e @params dell'istruzione sp_create_plan guide devono corrispondere al testo specificato nella query effettiva.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. Per altre informazioni, vedere sp_create_plan_guide (Transact-SQL) e Usare SQL Server Profiler per creare e testare guide di piano.For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

È possibile creare guide di piano SQL anche per le query con parametrizzazione forzata quando l'opzione di database PARAMETERIZATION è impostata su FORCED, oppure quando si crea una guida di piano di tipo TEMPLATE per specificare la parametrizzazione di una classe di query.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.

TEMPLATE - guida di pianoTEMPLATE plan guide

Una guida di piano di tipo TEMPLATE corrisponde alle query autonome con parametrizzazioni specifiche.A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. Tali guide di piano vengono utilizzate per sostituire l'opzione SET di database PARAMETERIZATION di un database per una classe di query.These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

È possibile creare una guida di piano di tipo TEMPLATE nelle seguenti situazioni:You can create a TEMPLATE plan guide in either of the following situations:

  • L'opzione di database PARAMETERIZATION è impostata su FORCED, ma si vogliono compilare alcune query in base alle regole della parametrizzazione semplice.The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of Simple Parameterization.

  • L'opzione di database PARAMETERIZATION è impostata su SIMPLE (impostazione predefinita), ma si vuole che una classe di query venga sottoposta a parametrizzazione forzata.The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want Forced Parameterization to be tried on a class of queries.

Requisiti di corrispondenza per la guida di pianoPlan Guide Matching Requirements

Le guide di piano sono definite a livello di ambito del database in cui vengono create.Plan guides are scoped to the database in which they are created. Pertanto, è possibile far corrispondere alla query solo le guide di piano presenti nel database corrente al momento dell'esecuzione della query.Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. Ad esempio, se AdventureWorks2012AdventureWorks2012 è il database corrente e viene eseguita la query seguente:For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

È possibile far corrispondere alla query solo le guide di piano nel database AdventureWorks2012AdventureWorks2012 .Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. Se tuttavia il database corrente è AdventureWorks2012AdventureWorks2012 e vengono eseguite le istruzioni seguenti:However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

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

È possibile far corrispondere alla query solo le guide di piano in DB1 , poiché la query è in esecuzione nel contesto di DB1.Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

Per guide di piano basate su SQL o TEMPLATE, SQL ServerSQL Server esegue la corrispondenza tra i valori per gli argomenti @module_or_batch e @params e una query, confrontando i due valori carattere per carattere.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. Per questo motivo è necessario immettere il testo esattamente come SQL ServerSQL Server lo riceve nel batch.This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

Se @type = 'SQL' e @module_or_batch vengono impostate su NULL, il valore di @module_or_batch viene impostato sul valore di @stmt. Di conseguenza, il valore per statement_text deve essere specificato nello stesso formato, carattere per carattere, così come viene inviato a 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. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna.No internal conversion is performed to facilitate this match.

Quando è possibile applicare sia una guida di piano normale (SQL o OBJECT) sia una guida di piano TEMPLATE a un'istruzione, verrà utilizzata solo la guida di piano normale.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.

Nota

Il batch contenente l'istruzione sulla quale si desidera creare una guida di piano non può contenere un'istruzione USE database .The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

Effetto delle guide di piano sulla cache dei pianiPlan Guide Effect on the Plan Cache

La creazione di una guida di piano su un modulo rimuove il piano di query per il dato modulo dalla cache dei piani.Creating a plan guide on a module removes the query plan for that module from the plan cache. La creazione di una guida di piano di tipo OBJECT o SQL su un batch rimuove il piano di query per un batch con lo stesso valore 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. La creazione di una guida di piano di tipo TEMPLATE rimuove tutti i batch a istruzione singola dalla cache dei piani all'interno del database.Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

AttivitàTask ArgomentoTopic
Viene descritto come creare una guida di piano.Describes how to create a plan guide. Creare una nuova guida di pianoCreate a New Plan Guide
Viene descritto come creare una guida di piano per le query con parametri.Describes how to create a plan guide for parameterized queries. Creare una guida di piano per le query con parametriCreate a Plan Guide for Parameterized Queries
Viene descritto come controllare il comportamento di parametrizzazione delle query utilizzando guide di piano.Describes how to control query parameterization behavior by using plan guides. Definizione delle funzionalità di parametrizzazione delle query tramite guide di pianoSpecify Query Parameterization Behavior by Using Plan Guides
Viene descritto come includere un piano di query fisso in una guida di piano.Describes how to include a fixed query plan in a plan guide. Applicare un piano di query fisso a una guida di pianoApply a Fixed Query Plan to a Plan Guide
Viene descritto come specificare hint per la query in una guida di piano.Describes how to specify query hints in a plan guide. Associazione degli hint per le query a una guida di pianoAttach Query Hints to a Plan Guide
Viene descritto come visualizzare le proprietà di una guida di piano.Describes how to view plan guide properties. Visualizzare le proprietà delle guide di pianoView Plan Guide Properties
Viene descritto come utilizzare SQL Server Profiler per creare e testare guide di piano.Describes how to use SQL Server Profiler to create and test plan guides. Usare SQL Server Profiler per creare e testare guide di pianoUse SQL Server Profiler to Create and Test Plan Guides
Viene descritto come convalidare una guida di piano.Describes how to validate plan guides. Convalidare le guide di piano dopo l'aggiornamentoValidate Plan Guides After Upgrade

Vedere ancheSee 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)