Guide di piano

Si applica a:SQL ServerDatabase SQL diAzure Istanza gestita di SQL di Azure

Le guide di piano consentono di ottimizzare le prestazioni delle query quando non è possibile o non si vuole modificare direttamente il testo della query effettiva 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 la query viene eseguita, SQL Server corrisponde all'istruzione Transact-SQL alla guida di piano e associa la clausola OPTION alla query in fase di esecuzione o usa il piano di query specificato. Poiché Query Optimizer di SQL Server seleziona in genere il piano di esecuzione migliore per una query, è consigliabile usare solo le guide di piano come ultima risorsa per sviluppatori esperti e amministratori di database.

Nota

Gli hint di Query Store offrono un metodo più semplice da usare per modellare i piani di query senza modificare il codice dell'applicazione. Gli hint di Query Store sono più semplici rispetto alle guide di piano. Gli hint di Query Store sono disponibili nel database SQL di Azure e nell'istanza gestita di SQL di Azure e in SQL Server 2022 (16.x) e versioni successive.

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 non possono essere usate in ogni edizione 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. Le guide di piano rimangono intatte quando si ripristina o si collega un database a una versione aggiornata di SQL Server.

Tipi di guide di piano

È possibile creare i seguenti tipi di guide di piano:

guida di piano di tipo OBJECT

Una guida di piano OBJECT corrisponde alle query eseguite nel contesto di stored procedure Transact-SQL, funzioni scalari definite dall'utente, funzioni con valori di tabella a più istruzioni definite dall'utente 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é esistono relativamente pochi ordini di vendita provenienti dall'Australia, le prestazioni diminuiscono quando la query viene eseguita usando i valori dei parametri di paesi/aree geografiche 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 SQL corrisponde alle query eseguite nel contesto di istruzioni Transact-SQL autonome e batch 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 SQL si applicano alle istruzioni Transact-SQL autonome e ai 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 le guide di piano basate su SQL o MODELLO, SQL Server corrisponde ai valori per gli @module_or_batch argomenti e @params a una query confrontando i due valori carattere per carattere. Ciò significa che è necessario specificare il testo esattamente come SQL Server lo riceve nel batch effettivo.

Quando @type = 'SQL' e @module_or_batch è impostato su NULL, il valore di @module_or_batch viene impostato sul valore di @stmt. Ciò significa che il valore per statement_text deve essere fornito nel formato identico, carattere per carattere, perché viene 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)