SQL Server 2022 Degree of parallelism feedback

Degree of parallelism (DOP) feedback è una delle nuove feature di SQL Server 2022 e si colloca nella famiglia di funzionalità conosciuta con il nome di Intelligent Query Processing. Queste funzionalità intelligenti e adattive migliorano le prestazioni dei carichi di lavoro esistenti senza modifiche al codice applicativo. Intelligent Query Processing potenziato anche in SQL Server 2019 è l’erede della precedente tecnologia nota con il nome di Adaptive Query Processing.

Degree of parallelism (DOP) feedback

Degree of parallelism (DOP) feedback in SQL Server 2022 potenzia ulteriormente Intelligent Query Processing affrontando lo scenario che si verifica quando una query OLTP viene eseguita ripetutamente in parallel mode e vengono riscontrati problemi di prestazioni. Il parallelismo nel piano di esecuzione di una query è spesso utile per le query analitiche o per le query che gestiscono grandi quantità di dati. Al contrario, le query tipiche di un carico di lavoro OLTP eseguite in parallel mode potrebbero riscontrare problemi di prestazioni quando il tempo impiegato per coordinare i thread interessati supera i vantaggi dell’utilizzo di un piano di esecuzione parallelo.

Nelle versioni precedenti a SQL Server 2019, il valore predefinito per l’opzione MAXDOP (max degree of parallelism) sia a livello di istanza (server level) che in ambito database (database level) era impostato con il valore 0 (zero). Il valore zero indica all’Engine di SQL Server di utilizzare tutti gli scheduler disponibili durante l’elaborazione di una query con piano di esecuzione parallelo. SQL Server 2019 modifica l’impostazione predefinita di MAXDOP sia per le istanze SQL Server on-premise che per Azure SQL Database. Per le istanze on-premise il valore di MAXDOP (server level) viene impostando con il risultato di un calcolo basato sui processori rilevati al momento dell’installazione. Per le istanze Azure SQL Database il valore di MAXDOP (server level) viene impostato con il valore predefinito 8. Per maggiori dettagli sulla configurazione dell’opzione MAXDOP potete consultare l’articolo Configure the max degree of parallelism Server Configuration Option.

Il compito della nuova funzione DOP feedback è quello di autoregolare l’opzione MAXDOP per evitare i problemi sopra descritti azzerando le difficoltà dovute alla determinazione di un’impostazione predefinita omnicomprensiva di MAXDOP e agli interventi di modifica manuali a ciascuna query.

Come funziona DOP feedback?

DOP feedback identificherà per voi le inefficienze dovute al parallelismo per le query ripetute, in base al tempo di esecuzione e ai tipi di attesa (wait types) incontrati. Se l’utilizzo del parallelismo verrà ritenuto inefficiente, DOP Feedback abbasserà il grado di parallelismo nella successiva esecuzione della query, il meccanismo verrà innescato qualunque sia il grado di parallelismo configurato, verrà quindi verificato il tempo di esecuzione e i tipi di attesa incontrati con la nuova configurazione determinando se c’è stato un beneficio.

Per valutare l’idoneità di una query ad essere trattata con DOP feedback, il tempo di esecuzione della query (total elapsed time al netto delle attese di tipo Buffer Latch, Buffer IO e Network IO che sono riferite a fattori esterni all’esecuzione della query parallela) viene misurato su alcune esecuzioni della query stessa. L’obiettivo è quello di aumentare la concorrenza complessiva e ridurre significativamente le attese, anche a fronte di un leggero aumento del tempo di esecuzione della query.

Solo i feedback verificati verranno mantenuti.

Se la correzione sul grado di parallelismo produrrà una regressione delle prestazioni, il grado di parallelismo verrà ripristinato con l’ultimo valore valido noto per la query. In questo contesto, anche una query annullata dall’utente verrà percepita come una regressione. DOP feedback non effettuerà ricompilazioni dei piani di esecuzione. Una correzione stabile, in uso, del grado di parallelismo viene verificata ad ogni ricompilazione del piano di esecuzione e può riadattarsi all’ultimo backup o essere nuovamente rivista al ribasso, comunque mai al di sopra dell’impostazione MAXDOP (inclusi gli hint MAXDOP).

Per ulteriori informazioni guardate il video Azure SQL & SQL Server 2022 Intelligent Database Futures:

Per abilitare DOP feedback a livello di database utilizzate il comando ALTER DATABASE e impostate a ON l’opzione DOP_FEEDBACK come illustra il seguente frammento di codice T-SQL.

-- Enable DOP feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
GO

In modo analogo, per disabilitare DOP feedback a livello di database impostate a OFF l’opzione DOP_FEEDBACK come illustra il seguente frammento di codice T-SQL.

-- Disable DOP feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF;
GO

Per disabilitare DOP feedback a livello di query utilizzate il query hint DISABLE_DOP_FEEDBACK.

Qualora abbiate forzato il piano di esecuzione per una query attraverso Query Store, DOP feedback potrà comunque essere utilizzato per ottimizzare il grado di parallelismo della query. Qualora abbiate utilizzato il query hint MAXDOP direttamente nel codice della query o tramite i suggerimenti di Query Store e l’hint MAXDOP è maggiore di 2, DOP feedback potrà abbassare ulteriormente il grado di parallelismo utilizzando il nuovo limite suggerito nella modalità descritta precedentemente. Per maggiori dettagli potete consultare gli articoli Hints (Transact-SQL) – Query e Query Store hints (preview).

E’ possibile monitorare il funzionamento di DOP feedback sia attraverso 4 nuovi Extended Events sia attraverso la via di sistema sys.query_store_plan_feedback.

Riporto di seguito i 4 nuovi Extended Events:

• dop_feedback_eligible_query
  • Si verifica quando il piano di esecuzione di una query diventa idoneo il tentativo di ottimizzazione con DOP feedback
• dop_feedback_provided
  • Si verifica quando DOP feedback ha fornito un suggerimento di correzione del grado di parallelismo
• dop_feedback_validation
  • Si verifica quando la correzione del grado di parallelismo viene attuata e ne vengono monitorati i risultati
• dop_feedback_reverted
  • Si verifica quando viene ripristinato l’ultimo valore valido noto di MAXDOP per la query

Conclusioni

Degree of parallelism (DOP) feedback è una delle nuove feature di SQL Server 2022 e si colloca nella famiglia di funzionalità conosciuta con il nome di Intelligent Query Processing. Queste funzionalità intelligenti e adattive migliorano le prestazioni dei carichi di lavoro esistenti senza modifiche al codice applicativo. DOP feedback è in grado di autoregolare l’opzione MAXDOP per evitare i problemi di performance dovuti all’utilizzo del parallelismo nelle query OLTP eseguite ripetutamente.

Buon divertimento con SQL Server 2022 e DOP feedback!