Condividi tramite

Guide per i piani

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azuredatabase SQL in Microsoft Fabric

Importante

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, database SQL in Microsoft Fabric, Istanza gestita di SQL di Azure e in SQL Server 2022 (16.x) e versioni successive.

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 suggerimenti 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. Nella guida del piano, si specifica l'istruzione Transact-SQL da ottimizzare e una clausola OPTION che contiene gli hint per la query che si desidera utilizzare o un piano di query specifico 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.

Il numero totale di guide di piano che è possibile creare è limitato solo dalle risorse di sistema disponibili. È comunque consigliabile limitare l'utilizzo delle guide di piano alle query essenziali per cui si intende migliorare o stabilizzare le prestazioni. Le guide di piano non dovrebbero essere utilizzate per influenzare la maggior parte delle 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 utilizzate in tutte le 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 del 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 sia 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.

Potresti affrontare questo problema modificando la procedura memorizzata per aggiungere l'hint OPTIMIZE FOR alla query. 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 al piano SQL corrisponde alle query che vengono eseguite nel contesto di istruzioni Transact-SQL indipendenti e batch che non fanno parte di un oggetto di database. Le guide di piano basate su SQL possono essere utilizzate anche per abbinare query che si parametrizzano in una forma specificata. 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 al piano e impostare l'hint della 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 seguente guida al piano e utilizzare l'indicazione della 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 che si parametrizzano nella stessa forma quando l'opzione di database PARAMETERIZATION è impostata su FORCED, oppure quando si crea una guida di piano di tipo TEMPLATE specificando la parametrizzazione di una classe di query.

TEMPLATE - guida di piano

Una guida di piano di tipo TEMPLATE corrisponde a query indipendenti che vengono parametrizzate in una forma specifica. 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 della guida del piano

Le guide di piano sono limitate al 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 TEMPLATE, SQL Server abbina i valori degli argomenti @module_or_batch e @params per 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 del piano su un modulo rimuove il query plan 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
Descrive come controllare il comportamento di parametrizzazione delle query utilizzando le guide di piano. Specificare il comportamento di parametrizzazione delle query tramite guide di piano d'esecuzione
Viene descritto come includere un piano di query fisso in una guida di piano. Applicare un piano di query fisso a una guida per il piano
Viene descritto come specificare suggerimenti per le query in una guida al piano. Associare gli hint delle query a una guida al 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 dei piani
Descrive come convalidare le guide dei piani. Convalidare le guide dei piani 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)