Feedback della concessione di memoria

Si applica a: SQL Server 2017 (14.x) e versioni successive, Database SQL di Azure, Istanza gestita di SQL di Azure

A volte una query viene eseguita con una concessione di memoria troppo grande o troppo piccola. Se la concessione di memoria è troppo grande, viene ostacolato il parallelismo nel server. Se la concessione di memoria è troppo piccola, è possibile eseguire lo spill su disco, ovvero un'operazione costosa. Il feedback delle concessioni di memoria prova a ricordare le esigenze di memoria di un'esecuzione precedente (con feedback percentile, varie esecuzioni precedenti). In base a queste informazioni sulle query cronologiche, il feedback delle concessioni di memoria regola quindi la concessione assegnata alla query per le esecuzioni successive.

Questa funzionalità è stata rilasciata in tre parti. Il feedback delle concessioni di memoria in modalità batch, seguito dal feedback delle concessioni di memoria in modalità riga e da SQL Server 2022 (16.x) è stato introdotto il feedback delle concessioni di memoria su disco usando Query Store e un algoritmo migliorato noto come concessione percentile.

Nota

Per altre funzionalità di feedback sulle query, vedere Feedback sulla stima della cardinalità (CE) e Feedback sul grado di parallelismo (DOP).

Feedback delle concessioni di memoria in modalità batch

Si applica a: SQL Server, a partire da SQL Server 2017 (14.x) e database SQL di Azure

Un piano post esecuzione di una query include la memoria minima richiesta per l'esecuzione e la dimensione della concessione di memoria sufficiente a far sì che tutte le righe siano incluse nella memoria. Se le dimensioni della concessione di memoria non vengono impostate correttamente le prestazioni possono risultare ridotte. Le concessioni di dimensioni eccessive causano memoria non usata e riduzione della concorrenza. Le concessioni di memoria di dimensioni insufficienti causano costose distribuzioni su disco. Incentrandosi sui carichi di lavoro ripetuti, il feedback delle concessioni di memoria in modalità batch ricalcola la memoria effettiva necessaria per una query, quindi aggiorna il valore della concessione per il piano nella cache. Quando viene eseguita un'istruzione query identica la query usa le dimensioni della concessione di memoria aggiornate, riducendo il numero eccessivo di concessioni che limita la concorrenza e correggendo il numero insufficiente di concessioni che causa costose distribuzioni su disco.

Il grafico seguente visualizza un esempio dell'uso del feedback delle concessioni di memoria in modalità batch. La durata della prima esecuzione della query è pari a 88 secondi a causa del numero elevato di distribuzioni:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Con il feedback delle concessioni di memoria attivato la durata della seconda esecuzione della query si riduce a 1 secondo (da 88 secondi), le distribuzioni su disco vengono rimosse completamente e la concessione è superiore:

Dimensionamento tramite il feedback delle concessioni di memoria

Per una condizione di concessione di memoria di dimensioni eccessive, se la memoria concessa supera di oltre due volte la quantità di memoria realmente usata, il feedback delle concessioni di memoria ricalcola la concessione e aggiorna il piano memorizzato nella cache. I piani con concessioni di memoria di dimensioni inferiori a 1 MB non vengono ricalcolati per le eccedenze.

Per una condizione di concessione di memoria di dimensioni insufficienti che genera distribuzioni su disco per gli operatori in modalità batch, il feedback delle concessioni di memoria attiverà il ricalcolo della concessione di memoria. Gli eventi di distribuzione vengono segnalati al feedback della concessione di memoria e possono essere esplorati tramite l'evento esteso spilling_report_to_memory_grant_feedback. Questo evento restituisce l'ID del nodo dal piano e il volume dei dati distribuiti su disco da tale nodo.

La concessione di memoria modificata viene visualizzata nel piano effettivo (post-esecuzione) tramite la proprietà GrantedMemory.

È possibile visualizzare questa proprietà nell'operatore radice dell'output grafico showplan o nell'output XML showplan:

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

Per fare in modo che i carichi di lavoro siano automaticamente idonei per questo miglioramento, abilitare il livello di compatibilità 140 per il database.

Esempio:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Feedback delle concessioni di memoria e scenari dipendenti dai parametri

Per risultati ottimali, valori dei parametri diversi potrebbero richiedere piani di query diversi. Le query di questo tipo sono definite "sensibili ai parametri".

Per i piani sensibili ai parametri il feedback delle concessioni di memoria si disattiva quando una query registra requisiti di memoria non stabili. La funzionalità feedback delle concessioni di memoria viene disattivata dopo varie ripetizioni dell'esecuzione della query e la disattivazione può essere rilevata monitorando l'evento esteso memory_grant_feedback_loop_disabled. Questa condizione viene mitigata con la modalità di persistenza e percentile per il feedback delle concessioni di memoria introdotta in SQL Server 2022 (16.x). La funzionalità di persistenza del feedback delle concessioni di memoria richiede l'abilitazione di Query Store nel database e la modalità "lettura/scrittura".

Per altre informazioni su sniffing e sensibilità dei parametri, consultare la Guida sull'architettura di elaborazione delle query.

Memorizzazione nella cache del feedback delle concessioni di memoria

Il feedback può essere archiviato nel piano memorizzato nella cache per una singola esecuzione. Tuttavia i vantaggi del feedback delle concessioni di memoria appaiono in caso di esecuzioni consecutive dell'istruzione. Questa funzionalità si applica all'esecuzione ripetuta di istruzioni. Il feedback delle concessioni di memoria modifica solo il piano memorizzato nella cache. Le modifiche non sono state acquisite in Query Store prima di SQL Server 2022 (16.x).

Se il piano viene rimosso dalla cache il feedback non viene mantenuto. Il feedback va perduto anche nel caso di un failover. Un'istruzione che usa OPTION (RECOMPILE) crea un nuovo piano e non lo memorizza nella cache. Dato che il piano non è memorizzato nella cache, il feedback delle concessioni di memoria non viene generato e non viene archiviato per la compilazione e l'esecuzione. Se tuttavia un'istruzione equivalente (con lo stesso hash di query) che non ha usato OPTION (RECOMPILE) è stata memorizzata nella cache e quindi rieseguita, le successive istruzioni consecutive possono trarre vantaggio dal feedback delle concessioni di memoria.

Rilevare le attività di feedback delle concessioni di memoria

È possibile tenere traccia degli eventi di feedback delle concessioni di memoria usando l'evento esteso memory_grant_updated_by_feedback. Questo evento rileva la cronologia del conteggio di esecuzione corrente, il numero di volte per il quale il piano è stato aggiornato dal feedback delle concessioni di memoria, la concessione di memoria aggiuntiva ideale prima della modifica e la concessione di memoria aggiuntiva ideale dopo che il feedback delle concessioni di memoria ha modificato il piano salvato nella cache.

Feedback delle concessioni di memoria, Resource Governor e hint per la query

La memoria concessa reale è conforme al limite di memoria per le query determinato da Resource Governor o dall'hint per la query.

Disabilitare il feedback delle concessioni di memoria in modalità batch senza modificare il livello di compatibilità

È possibile disabilitare i commenti della concessione di memoria nell'ambito del database o dell'istruzione mantenendo comunque la compatibilità sul livello 140 o superiore. Per disabilitare i feedback della concessione di memoria in modalità batch per tutte le esecuzioni di query provenienti dal database, eseguire le istruzioni SQL successive all'interno del contesto del database applicabile:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Quando è abilitata, questa impostazione viene visualizzata come abilitata in sys.database_scoped_configurations.

Per abilitare nuovamente i feedback della concessione di memoria in modalità batch per tutte le esecuzioni di query provenienti dal database, eseguire le istruzioni SQL all'interno del contesto del database applicabile:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

È anche possibile disabilitare i commenti sulla concessione di memoria in modalità batch per una query specifica definendo DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK come hint per la query USE HINT. Ad esempio:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

L'hint per la query USE HINT ha la precedenza rispetto una configurazione con ambito database o un'impostazione del flag di traccia.

Feedback delle concessioni di memoria in modalità riga

Si applica a: SQL Server, a partire da SQL Server 2019 (15.x) e database SQL di Azure

Il feedback delle concessioni di memoria in modalità riga estende la funzionalità di feedback delle concessioni di memoria in modalità batch adattando le dimensioni delle concessioni di memoria sia per gli operatori in modalità batch che per quelli in modalità riga.

Per abilitare il feedback della concessione di memoria in modalità riga in Database SQL di Azure, attivare il livello di compatibilità del database 150 o superiore per il database a cui si è connessi quando si esegue la query.

Esempio:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

Come per il feedback della concessione di memoria in modalità batch, l'attività di feedback della concessione di memoria in modalità riga è visibile attraverso XEvent memory_grant_updated_by_feedback. Vengono inoltre introdotti due nuovi attributi del piano di esecuzione delle query per una migliore visibilità sullo stato corrente di un'operazione di feedback delle concessioni di memoria per la modalità riga e batch.

I feedback delle concessioni di memoria non richiedono Query Store. Tuttavia, i miglioramenti della persistenza introdotti in SQL Server 2022 (16.x) richiedono che il Query Store sia abilitato per il database e in uno stato “lettura/scrittura”. Per altre informazioni sulla persistenza, vedere successivamente in questo articolo Feedback sulle concessioni di memoria in modalità percentile e persistenza.

L'attività di feedback delle concessioni di memoria in modalità riga è visualizzabile attraverso l'evento esteso memory_grant_updated_by_feedback.

A partire dal feedback della concessione di memoria in modalità riga, vengono mostrati due nuovi attributi del piano di query per i piani effettivi post-esecuzione: IsMemoryGrantFeedbackAdjusted e LastRequestedMemory, che vengono aggiunti all'elemento XML del piano di query MemoryGrantInfo.

• L’attributo LastRequestedMemory mostra la memoria concessa in Kilobyte (KB) dall'esecuzione della query precedente.
• L'attributo IsMemoryGrantFeedbackAdjusted consente di controllare lo stato del feedback della concessione di memoria per l'istruzione all'interno di un piano di esecuzione query effettivo.

I valori esposti in questo attributo sono i seguenti:

IsMemoryGrantFeedbackAdjusted Valore	Descrizione
No: First Execution	Il feedback delle concessioni di memoria non regola la memoria per la prima compilazione e l'esecuzione associata.
No: Accurate Grant	In assenza di spill su disco e se l'istruzione usa almeno il 50% della memoria concessa, il feedback delle concessioni di memoria non viene attivato.
No: Feedback disabled	Se il feedback delle concessioni di memoria viene attivato continuamente e alterna tra operazioni di aumento della memoria e riduzione della memoria, il motore di database disabiliterà il feedback delle concessioni di memoria per l'istruzione.
Yes: Adjusting	Il feedback delle concessioni di memoria è stato applicato e potrebbe essere ulteriormente regolato per l'esecuzione successiva.
Sì: Percentile Adjusting	Il feedback delle concessioni di memoria viene applicato usando l'algoritmo di concessione percentile che esamina maggiormente la cronologia rispetto alla sola esecuzione più recente.
Yes: Stable	Il feedback delle concessioni di memoria è stato applicato e la memoria concessa è ora stabile, ovvero quella concessa per l'esecuzione precedente è uguale a quella concessa per l'esecuzione corrente.

Feedback delle concessioni di memoria in modalità percentile e persistenza

Si applica a: SQL Server, a partire da SQL Server 2022 (16.x) e database SQL di Azure

Questa funzionalità è stata introdotta in SQL Server 2022 (16.x), tuttavia questo miglioramento delle prestazioni è disponibile per le query che operano nel livello di compatibilità del database 140 (introdotto in SQL Server 2017) o versione successiva oppure l'hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 140 e versioni successive e quando Query Store è abilitato per il database ed è in uno stato di "lettura/scrittura".

• Il feedback delle concessioni di memoria percentile è abilitato per impostazione predefinita in SQL Server 2022 (16.x), ma non ha alcun effetto se Query Store è disabilitato o in uno stato differente da quello di "lettura/scrittura".
• La persistenza per le concessioni di memoria, il feedback su stima di cardinalità (CE) e grado di parallelismo (DOP) sono attivati per impostazione predefinita in SQL Server 2022 (16.x), ma non hanno alcun effetto quando Query Store è disabilitato o quando è in uno stato differente da quello di "lettura/scrittura".
• Percentile e persistenza per il feedback delle concessioni di memoria sono disponibili in database SQL di Azure e abilitati per impostazione predefinita in tutti i database, sia esistenti che nuovi.
• Percentile e persistenza per il feedback delle concessioni di memoria non sono attualmente disponibili in Istanza gestita di SQL di Azure.

È consigliabile avere una base di riferimento delle prestazioni per il carico di lavoro prima che la funzionalità sia abilitata per il database. I numeri di riferimento consentono di determinare se il vantaggio previsto si ottiene dalla funzionalità.

Il feedback della concessione di memoria (MGF) è una funzionalità esistente che regola le dimensioni della memoria allocata per una query sulla base delle prestazioni precedenti. Tuttavia, le fasi iniziali di questo progetto archiviavano solo la rettifica della concessione di memoria con il piano nella cache. Se un piano viene rimosso dalla cache, è necessario riniziare il processo di feedback con conseguenti scarse prestazioni nelle prime volte in cui una query viene rieseguita. La nuova soluzione consiste nel rendere persistenti le informazioni sulle concessioni con le altre informazioni sulle query in Query Store, per estendere la durata dei vantaggi quando la cache viene eliminata. La persistenza e il percentile delle concessioni di memoria consentono di risolvere le limitazioni esistenti del feedback delle concessioni di memoria in modo non intrusivo.

Inoltre, le rettifiche delle dimensioni delle concessioni si riferiscono solo alla concessione usata più di recente. Quindi, se una query o un carico di lavoro con parametri richiede dimensioni di concessione di memoria significativamente variabili per ogni esecuzione, le informazioni di concessione più recenti potrebbero non essere accurate. Potrebbero essere differenti dalle esigenze effettive della query in esecuzione. Il feedback delle concessioni di memoria in questo scenario non è utile per le prestazioni poiché la memoria è in costante aggiornamento in base all'ultimo valore di concessione usato. L'immagine successiva mostra il comportamento possibile con il feedback delle concessioni di memoria senza la modalità percentile e di persistenza.

Come si può notare, in questo comportamento insolito ma possibile di query, l'oscillazione tra le quantità di memoria effettive necessarie e concesse comporta uno spreco di memoria e una memoria insufficiente se l'esecuzione della query si alterna in termini di quantità di memoria. In questo scenario, il feedback delle concessioni di memoria si disabilita, riconoscendo che sta facendo più male che bene.

Usando un calcolo basato su percentile sulla cronologia recente della query, anziché semplicemente l'ultima esecuzione, è possibile uniformare i valori delle dimensioni delle concessioni in base alla cronologia di utilizzo dell'esecuzione precedente e provare a ottimizzare per ridurre al minimo lo spill. Ad esempio, lo stesso carico di lavoro alternato visualizzerà il comportamento di concessione di memoria seguente:

Query Optimizer usa un percentile elevato di requisiti di dimensionamento delle concessioni di memoria precedenti per le esecuzioni del piano memorizzato nella cache per calcolare le dimensioni delle concessioni di memoria, usando i dati salvati in modo permanente in Query Store. La regolazione percentile, che regolerà la concessione di memoria, si basa sulla cronologia recente delle esecuzioni. Nel corso del tempo, la concessione di memoria specificata riduce lo spill e la memoria sprecata.

La persistenza si applica anche a Feedback sul grado di parallelismo e Feedback sulla stima di cardinalità.

Abilitare e disabilitare le funzionalità di feedback delle concessioni di memoria

Disabilitare il feedback delle concessioni di memoria in modalità riga senza modificare il livello di compatibilità

È possibile disabilitare il feedback delle concessioni di memoria in modalità riga nell'ambito del database o dell'istruzione mantenendo comunque la compatibilità sul livello 150 o superiore. Per disabilitare il feedback delle concessioni di memoria in modalità riga per tutte le esecuzioni di query provenienti dal database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Per riabilitare il feedback delle concessioni di memoria in modalità riga per tutte le esecuzioni di query provenienti dal database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

È anche possibile disabilitare i commenti sulla concessione di memoria in modalità riga per una query specifica definendo DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK come hint per la query USE HINT. Ad esempio:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

L'hint per la query USE HINT ha la precedenza rispetto una configurazione con ambito database o un'impostazione del flag di traccia.

Abilitare la persistenza e il percentile delle concessioni di memoria

Il feedback persistenza e percentile sono abilitati per impostazione predefinita in Database SQL di Azure e SQL Server 2022 (16.x).

Usare il livello di compatibilità del database 140 o superiore per il database a cui si è connessi quando si esegue la query. È possibile modificarlo tramite ALTER DATABASE:

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

Query Store deve essere abilitato per ogni database in cui viene usata la parte di persistenza di questa funzionalità.

Disabilitare il percentile

Per disabilitare il percentile del feedback delle concessioni di memoria per tutte le esecuzioni di query provenienti dal database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

L'impostazione predefinita per MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT è ON.

Disabilitare la persistenza

Per disabilitare la persistenza del feedback delle concessioni di memoria per tutte le esecuzioni di query provenienti dal database,

eseguire quanto segue nel contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

La disabilitazione della persistenza dei feedback delle concessioni di memoria rimuoverà anche il feedback raccolto esistente.

L'impostazione predefinita per MEMORY_GRANT_FEEDBACK_PERSISTENCE è ON.

Considerazioni sul feedback sulle concessioni di memoria

È possibile visualizzare le impostazioni correnti attraverso la query sys.database_scoped_configurations.

Nota

Questa funzionalità non è abilitata se l’impostazione di BATCH_MODE_MEMORY_GRANT_FEEDBACK e ROW_MODE_MEMORY_GRANT_FEEDBACK è su OFF.

Considerato che i dati di feedback sono ora mantenuti in Query Store, si verifica un aumento dei requisiti di utilizzo di Query Store.

La concessione di memoria basata su percentile non viene eseguita sul lato della riduzione dello spill. Poiché non si basa più sull'ultima esecuzione, ma su un'osservazione delle diverse esecuzioni precedenti, questo potrebbe aumentare l'utilizzo della memoria per oscillare i carichi di lavoro con varianza estesa nei requisiti di concessione di memoria tra le esecuzioni.

A partire da SQL Server 2022 (16.x), quando è abilitato Query Store per le repliche secondarie, il feedback delle concessioni di memoria è compatibile con la replica per le repliche secondarie nei gruppi di disponibilità. Il feedback delle concessioni di memoria può comportarsi diversamente in una replica primaria e in una replica secondaria. Tuttavia, il feedback delle concessioni di memoria non viene salvato in modo permanente nelle repliche secondarie e, in caso di failover, il feedback delle concessioni di memoria dalla replica primaria precedente viene applicato alla nuova replica primaria. Quando la replica secondaria diventa la replica primaria qualsiasi feedback applicato viene perso. Per altre informazioni, vedere Query Store per repliche secondarie.

Contenuto correlato

• Elaborazione di query intelligenti nei database SQL
• Funzionalità di elaborazione di query intelligenti in dettaglio
• RECONFIGURE (Transact-SQL)
• Monitoraggio e ottimizzazione delle prestazioni
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)