Procedure consigliate per il pool SQL serverless in Azure Synapse Analytics

Questo articolo include una raccolta di procedure consigliate per l'uso del pool SQL serverless. Il pool SQL serverless è una risorsa in Azure Synapse Analytics. Se si usa un pool SQL dedicato, vedere Procedure consigliate per i pool SQL dedicati per indicazioni specifiche.

Pool SQL serverless consente di eseguire query sui file negli account di archiviazione di Azure. Non include funzionalità di archiviazione o inserimento in locale. Tutti i file di destinazione delle query sono esterni al pool SQL serverless. Tutte le operazioni correlate alla lettura di file dall'archiviazione possono avere un impatto sulle prestazioni delle query.

Alcune linee guida generiche sono:

• Assicurarsi che le applicazioni client siano collocate con il pool SQL serverless.
  • Se si usano applicazioni client esterne ad Azure, assicurarsi di usare il pool SQL serverless in un'area vicina al computer client. Gli esempi di applicazioni client includono Power BI Desktop, SQL Server Management Studio e Azure Data Studio.
• Assicurarsi che l'archiviazione e il pool SQL serverless si trovino nella stessa area. Archiviazione esempi includono Azure Data Lake Storage e Azure Cosmos DB.
• Provare a ottimizzare il layout di archiviazione usando il partizionamento e mantenendo i file compresi nell'intervallo compreso tra 100 MB e 10 GB.
• Se si restituisce un numero elevato di risultati, assicurarsi di usare SQL Server Management Studio o Azure Data Studio e non Azure Synapse Studio. Azure Synapse Studio è uno strumento Web non progettato per set di risultati di grandi dimensioni.
• Se si filtrano i risultati in base alla colonna stringa, provare a usare le regole di confronto BIN2_UTF8. Per altre informazioni sulla modifica delle regole di confronto, vedere Tipi di regole di confronto supportati per Synapse SQL.
• Prendere in considerazione la possibilità di memorizzare nella cache i risultati sul lato client usando la modalità di importazione di Power BI o Azure Analysis Services e di aggiornarli periodicamente. I pool SQL serverless non possono offrire un'esperienza interattiva in modalità Power BI Direct Query se si usano query complesse o si elaborano grandi quantità di dati.
• La concorrenza massima non è limitata e dipende dalla complessità delle query e dalla quantità di dati analizzati. Un pool SQL serverless può gestire contemporaneamente 1.000 sessioni attive che eseguono query leggere. I numeri verranno diminuiti se le query sono più complesse o analizzano una quantità maggiore di dati, quindi in tal caso prendere in considerazione la riduzione della concorrenza ed eseguire query in un periodo di tempo più lungo, se possibile.

Applicazioni client e connessioni di rete

Assicurarsi che l'applicazione client sia connessa all'area di lavoro di Azure Synapse più vicina possibile con la connessione ottimale.

• Condividere il percorso di un'applicazione client con l'area di lavoro di Azure Synapse. Se si usano applicazioni come Power BI o Azure Analysis Service, assicurarsi che si trovino nella stessa area in cui è stata creata l'area di lavoro di Azure Synapse. Se necessario, creare le aree di lavoro separate associate alle applicazioni client. Se un'applicazione client e l'area di lavoro di Azure Synapse si trovano in aree diverse la latenza sarà maggiore e il flusso dei risultati sarà più lento.
• Se si esegue la lettura dei dati dall'applicazione locale, assicurarsi che l'area di lavoro di Azure Synapse si trovi nell'area vicina alla posizione dell'utente.
• Assicurarsi di non avere problemi di larghezza di banda di rete durante la lettura di una grande quantità di dati.
• Non usare Azure Synapse Studio per restituire una grande quantità di dati. Azure Synapse Studio è uno strumento Web che usa il protocollo HTTPS per trasferire i dati. Usare Azure Data Studio o SQL Server Management Studio per eseguire la lettura di una grande quantità di dati.

Archiviazione e layout del contenuto

Di seguito sono riportate le procedure consigliate per l'archiviazione e il layout del contenuto nel pool SQL serverless.

Condividere il percorso dell'archiviazione e del pool SQL serverless

Per ridurre al minimo la latenza, condividere il percorso dell'account di archiviazione di Azure o dell'archivio analitico di Azure Cosmos DB e l'endpoint del pool SQL serverless. Gli account di archiviazione e gli endpoint sottoposti a provisioning durante la creazione dell'area di lavoro si trovano nella stessa area.

Per prestazioni ottimali, se si accede ad altri account di archiviazione con un pool SQL serverless, assicurarsi che si trovino nella stessa area. Se non si trovano nella stessa area, si verificherà un aumento della latenza per il trasferimento in rete dei dati tra l'area remota e l'area dell'endpoint.

Limitazione delle richieste di archiviazione di Azure

Più applicazioni e servizi possono accedere all'account di archiviazione. La limitazione delle richieste di archiviazione si verifica quando il numero combinato di operazioni di I/O al secondo o unità di elaborazione generato da applicazioni, servizi e carichi di lavoro del pool SQL serverless supera i limiti dell'account di archiviazione. Di conseguenza, si verificherà un effetto negativo significativo sulle prestazioni delle query.

Quando viene rilevata la limitazione delle richieste, il pool SQL serverless prevede una soluzione predefinita per risolverla. Il pool SQL serverless effettuerà le richieste alla risorsa di archiviazione a un ritmo più lento fino a quando non verrà risolta la limitazione delle richieste.

Suggerimento

Per un'esecuzione ottimale delle query, evitare di sovraccaricare l'account di archiviazione con altri carichi di lavoro durante l'esecuzione di query.

Preparare i file per l'esecuzione di query

Se possibile, preparare i file per migliorare le prestazioni:

• Convertire file CSV e JSON di grandi dimensioni in Parquet. Parquet è un formato a colonne. Poiché è compresso, le dimensioni dei file sono ridotte rispetto a quelle dei file CSV o JSON che contengono gli stessi dati. Il pool SQL serverless ignora le colonne e le righe non necessarie in una query durante la lettura di file Parquet. Il pool SQL serverless richiede meno tempo e un numero minore di richieste di archiviazione per leggerli.
• Se una query è destinata a un singolo file di grandi dimensioni, è possibile dividerla in più file di dimensioni ridotte.
• Provare a mantenere le dimensioni dei file CSV tra 100 MB e 10 GB.
• È preferibile avere file di dimensioni uguali per un singolo percorso OPENROWSET o per la proprietà LOCATION di una tabella esterna.
• Partizionare i dati archiviando le partizioni in cartelle o nomi di file diversi. Vedere Usare le funzioni filename e filepath per indicare come destinazione partizioni specifiche.

Condividere il percorso dell'archivio analitico di Azure Cosmos DB e del pool SQL serverless

Assicurarsi che l'archivio analitico di Azure Cosmos DB risieda nella stessa area di un'area di lavoro di Azure Synapse. Le query tra aree potrebbero causare latenze significative. Usare la proprietà dell'area nella stringa di connessione per specificare in modo esplicito l'area in cui si trova l'archivio analitico (vedere Eseguire query in Azure Cosmos DB usando il pool SQL serverless): account=<database account name>;database=<database name>;region=<region name>'

Ottimizzazioni dei file CSV

Di seguito sono riportate le procedure consigliate per l'uso di file CSV nel pool SQL serverless.

Usare PARSER_VERSION 2.0 per eseguire query su file CSV

Quando si eseguono query su file CSV, è possibile usare un parser ottimizzato per le prestazioni. Per informazioni dettagliate, vedere PARSER_VERSION.

Creare manualmente le statistiche per i file CSV

Il pool SQL serverless si basa sulle statistiche per generare piani di esecuzione delle query ottimali. Vengono create automaticamente le statistiche per le colonne mediante il campionamento e nella maggior parte dei casi la percentuale di campionamento sarà inferiore al 100%. Questo flusso è lo stesso per ogni formato di file. Tenere presente che quando si legge un file CSV con il parser versione 1.0 il campionamento non è supportato e la creazione automatica delle statistiche non avviene con percentuale di campionamento inferiore al 100%. Per le tabelle di piccole dimensioni con cardinalità (numero di righe) bassa stimata verrà attivata la creazione automatica delle statistiche con una percentuale di campionamento del 100%. Ciò significa che viene attivata l'analisi completa e vengono create le statistiche automatiche anche per i file CSV con parser versione 1.0. Nel caso in cui le statistiche non vengano create automaticamente, creare le statistiche manualmente per le colonne usate nelle query, in particolare quelle usate con le clausole DISTINCT, JOIN, WHERE, ORDER BY e GROUP BY. Per informazioni dettagliate, vedere Statistiche nel pool SQL serverless.

Tipo di dati

Di seguito sono riportate le procedure consigliate per l'uso dei tipi di dati nel pool SQL serverless.

Usare i tipi di dati appropriati

I tipi di dati usati nella query influiscono sulle prestazioni e sulla concorrenza. Per migliorare le prestazioni, seguire queste linee guida:

• Usare le dimensioni di dati minime che supportano il valore massimo possibile.
  • Se la lunghezza massima del valore in caratteri è 30, usare un tipo di dati carattere di lunghezza 30.
  • Se tutti i valori delle colonne di caratteri sono di dimensioni fisse, usare char o nchar. In caso contrario, usare varchar o nvarchar.
  • Se il valore massimo di una colonna di numeri interi è 500, usare smallint, perché è il tipo di caratteri più piccolo in grado di supportare questo valore. Per altre informazioni, vedere Intervalli del tipo di dati Integer.
• Se possibile, usare varchar e char invece di nvarchar e nchar.
  • Usare il tipo varchar con alcune regole di confronto UTF8 se si esegue la lettura dei dati da Parquet, Azure Cosmos DB, Delta Lake o CSV con codifica UTF-8.
  • Usare il tipo varchar senza regole di confronto UTF8 se si esegue la lettura dei dati da file non Unicode CSV (ad esempio, ASCII).
  • Usare il tipo nvarchar se si esegue la lettura dei dati da un file CSV UTF-16.
• Se possibile, usare tipi di dati basati su integer. Le operazioni SORT, JOIN e GROUP BY vengono completate più velocemente su numeri interi che non su dati di tipo carattere.
• Se si usa l'inferenza dello schema, controllare i tipi di dati dedotti ed eseguirne l'override in modo esplicito con i tipi più piccoli, se possibile.

Controllare i tipi di dati dedotti

L'inferenza dello schema consente di scrivere rapidamente query e di esplorare i dati senza conoscere gli schemi dei file. Il costo di questa opzione sta nel fatto che i tipi di dati dedotti potrebbero avere dimensioni maggiori rispetto ai tipi di dati effettivi. Questa discrepanza si verifica quando nei file di origine non sono disponibili informazioni sufficienti per assicurare che venga usato il tipo di dati appropriato. Ad esempio, i file Parquet non contengono metadati sulla lunghezza massima delle colonne di caratteri. Di conseguenza, il pool SQL serverless lo deduce come varchar(8000).

Tenere presente che la situazione può essere diversa nel caso in cui le tabelle Spark gestite ed esterne condivisibili vengano esposte nel motore SQL come tabelle esterne. Le tabelle di Spark forniscono tipi di dati diversi rispetto ai motori Synapse SQL. Il mapping tra i tipi di dati della tabella Spark e i tipi SQL è disponibile qui.

È possibile usare la stored procedure di sistema sp_describe_first_results_set per controllare i tipi di dati risultanti della query.

L'esempio seguente mostra come ottimizzare i tipi di dati dedotti. Questa procedura viene usata per visualizzare i tipi di dati dedotti:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Il set di risultati è il seguente:

is_hidden	column_ordinal	name	system_type_name	max_length
0	1	vendor_id	varchar(8000)	8000
0	2	pickup_datetime	datetime2(7)	8
0	3	passenger_count	int	4

Se si conoscono i tipi di dati dedotti per la query, è possibile specificare quelli appropriati:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Ottimizzazione del filtro

Di seguito sono riportate le procedure consigliate per l'uso delle query nel pool SQL serverless.

Eseguire il push di caratteri jolly per ridurre i livelli nel percorso

È possibile usare caratteri jolly nel percorso per eseguire query su più file e cartelle. Il pool SQL serverless elenca i file nell'account di archiviazione, a partire dal primo asterisco (*), usando l'API di archiviazione. Elimina i file che non corrispondono al percorso specificato. La riduzione dell'elenco iniziale di file può migliorare le prestazioni nel caso in cui molti file corrispondano al percorso specificato fino al primo carattere jolly.

Usare le funzioni filename e filepath per indicare come destinazione partizioni specifiche

I dati sono spesso organizzati in partizioni. È possibile indicare al pool SQL serverless di eseguire query su cartelle e file specifici. Questa operazione ridurrà il numero di file e la quantità di dati che la query deve leggere ed elaborare. Un ulteriore vantaggio è che si otterranno prestazioni più elevate.

Per altre informazioni, vedere le informazioni sulle funzioni filename e filepath e vedere gli esempi relativi a query su file specifici.

Suggerimento

Eseguire sempre il cast dei risultati delle funzioni filepath e filename al tipo di dati appropriato. Se si usano tipi di dati carattere, assicurarsi di usare la lunghezza appropriata.

Le funzioni usate per l'eliminazione di partizioni, filepath e filename, non sono attualmente supportate per tabelle esterne diverse da quelle create automaticamente per ogni tabella creata in Apache Spark per Azure Synapse Analytics.

Se i dati archiviati non sono partizionati, prendere in considerazione di partizionarli. In questo modo è possibile usare queste funzioni per ottimizzare le query da eseguire su tali file. Quando si esegue una query su tabelle Apache Spark per Azure Synapse partizionate dal pool SQL serverless, la query viene automaticamente eseguita solo sui file necessari.

Usare regole di confronto appropriate per il pushdown del predicato per le colonne di caratteri

I dati in un file Parquet sono organizzati in gruppi di righe. Il pool SQL serverless ignora i gruppi di righe in base al predicato specificato nella clausola WHERE, che riduce l'I/O. Il risultato è un aumento delle prestazioni delle query.

Il pushdown del predicato per le colonne di caratteri nei file Parquet è supportato solo per le regole di confronto Latin1_General_100_BIN2_UTF8. È possibile specificare regole di confronto per una determinata colonna usando una clausola WITH. Se non si specifica queste regole di confronto usando una clausola WITH, vengono usate le regole di confronto del database.

Ottimizzare le query ripetute

Di seguito sono riportate le procedure consigliate per l'uso della funzionalità CREATE EXTERNAL TABLE AS (CETAS) nel pool SQL serverless.

Usare CETAS per ottimizzare le prestazioni e i join delle query

CETAS è una delle funzionalità più importanti disponibili nel pool SQL serverless. CETAS è un'operazione parallela che crea metadati di tabelle esterne ed esporta i risultati delle query SELECT in un set di file nell'account di archiviazione.

È possibile usare CETAS per materializzare parti di query usate di frequente, come le tabelle di riferimento unite in join, in un nuovo set di file. È quindi possibile creare un join a questa sola tabella esterna invece di ripetere join comuni in più query.

Poiché CETAS genera file Parquet, le statistiche vengono create automaticamente quando la prima query raggiunge questa tabella esterna. Il risultato è un miglioramento delle prestazioni per le query successive destinate alla tabella generata con CETAS.

Eseguire query sui dati di Azure

I pool SQL serverless consentono di eseguire query sui dati in Archiviazione di Azure o in Azure Cosmos DB usando tabelle esterne e la funzione OPENROWSET. Assicurarsi che nella risorsa di archiviazione sia configurata l'autorizzazione corretta.

Eseguire query sui dati CSV

Informazioni su come eseguire query su un singolo file CSV o su cartelle e più file CSV. È anche possibile eseguire query sui file partizionati

Eseguire query su file Parquet

Informazioni su come eseguire query sui file Parquet con tipi nidificati. È anche possibile eseguire query sui file partizionati.

Eseguire query su Delta Lake

Informazioni su come eseguire query sui file Delta Lake con tipi nidificati.

Eseguire query sui dati di Azure Cosmos DB

Informazioni su come eseguire query sull'archivio analitico di Azure Cosmos DB. È possibile usare un generatore online per generare la clausola WITH in base a un documento di Azure Cosmos DB di esempio. È possibile creare viste sui contenitori di Azure Cosmos DB.

Eseguire una query dei dati JSON

Informazioni su come eseguire query sui file JSON. È anche possibile eseguire query sui file partizionati.

Creare viste, tabelle e altri oggetti di database

Informazioni su come creare e usare viste e tabelle esterne o configurare la sicurezza a livello di riga. Se sono presenti file partizionati, assicurarsi di usare viste partizionate.

Copiare e trasformare i dati (CETAS)

Informazioni su come archiviare i risultati delle query nell'account di archiviazione usando il comando CETAS.

Passaggi successivi

• Vedere l'articolo sulla risoluzione dei problemi dei pool SQL serverless per le soluzioni ai problemi comuni.
• Se si usa un pool SQL dedicato anziché un pool SQL serverless, vedere Procedure consigliate per i pool SQL dedicati per indicazioni specifiche.
• Domande frequenti su Azure Synapse Analytics
• Concedere autorizzazioni all'identità gestita dell'area di lavoro