Procedure consigliate per i pool SQL dedicati in Azure Synapse Analytics

  • Articolo

Questo articolo fornisce una raccolta di procedure consigliate per ottenere prestazioni ottimali per i pool SQL dedicati in Azure Synapse Analytics. Se si usa un pool SQL serverless, vedere Procedure consigliate per i pool SQL serverless per indicazioni specifiche. Di seguito sono riportate le linee guida di base e le aree importanti da concentrarsi sulla creazione della soluzione. Ogni sezione presenta un concetto e rimanda ad articoli più dettagliati che analizzano il concetto in modo più approfondito.

Caricamento di pool SQL dedicati

Per indicazioni sul caricamento di pool SQL dedicati, vedere Linee guida per il caricamento dei dati.

Ridurre i costi con sospensione e scalabilità

Per altre informazioni sulla riduzione dei costi tramite sospensione e scalabilità, vedere Gestire il calcolo.

Gestire le statistiche

È possibile configurare un pool SQL dedicato per rilevare e creare automaticamente statistiche sulle colonne. La qualità dei piani di query creati dall'utilità di ottimizzazione dipende dalla qualità delle statistiche disponibili.

È consigliabile abilitare AUTO_CREATE_STATISTICS per i database e mantenere aggiornate le statistiche ogni giorno o dopo ogni caricamento per assicurarsi che le statistiche sulle colonne usate nelle query siano sempre aggiornate.

Per accorciare il tempo di gestione delle statistiche, è necessario selezionare in base alle colonne con statistiche o che hanno bisogno di essere aggiornate con più frequenza. Ad esempio potrebbe essere consigliabile aggiornare le colonne di data, in cui potrebbero venire aggiunti nuovi valori ogni giorno. Concentrarsi su statistiche per le colonne usate nei join, nella clausola WHERE e presenti in GROUP BY.

Altre informazioni sulle statistiche sono disponibili negli articoli Gestire le statistiche delle tabelle, CREATE STATISTICS e UPDATE STATISTICS.

Ottimizzare le prestazioni delle query

Raggruppare le istruzioni INSERT in batch

Un caricamento in una sola volta in una tabella di piccole dimensioni con un'istruzione INSERT, ad esempio INSERT INTO MyLookup VALUES (1, 'Type 1'), può essere l'approccio migliore a seconda delle esigenze. Se tuttavia è necessario caricare migliaia o milioni di righe nel corso della giornata, è probabile che singole operazioni INSERT non siano ottimali.

Un modo per risolvere il problema consiste nello sviluppare un processo che scriva in un file e un altro processo che carichi periodicamente tale file. Per altre informazioni, fare riferimento all'articolo INSERT.

Utilizzare PolyBase per caricare ed esportare rapidamente i dati

Il pool SQL dedicato supporta il caricamento e l'esportazione dei dati tramite diversi strumenti, tra cui Azure Data Factory, PolyBase e BCP. Per piccole quantità di dati in cui le prestazioni non sono fondamentali, qualsiasi strumento può essere adeguato per soddisfare le esigenze.

Nota

PolyBase è la scelta migliore quando si caricano o si esportano grandi volumi di dati oppure è necessario ottenere prestazioni più veloci.

Le operazioni di caricamento di PolyBase possono essere eseguite usando CTAS o INSERT INTO. CTAS riduce al minimo la registrazione delle transazioni ed è il modo più rapido per caricare i dati. Azure Data Factory supporta anche i caricamenti PolyBase e può raggiungere prestazioni simili a quelle di CTAS. PolyBase supporta vari formati di file, inclusi i file con estensione gzip.

Per aumentare al massimo la velocità effettiva quando si usano file di testo con estensione gzip, suddividere i file in 60 o più file per ottimizzare il parallelismo del caricamento. Per una velocità effettiva totale maggiore, prendere in considerazione il caricamento simultaneo dei dati. Altre informazioni rilevanti per questa sezione sono incluse negli articoli seguenti:

Caricare ed eseguire query su tabelle esterne

PolyBase non è ottimale per le query. Le tabelle PolyBase per i pool SQL dedicati attualmente supportano solo i file BLOB di Azure e Azure Data Lake Storage. Questi file non sono supportati da alcuna risorsa di calcolo. Di conseguenza, i pool SQL dedicati non possono eseguire l'offload di questo lavoro e devono leggere l'intero file caricandolo in tempdb in modo da poter leggere i dati.

In presenza di numerose query relative a questi dati, è consigliabile caricare i dati una volta ed eseguire le query tramite la tabella locale. Altre indicazioni su PolyBase sono incluse nell'articolo Guida per l'uso di PolyBase .

Utilizzare la distribuzione hash per le tabelle di grandi dimensioni

Per impostazione predefinita, per le tabelle viene usata la distribuzione round robin. Con questa impostazione predefinita, per gli utenti è più facile iniziare a creare tabelle senza dover decidere come devono essere distribuite. Le tabelle round robin possono garantire prestazioni sufficienti per alcuni carichi di lavoro. Tuttavia, nella maggior parte dei casi una colonna di distribuzione fornisce prestazioni migliori.

L'esempio più comune di una tabella distribuita da una colonna che supera le prestazioni di una tabella round robin è quando vengono unite due tabelle dei fatti di grandi dimensioni.

Ad esempio, se sono presenti una tabella degli ordini distribuita in base a order_id e una tabella delle transazioni, anch'essa distribuita in base a order_id, quando si crea un join tra le due tabelle in order_id, la query diventa una query pass-through. Vengono quindi eliminate le operazioni di spostamento dati. Un numero inferiore di passaggi consente una maggiore velocità di esecuzione delle query. Anche un numero inferiore di operazioni di spostamento consente query più veloci.

Suggerimento

Quando si carica una tabella distribuita, i dati in ingresso non devono essere ordinati in base alla chiave di distribuzione. Questa operazione rallenterà i caricamenti.

I collegamenti agli articoli riportati di seguito forniranno altri dettagli sul miglioramento delle prestazioni tramite la selezione di una colonna di distribuzione. Sono inoltre disponibili informazioni su come definire una tabella distribuita nella clausola WITH dell'istruzione CREATE TABLE:

Non creare un numero eccessivo di partizioni

Anche se il partizionamento dei dati può essere efficace per la gestione dei dati tramite il cambio di partizione o l'ottimizzazione delle analisi con l'eliminazione delle partizioni, un numero eccessivo di partizioni può rallentare le query. Spesso una strategia di partizionamento con granularità elevata che potrebbe funzionare correttamente in SQL Server potrebbe non funzionare correttamente nel pool SQL dedicato.

Un numero eccessivo di partizioni può ridurre l'efficacia degli indici columnstore in cluster se ogni partizione contiene meno di 1 milione di righe. I pool SQL dedicati partizionano automaticamente i dati in 60 database. Quindi, se si crea una tabella con 100 partizioni, il risultato sarà 6000 partizioni. Ogni carico di lavoro è diverso, quindi la cosa migliore da fare è sperimentare diversi tipi di partizionamento per capire qual è la soluzione migliore per il carico di lavoro specifico.

Una delle opzioni da considerare è l'uso di una granularità inferiore a quella implementata usando SQL Server. Ad esempio, prendere in considerazione l'uso di partizioni settimanali o mensili invece che giornaliere.

Altre informazioni sul partizionamento sono illustrate in dettaglio nell'articolo Partizionamento delle tabelle.

Ridurre al minimo le dimensioni delle transazioni

In una transazione vengono eseguite le istruzioni INSERT, UPDATE e DELETE. Quando hanno esito negativo, occorre eseguire il rollback. Per ridurre il rischio di un rollback prolungato, ridurre al minimo le dimensioni delle transazioni, quando possibile. Tale riduzione può essere eseguita suddividendo in parti le istruzioni INSERT, UPDATE e DELETE. Se, ad esempio, si dispone di un'istruzione INSERT che si prevede richieda 1 ora, è possibile suddividerla in quattro parti. Ogni esecuzione verrà accorciata a 15 minuti.

Suggerimento

Sfruttare i casi speciali di registrazione minima, ad esempio CTAS, TRUNCATE, DROP TABLE o INSERT in tabelle vuote, in modo da ridurre il rischio di rollback.

Un altro modo per eliminare i rollback consiste nell'usare operazioni solo sui metadati come il cambio di partizione per la gestione dati. Ad esempio, anziché eseguire un'istruzione DELETE per eliminare tutte le righe di una tabella in cui order_date era nel mese di ottobre 2001, è possibile partizionare i dati mensilmente. È quindi possibile scambiare la partizione con dati con una partizione vuota di un'altra tabella (vedere gli esempi ALTER TABLE).

Per le tabelle non partizionate, prendere in considerazione l'uso di un CTAS per scrivere i dati da conservare in una tabella anziché usare DELETE. Se un'operazione CTAS richiede la stessa quantità di tempo, è molto più sicura da eseguire, perché prevede una registrazione delle transazioni minima e può essere annullata rapidamente, se necessario.

Altre informazioni sui contenuti descritti in questa sezione sono incluse negli articoli seguenti:

Ridurre le dimensioni dei risultati delle query

La riduzione delle dimensioni dei risultati delle query contribuisce a evitare problemi lato client causati da numerosi risultati delle query. È possibile modificare la query in modo da ridurre il numero di righe restituite. Alcuni strumenti per la generazione di query consentono di aggiungere la sintassi "top N" a ogni query. È anche possibile usare la sintassi CETAS sui risultati delle query per creare una tabella temporanea e poi usare l'esportazione PolyBase per l'elaborazione di livello inferiore.

Utilizzare colonne con dimensioni il più piccole possibili

Quando si definisce il codice DDL, per migliorare le prestazioni di query usare il più piccolo tipo di dati in grado di supportare i dati. Questo consiglio è particolarmente importante per le colonne CHAR e VARCHAR. Se il valore più lungo in una colonna è di 25 caratteri, definire la colonna come VARCHAR(25). Evitare di definire tutte le colonne di tipo carattere impostando una lunghezza predefinita elevata. Definire inoltre le colonne come VARCHAR quando è sufficiente invece di usare NVARCHAR.

Per una revisione più dettagliata dei concetti fondamentali relativi alle informazioni precedenti, vedere gli articoli Panoramica delle tabelle, Tipi di dati delle tabelle e CREATE TABLE.

Utilizzare tabelle heap temporanee per i dati temporanei

Quando si effettuano temporaneamente l'atterraggio dei dati nei pool SQL dedicati, le tabelle heap in genere renderanno più veloce il processo complessivo. Se si caricano dati solo per la gestione temporanea prima di eseguire altre trasformazioni, il caricamento della tabella in una tabella heap sarà molto più rapido del caricamento dei dati in una tabella columnstore in cluster.

Il caricamento dei dati in una tabella temporanea sarà molto più veloce del caricamento di una tabella in un archivio permanente. Le tabelle temporanee iniziano con "#" e può accedervi solo la sessione che le ha create. Di conseguenza, potrebbero funzionare solo in scenari limitati. Le tabelle heap sono definite nella clausola WITH di CREATE TABLE. Se si usa una tabella temporanea, ricordarsi di creare le statistiche anche su tale tabella temporanea.

Per altre informazioni, vedere gli articoli Tabelle temporanee, CREATE TABLE e CREATE TABLE AS edizione Standard LECT.

Ottimizzare le tabelle columnstore cluster

Gli indici columnstore cluster sono uno dei modi più efficienti per archiviare i dati nel pool SQL dedicato. Per impostazione predefinita, le tabelle nel pool SQL dedicato vengono create come ColumnStore cluster. Per ottenere prestazioni ottimali per le query sulle tabelle columnstore è importante che la qualità dei segmenti sia buona. Quando le righe vengono scritte nelle tabelle columnstore in condizioni di utilizzo elevato di memoria, la qualità dei segmenti columnstore potrebbe risentirne.

La qualità dei segmenti si può misurare in base al numero di righe in un gruppo di righe compresso. Per istruzioni dettagliate su come rilevare e migliorare la qualità dei segmenti per le tabelle columnstore in cluster, vedere la sezione Possibili cause di una qualità scadente dell'indice columnstore nell'articolo Indici delle tabelle.

Poiché l'elevata qualità dei segmenti columnstore è importante, è consigliabile usare ID utente della classe di risorse media o grande per caricare i dati. L'uso di unità di data warehouse inferiori implica l'intenzione di assegnare una classe di risorse di dimensioni superiori all'utente incaricato del caricamento.

Generalmente le tabelle columnstore non effettuano il push dei dati in un segmento columnstore compresso fino a quando non è presente più di 1 milione di righe per tabella. Ogni tabella del pool SQL dedicato viene distribuita in 60 distribuzioni diverse. Di conseguenza, le tabelle columnstore non trarranno vantaggio da una query, a meno che la tabella non contenga più di 60 milioni di righe.

Suggerimento

Per le tabelle con meno di 60 milioni di righe, la presenza di un indice columnstore potrebbe non essere la soluzione ottimale.

Se i dati vengono partizionati, ogni partizione dovrà contenere 1 milione di righe per trarre vantaggio da un indice columnstore in cluster. Una tabella con 100 partizioni deve contenere almeno 6 miliardi di righe per poter trarre vantaggio da un archivio di colonne in cluster (60 distribuzioni 100 partizioni 1 milione di righe).

Se la tabella non contiene 6 miliardi di righe, esistono due opzioni principali: ridurre il numero di partizioni o prendere il considerazione l'uso di una tabella heap. Potrebbe anche essere utile fare delle prove per verificare se è possibile ottenere prestazioni migliori usando una tabella heap con indici secondari al posto di una tabella columnstore.

Quando si esegue una query su una tabella columnstore, l'esecuzione sarà più rapida se si selezionano solo le colonne necessarie. Altre informazioni sugli indici di tabella e columnstore sono disponibili negli articoli seguenti:

Utilizzare una classe di risorse più grande per migliorare le prestazioni delle query

I pool SQL usano i gruppi di risorse come modo per allocare memoria alle query. Inizialmente, tutti gli utenti vengono assegnati alla classe di risorse piccola, che garantisce 100 MB di memoria per distribuzione. Sono sempre presenti 60 distribuzioni. A ogni distribuzione sono assegnati almeno 100 MB. L'allocazione di memoria totale a livello di sistema è di 6000 MB o appena inferiore a 6 GB.

Alcune query, ad esempio i join di grandi dimensioni oppure le operazioni di caricamento in tabelle columnstore cluster, risultano avvantaggiate da allocazioni di una quantità maggiore di memoria. Alcune query, come le operazioni di sola analisi, non traggono alcun vantaggio. L'uso di classi di risorse di dimensioni maggiori incide sulla concorrenza. Quindi, prima di spostare tutti gli utenti in una classe di risorse di grandi dimensioni, è necessario tenere a mente tutti questi fattori.

Per altre informazioni sulle classi di risorse, fare riferimento all'articolo Classi di risorse per la gestione del carico di lavoro.

Usare una classe di risorse più piccola per aumentare la concorrenza

Se si nota un ritardo prolungato nelle query, è possibile che gli utenti stiano usando classi di risorse più grandi. Questo scenario promuove il consumo di slot della concorrenza, che può causare l'accodamento di altre query. Per determinare se le query degli utenti sono in coda, eseguire SELECT * FROM sys.dm_pdw_waits per verificare se vengono restituite righe.

Altre informazioni sono disponibili negli articoli Classi di risorse per la gestione del carico di lavoro e sys.dm_pdw_waits.

Utilizzare DMV per monitorare e ottimizzare le query

I pool SQL dedicati hanno diverse DMV che possono essere usate per monitorare l'esecuzione delle query. L'articolo relativo al monitoraggio indicato di seguito fornisce istruzioni dettagliate su come visualizzare i dati di una query in esecuzione. Per trovare rapidamente le query in queste DMV, può essere utile usare l'opzione LABEL con le query. Per altre informazioni dettagliate, consultare gli articoli elencati di seguito:

Passaggi successivi

Consultare anche l'articolo Risoluzione dei problemi con i problemi comuni e le relative soluzioni.

Se sono necessarie informazioni non fornite in questo articolo, cercare azure Synapse nella pagina domande di Microsoft synapse per porre domande ad altri utenti e al gruppo di prodotti Azure Synapse Analytics.

Questo forum viene monitorato attivamente per garantire che venga fornita una risposta a tutte le domande, da un altro utente o da Microsoft. Se si preferisce porre domande su Stack Overflow, è disponibile anche un forum di Stack Overflow di Azure Synapse Analytics.