Guide di piano

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

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. Le guide di piano influiscono sull'ottimizzazione delle query mediante l'aggiunta di hint per la query o di un piano di query fisso. 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. 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. Quando viene eseguita la query, SQL Server associa l'istruzione Transact-SQL alle guide di piano e, in fase di esecuzione, associa la clausola OPTION alla query oppure utilizza il piano di query specificato. Poiché Query Optimizer di SQL Server in genere seleziona il piano di esecuzione ottimale per una query, è consigliabile usare le guide di piano solo come ultima risorsa e sempre da parte di sviluppatori e amministratori esperti di database.

Nota

Gli hint di Query Store in Database SQL di Azure offrono un metodo semplice per modellare i piani di query senza cambiare il codice dell'applicazione. Gli hint di Query Store sono più semplici rispetto alle guide di piano. Gli hint di Query Store sono disponibili in Database SQL di Azure e Istanza gestita di SQL di Azure e successivamente in SQL Server 2022 (16.x).

Il numero totale di guide di piano che è possibile creare è limitato solo dalle risorse di sistema disponibili. È comunque consigliabile utilizzare le guide di piano per le sole query critiche di cui si desidera migliorare o stabilizzare le prestazioni. Le guide di piano non vanno utilizzate per modificare la maggior parte del carico di query di un'applicazione distribuita.

Il piano di esecuzione risultante forzato da questa funzionalità sarà identico o simile al piano da forzare. Poiché il piano risultante potrebbe non essere identico al piano specificato dalla guida di piano, le prestazioni dei piani possono variare. In rari casi, la differenza di prestazioni può essere significativa e negativa; in tal caso, l'amministratore deve rimuovere il piano forzato.

Le guide di piano sono supportate solo in alcune edizioni di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2016. Le guide di piano sono visibili in qualsiasi edizione. È inoltre possibile collegare un database che contiene guide di piano a qualsiasi edizione. Quando si ripristina o si collega un database a una versione aggiornata di SQL Server, le guide di piano non vengono modificate.

Tipi di guide di piano

È possibile creare i seguenti tipi di guide di piano:

guida di piano di tipo OBJECT

Una guida di piano di tipo OBJECT corrisponde alle query eseguite nel contesto di stored procedure di Transact-SQL, funzioni scalari definite dall'utente, funzioni con valori di tabella definite dall'utente con istruzioni multiple e trigger DML.

Si supponga che la stored procedure seguente, che accetta il parametro @Country_region, si trovi in un'applicazione di database distribuita sul database AdventureWorks2022:

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). 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/regioni con più ordini di vendita. 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.

Per risolvere il problema è possibile modificare la stored procedure aggiungendo alla query l'hint OPTIMIZE FOR . Poiché la stored procedure si trova in un'applicazione distribuita, non è possibile modificare direttamente il codice dell'applicazione. È invece possibile creare la guida di piano seguente nel database AdventureWorks2022 .

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

Guida di piano di tipo SQL

Una guida di piano di tipo SQL corrisponde alle query eseguite nel contesto di batch e istruzioni Transact-SQL autonome che non fanno parte di un oggetto di database. Le guide di piano basate su SQL possono inoltre essere utilizzate per query con parametrizzazioni specifiche. Le guide di piano di tipo SQL vengono applicate a istruzioni Transact-SQL autonome e batch. Spesso tali istruzioni vengono inoltrate da un'applicazione mediante la stored procedure di sistema sp_executesql . Ad esempio, si consideri il batch autonomo seguente:

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 .

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

Un altro esempio è la seguente istruzione SQL inviata usando sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Per creare un piano univoco per ogni esecuzione di questa query, creare la guida di piano seguente e usare l'hint per la query OPTION (RECOMPILE) nel parametro @hints.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

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. Per altre informazioni, vedere sp_create_plan_guide (Transact-SQL) e Usare SQL Server Profiler per creare e testare guide di piano.

È 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.

TEMPLATE - guida di piano

Una guida di piano di tipo TEMPLATE corrisponde alle query autonome con parametrizzazioni specifiche. Tali guide di piano vengono utilizzate per sostituire l'opzione SET di database PARAMETERIZATION di un database per una classe di query.

È possibile creare una guida di piano di tipo TEMPLATE nelle seguenti situazioni:

  • L'opzione di database PARAMETERIZATION è impostata su FORCED, ma si vogliono compilare alcune query in base alle regole della parametrizzazione semplice.

  • L'opzione di database PARAMETERIZATION è impostata su SIMPLE (impostazione predefinita), ma si vuole che una classe di query venga sottoposta a parametrizzazione forzata.

Requisiti di corrispondenza per la guida di piano

Le guide di piano sono definite a livello di ambito del database in cui vengono create. Pertanto, è possibile far corrispondere alla query solo le guide di piano presenti nel database corrente al momento dell'esecuzione della query. Ad esempio, se AdventureWorks2022 è il database corrente e viene eseguita la query seguente:

SELECT FirstName, LastName FROM Person.Person;

È possibile far corrispondere alla query solo le guide di piano nel database AdventureWorks2022 . Se tuttavia il database corrente è AdventureWorks2022 e vengono eseguite le istruzioni seguenti:

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.

Per guide di piano basate su SQL o TEMPLATE, SQL 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. Per questo motivo è necessario immettere il testo esattamente come SQL Server lo riceve nel batch.

Quando @type = 'SQL' e @module_or_batch è impostato su NULL, il valore di @module_or_batch viene impostato sul valore di @stmt. Ciò indica che il valore per statement_text deve essere fornito nello stesso formato, carattere per carattere, per essere inviato a SQL Server. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna.

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.

Nota

Il batch contenente l'istruzione sulla quale si desidera creare una guida di piano non può contenere un'istruzione USE database .

Effetto delle guide di piano sulla cache dei piani

La creazione di una guida di piano su un modulo rimuove il piano di query per il dato modulo dalla cache dei piani. 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. 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.

Attività Argomento
Viene descritto come creare una guida di piano. Creare una nuova guida di piano
Viene descritto come creare una guida di piano per le query con parametri. Creare una guida di piano per le query con parametri
Viene descritto come controllare il comportamento di parametrizzazione delle query utilizzando guide di piano. Specificare il comportamento di parametrizzazione delle query tramite guide di piano
Viene descritto come includere un piano di query fisso in una guida di piano. Applicare un piano di query fisso a una guida di piano
Viene descritto come specificare hint per la query in una guida di piano. Associare gli hint per le query a una guida di piano
Viene descritto come visualizzare le proprietà di una guida di piano. Visualizzare le proprietà delle guide di piano
Viene descritto come utilizzare SQL Server Profiler per creare e testare guide di piano. Usare SQL Server Profiler per creare e testare guide di piano
Viene descritto come convalidare una guida di piano. Convalidare le guide di piano dopo l'aggiornamento

Vedi anche

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