Descrivere l’elaborazione di query intelligenti
In SQL Server 2017 e 2019 e con Azure SQL, Microsoft ha introdotto numerose nuove funzionalità nei livelli di compatibilità 140 e 150. Molte di queste funzionalità correggono quelli che in precedenza erano anti-pattern, ad esempio l'uso di funzioni di valore scalare definite dall'utente e l'uso di variabili di tabella.
Queste funzionalità si suddividono in alcune famiglie di funzionalità:
L'Elaborazione di query intelligenti include funzionalità che migliorano le prestazioni esistenti del carico di lavoro con un lavoro minimo richiesto per l'implementazione.
Per rendere i carichi di lavoro automaticamente idonei per l'Elaborazione di query intelligenti, impostare il livello di compatibilità del database applicabile su 150. Ad esempio:
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;
Elaborazione di query adattive
L'elaborazione di query adattive include molte opzioni che rendono più dinamica l'elaborazione di query, in base al contesto di esecuzione di una query. Queste opzioni includono diverse funzionalità che migliorano l'elaborazione delle query.
Join adattivi: il motore di database rinvia la scelta del join tra i cicli hash e annidati in base al numero di righe che passano nel join. I join adattivi funzionano attualmente solo in modalità di esecuzione batch.
Esecuzione interleaved: attualmente questa funzionalità supporta le funzioni con valori di tabella con più istruzioni (MSTVF). Prima di SQL Server 2017, le funzioni MSTVF usavano una stima di riga fissa di una o 100 righe, a seconda della versione di SQL Server. Questa stima poteva condurre a piani di query non ottimali se la funzione restituiva molte più righe. Viene generato un conteggio di righe effettivo dalla funzione MSTVF prima della compilazione del resto del piano con l'esecuzione Interleaved.
Commenti sulla concessione di memoria: SQL Server genera una concessione di memoria nel piano iniziale della query, in base alle stime delle statistiche relative al conteggio delle righe. Un’importante asimmetria dei dati può comportare un numero eccessivo o inferiore di stime dei conteggi delle righe, che possono causare una quantità eccessiva di memoria che diminuisce la concorrenza, o inferiore, che può causare lo spill dei dati in tempdb da parte della query. Con i Commenti sulla concessione di memoria, SQL Server rileva queste condizioni e diminuisce o aumenta la quantità di memoria concessa alla query per evitare lo spill o l'allocazione eccessiva.
Tutte queste funzionalità sono abilitate automaticamente in modalità di compatibilità 150 e non richiedono l’abilitazione di altre modifiche.
Compilazione posticipata delle variabili di tabella
Analogamente alle funzioni MSTVF, le variabili di tabella nei piani di esecuzione SQL Server eseguono una stima fissa del numero di righe di una sola riga. Analogamente alle MSTVFs, questa stima fissa ha portato a prestazioni scarse quando la variabile aveva un numero di righe maggiore del previsto. Con SQL Server 2019, le variabili di tabella sono ora analizzate e hanno un conteggio effettivo delle righe. La compilazione posticipata è simile all'esecuzione interleaved per le funzioni MSTVF, con la differenza che viene eseguita alla prima compilazione della query anziché in modo dinamico all'interno del piano di esecuzione.
Modalità batch nell'archivio righe
La modalità di esecuzione batch consente di elaborare i dati in batch invece che riga per riga. Le query che comportano costi significativi della CPU per i calcoli e le aggregazioni possono trarre il massimo vantaggio da questo modello di elaborazione. Separando l'elaborazione batch e gli indici columnstore, più carichi di lavoro possono trarre vantaggio dall'elaborazione in modalità batch.
Inlining di funzioni scalari definite dall'utente
Nelle versioni precedenti di SQL Server le funzioni scalari offrivano scarse prestazioni per diversi motivi. Le funzioni scalari venivano eseguite in modo iterativo, elaborando una riga alla volta. Non prevedevano una stima dei costi adeguata in un piano di esecuzione e non consentivano il parallelismo in un piano di query. Con l'inlining di funzioni definite dall'utente, queste funzioni vengono trasformate in sottoquery scalari al posto dell'operatore della funzione definita dall'utente nel piano di esecuzione. Questa trasformazione può comportare un miglioramento significativo delle prestazioni per le query che coinvolgono chiamate di funzioni scalari.
Count Distinct approssimativo
Un comune modello di query del data warehouse consiste nell'eseguire un conteggio distinto di ordini o utenti. Questo modello di query può essere dispendioso rispetto a una tabella di grandi dimensioni. Conteggio approssimativo distinto introduce un approccio più rapido per ottenere un conteggio distinto raggruppando le righe. Questa funzione garantisce una percentuale di errore del 2% con un intervallo di confidenza del 97%.