Funzionalità per le prestazioni del flusso di dati

Si applica a:SQL Server SSIS Integration Runtime in Azure Data Factory

In questo argomento sono inclusi alcuni suggerimenti sulla progettazione di pacchetti di Integration Services per evitare problemi di prestazioni comuni. Sono inoltre fornite informazioni sugli strumenti e sulle funzionalità che è possibile usare per risolvere i problemi relativi alle prestazioni dei pacchetti.

Configurazione del flusso di dati

Per configurare l'attività Flusso di dati per ottenere prestazioni migliori, è possibile configurare le proprietà dell'attività, modificare le dimensioni del buffer e configurare il pacchetto per l'esecuzione parallela.

Configurazione delle proprietà dell'attività Flusso di dati

Nota

Le proprietà trattate in questa sezione devono essere impostate separatamente per ogni attività Flusso di dati in un pacchetto.

È possibile configurare le proprietà seguenti dell'attività Flusso di dati, che influenzano le prestazioni:

  • Specificare i percorsi per l'archiviazione temporanea dei dati del buffer (proprietà BufferTempStoragePath) e delle colonne che contengono dati BLOB (Binary Large Object) (proprietà BLOBTempStoragePath). Per impostazione predefinita, le proprietà contengono i valori delle variabili di ambiente TEMP e TMP. Potrebbe essere necessario specificare altre cartelle per inserire i file temporanei in un'unità disco rigido diversa o più potente o distribuirli in più unità. È possibile specificare più directory delimitando i relativi nomi con punti e virgola (;).

  • Definire le dimensioni predefinite del buffer usato dall'attività, impostando la proprietà DefaultBufferSize e il numero massimo di righe in ogni buffer, impostando la proprietà DefaultBufferMaxRows. Impostare la proprietà AutoAdjustBufferSize per indicare se le dimensioni predefinite del buffer vengono calcolate automaticamente dal valore della proprietà DefaultBufferMaxRows. Le dimensioni predefinite del buffer sono di 10 MB e quelle massime di 2^31-1 byte. Il numero di righe massimo predefinito è 10.000.

  • Definire il numero di thread che l'attività può usare durante l'esecuzione, impostando la proprietà EngineThreads. Questa proprietà indica al motore flusso di dati il numero di thread da utilizzare. Il valore predefinito è 10. Il valore minimo è 3. Il motore, tuttavia, non usa più thread di quelli necessari, indipendentemente dal valore di questa proprietà. Il motore può inoltre usare un numero di thread maggiore di quello specificato da questa proprietà, se necessario per evitare problemi di concorrenza.

  • Indicare se l'attività Flusso di dati viene eseguita in modalità ottimizzata, impostando la proprietà RunInOptimizedMode. La modalità ottimizzata consente di migliorare le prestazioni rimuovendo dal flusso di dati le colonne, gli output e i componenti inutilizzati.

    Nota

    Per indicare che l'attività Flusso di dati viene eseguita in modalità ottimizzata durante il debug, è possibile impostare una proprietà con lo stesso nome, ovvero RunInOptimizedMode, a livello di progetto in SQL Server Data Tools (SSDT). Questa proprietà del progetto sovrascrive la proprietà RunInOptimizedMode delle attività Flusso di dati in fase di progettazione.

Modificare il ridimensionamento dei buffer

Il motore flusso di dati inizia l'attività di ridimensionamento dei buffer calcolando le dimensioni stimate di una singola riga di dati. Quindi moltiplica la dimensione stimata di una riga per il valore di DefaultBufferMaxRows per ottenere un valore di lavoro preliminare per la dimensione del buffer.

  • Se AutoAdjustBufferSize è impostato su true, il motore del flusso di dati usa il valore calcolato come dimensione del buffer e il valore di DefaultBufferSize viene ignorato.

  • Se AutoAdjustBufferSize è impostato su false, il motore del flusso di dati utilizza le regole seguenti per determinare le dimensioni del buffer.

    • Se il risultato è superiore al valore di DefaultBufferSize, il numero di righe viene ridotto.

    • Se il risultato è inferiore alle dimensioni minime del buffer calcolate internamente, il numero di righe viene aumentato.

    • Se il risultato è compreso tra la dimensione minima del buffer e il valore della proprietà DefaultBufferSize, il motore dimensiona il buffer il più vicino possibile al prodotto tra la dimensione stimata della riga e il valore di DefaultBufferMaxRows.

Quando si inizia a verificare le prestazioni delle attività del flusso di dati, utilizzare i valori predefiniti per DefaultBufferSize e DefaultBufferMaxRows. Abilitare la registrazione nell'attività Flusso di dati e selezionare l'evento BufferSizeTuning per verificare il numero di righe presenti in ogni buffer.

Prima di iniziare a modificare il ridimensionamento dei buffer, è possibile ridurre le dimensioni di ogni riga di dati rimuovendo le colonne non necessarie e configurando i tipi di dati in modo appropriato.

Per determinare il numero e le dimensioni ottimali dei buffer, provare a usare diversi valori di DefaultBufferSize e DefaultBufferMaxRows monitorando le prestazioni e analizzando le informazioni riportate dall'evento BufferSizeTuning.

Evitare di aumentare le dimensioni del buffer fino al punto in cui inizia a verificarsi il paging su disco. Il paging su disco influisce negativamente sulle prestazioni più di una dimensione del buffer non ottimizzata. Per determinare se si verifica paging, monitorare il contatore delle prestazioni "Buffers spooled" nello snap-in Prestazioni della Microsoft Management Console (MMC).

Configurare il pacchetto per l'esecuzione parallela

L'esecuzione parallela migliora le prestazioni nei computer dotati di più processori fisici o logici. Per supportare l'esecuzione parallela di diverse attività nel pacchetto, Integration Services usa due proprietà: MaxConcurrentExecutables e EngineThreads.

La proprietà MaxConcurrentExecutables

La proprietà MaxConcurrentExecutables è una proprietà del pacchetto stesso. Questa proprietà definisce il numero massimo di attività che è possibile eseguire simultaneamente. Il valore predefinito è -1, a indicare il numero di processori fisici o logici più 2.

Per comprendere come funziona questa proprietà, si consideri un pacchetto di esempio che contiene tre attività di flusso di dati. Se si imposta MaxConcurrentExecutables su 3, le tre attività Flusso di dati potranno essere eseguite tutte simultaneamente. Si supponga, tuttavia, che ogni attività Flusso di dati contenga 10 alberi di esecuzione dall'origine alla destinazione. L'impostazione di MaxConcurrentExecutables su 3 non garantisce l'esecuzione in parallelo degli alberi di esecuzione in ogni attività Flusso di dati.

Proprietà EngineThreads

La proprietà EngineThreads è una proprietà di tutte le attività Flusso di dati. Questa proprietà definisce il numero di thread che il motore del flusso di dati può creare ed eseguire in parallelo. La proprietà EngineThreads si applica ugualmente sia ai thread di origine creati dal motore del flusso di dati per le origini sia ai thread di lavoro creati dal motore per trasformazioni e destinazioni. L'impostazione di EngineThreads su 10, pertanto, indica che il motore può creare fino a dieci thread di origine e fino a dieci thread di lavoro.

Per comprendere il funzionamento di questa proprietà, considerare il pacchetto di esempio contenente tre attività Flusso di dati. Ogni attività Flusso di dati contiene dieci alberi di esecuzione dall'origine alla destinazione. Se si imposta EngineThreads su 10 in ogni attività Flusso di dati, è virtualmente possibile eseguire tutti i 30 alberi di esecuzione simultaneamente.

Nota

Una trattazione del threading esula dall'ambito di questo argomento. La regola generale, tuttavia, consiste nell'evitare l'esecuzione in parallelo di un numero di thread maggiore del numero di processori disponibili. L'esecuzione di un numero di thread maggiore del numero di processori disponibili può influire negativamente sulle prestazioni a causa del frequente scambio del contesto tra thread.

Configurazione di singoli componenti del flusso di dati

Per configurare singoli componenti del flusso di dati ai fini delle prestazioni, è consigliabile seguire alcune linee guida generali. Vi sono inoltre linee guida specifiche per ogni tipo di componente del flusso di dati, ovvero origine, trasformazione e destinazione.

Linee guida generali

Indipendentemente dal componente del flusso di dati, è consigliabile seguire due linee guida generali per migliorare le prestazioni: ottimizzare le query ed evitare stringhe non necessarie.

Ottimizzazione delle query

Numerosi componenti del flusso di dati usano query in operazioni di estrazione dei dati dalle origini o in operazioni di ricerca per la creazione di tabelle di riferimento. La query predefinita usa la sintassi SELECT * FROM <tableName>. Questo tipo di query restituisce tutte le colonne della tabella di origine. La disponibilità di tutte le colonne in fase di progettazione consente di scegliere qualsiasi colonna come colonna di ricerca, colonna pass-through o colonna di origine. Dopo avere selezionato le colonne da usare, è tuttavia necessario modificare la query in modo che includa solo le colonne selezionate. La rimozione delle colonne superflue garantisce una maggiore efficienza del flusso di dati in un pacchetto, in quanto un minor numero di colonne comporta la creazione di una riga con dimensioni inferiori. Una riga con dimensioni inferiori fa sì che sia possibile inserire più righe in un buffer e che l'elaborazione di tutte le righe nel set di dati risulti meno complessa.

Per creare una query, è possibile digitarla o usare Generatore query.

Nota

Quando si esegue un pacchetto in SQL Server Data Tools (SSDT), nella scheda Stato di Progettazione SSIS vengono visualizzati alcuni avvisi. Tali avvisi includono l'identificazione di qualsiasi colonna di dati resa disponibile per il flusso di dati da un'origine, ma che non viene usata successivamente dai componenti del flusso di dati a valle. È possibile usare la proprietà RunInOptimizedMode per rimuovere queste colonne automaticamente.

Eliminazione di operazioni di ordinamento superflue

L'ordinamento è di per sé un'operazione lenta. Evitando operazioni di ordinamento non necessarie è pertanto possibile migliorare le prestazioni del flusso di dati di un pacchetto.

Talvolta i dati di origine sono già ordinati prima di essere usati da un componente a valle. Questo preordinamento può verificarsi quando la query SELECT usa una clausola ORDER BY o quando i dati vengono inseriti nell'origine come già ordinati. Per tali dati di origine già ordinati, è possibile fornire un'indicazione che segnali che i dati sono ordinati, evitando così l'uso di una trasformazione Ordinamento per soddisfare i requisiti di ordinamento di determinate trasformazioni a valle. Le trasformazioni Unione e Merge Join, ad esempio, richiedono input ordinati. Per fornire un hint indicante che i dati sono ordinati, è necessario eseguire le attività seguenti:

  • Impostare la proprietà IsSorted nell’output di un componente upstream del flusso di dati su True.

  • Specificare le colonne chiave di ordinamento in cui i dati sono ordinati.

Per altre informazioni, vedere Ordinare i dati per le trasformazioni Merge e Merge Join.

Se è necessario ordinare i dati del flusso di dati, è possibile migliorare le prestazioni progettando il flusso di dati in modo che venga eseguito il minor numero possibile di operazioni di ordinamento. Il flusso di dati, ad esempio, usa una trasformazione Multicast per copiare il set di dati. Ordinare il set di dati una volta prima dell'esecuzione della trasformazione Multicast anziché ordinare più output in seguito alla trasformazione.

Per altre informazioni, vedere Sort Transformation, Merge Transformation, Merge Join Transformatione Multicast Transformation.

Origini

Origine OLE DB

Quando si usa un'origine dati OLE DB per recuperare dati da una vista, selezionare "Comando SQL" come modalità di accesso ai dati e immettere un'istruzione SELECT. L'accesso a dati tramite un'istruzione SELECT è più efficace rispetto alla selezione di "Tabella o vista" come modalità di accesso ai dati.

Trasformazioni

Utilizzare i suggerimenti presenti in questa sezione per migliorare le prestazioni delle trasformazioni Aggregazione, Ricerca fuzzy, Raggruppamento fuzzy, Ricerca, Merge Join e Dimensione a modifica lenta.

Trasformazione di aggregazione

La trasformazione Aggregazione include le proprietà Keys, KeysScale, CountDistinctKeyse CountDistinctScale . Queste proprietà migliorano le prestazioni in quanto consentono alla trasformazione di preallocare la quantità di memoria necessaria per i dati memorizzati nella cache. Se si conosce il numero esatto o approssimativo di gruppi che dovrebbero risultato da un'operazione Group by , impostare rispettivamente le proprietà Keys e KeysScale . Se si conosce il numero esatto o approssimativo di valori distinti che si prevede risultino da un'operazione di conteggio distinto, impostare rispettivamente le proprietà CountDistinctKeys e CountDistinctScale.

Se in un flusso di dati è necessario creare più aggregazioni, valutare l'opportunità di creare più aggregazioni che usano una singola trasformazione Aggregazione anziché creare più trasformazioni. Questo approccio consente prestazioni migliori quando un'aggregazione è un subset di un'altra aggregazione, in quanto la trasformazione può ottimizzare l'archiviazione interna ed eseguire l'analisi dei dati in ingresso una sola volta. Nel caso, ad esempio, di un'aggregazione che usa la clausola GROUP BY e l'aggregazione AVG, è possibile migliorare le prestazioni combinando clausola e aggregazione in una sola trasformazione. Tuttavia, l'esecuzione di più aggregazioni all'interno di un'unica trasformazione Aggregate serializza le operazioni di aggregazione e, pertanto, potrebbe non migliorare le prestazioni quando più aggregazioni devono essere calcolate in modo indipendente.

Trasformazioni di ricerca fuzzy e di raggruppamento fuzzy

Per informazioni sull'ottimizzazione della prestazione di tali trasformazioni, vedere il white paper relativo a Ricerca fuzzy e Raggruppamento fuzzy in SQL Server Integration Services 2005.

Trasformazione di ricerca

È possibile ridurre al minimo le dimensioni dei dati di riferimento nella memoria immettendo un'istruzione SELECT per la ricerca delle sole colonne necessarie. Questa opzione garantisce prestazioni migliori rispetto alla selezione di un'intera tabella o vista, che restituisce invece una quantità elevata di dati non necessari.

Trasformazione Merge Join

Non è più necessario configurare il valore della proprietà MaxBuffersPerInput , in quanto Microsoft ha apportato modifiche che riducono il rischio di utilizzo di una quantità eccessiva di memoria da parte della trasformazione Merge join. Questo problema si verificava in genere quando tramite i diversi input della trasformazione Merge Join venivano prodotti dati con frequenze irregolari.

Trasformazione della dimensione a variazione lenta

La Creazione guidata Dimensioni a modifica lenta e la trasformazione Dimensione a modifica lenta sono strumenti generici che soddisfano le esigenze della maggior parte degli utenti. Il flusso di dati generato dalla procedura guidata, tuttavia, non è ottimizzato per le prestazioni.

In genere, i componenti più lenti nella trasformazione Slowly Changing Dimension sono le trasformazioni Comando OLE DB che eseguono operazioni UPDATE una riga alla volta. Il modo più efficace per migliorare le prestazioni della trasformazione dimensione a variazione lenta consiste pertanto nel sostituire le trasformazioni OLE DB Command. È possibile sostituire tali trasformazioni con componenti di destinazione che salvano tutte le righe da aggiornare in una tabella di staging. È quindi possibile aggiungere un'attività Esegui SQL che esegue una singola istruzione Transact-SQL UPDATE basata su set su tutte le righe contemporaneamente.

Gli utenti avanzati possono progettare un flusso di dati personalizzato per l'elaborazione delle dimensioni a modifica lenta ottimizzata per dimensioni estese. Per una descrizione e un esempio di questo approccio, vedere la sezione relativa allo scenario con dimensione univoca nel white paper Project REAL: Business Intelligence ETL Design Practices.

Destinazioni

Per ottenere prestazioni migliori con le destinazioni, valutare l'opportunità di usare una destinazione di SQL Server e di testarne le prestazioni.

Destinazione SQL Server

Quando un pacchetto carica dati in un'istanza di SQL Server nello stesso computer, usare una destinazione di SQL Server. Tale destinazione è ottimizzata per caricamenti bulk ad alta velocità.

Test delle prestazioni delle destinazioni

In alcuni casi il salvataggio di dati nelle destinazioni potrebbe richiedere tempi più lunghi di quelli previsti. Per determinare se il rallentamento è causato dall'incapacità della destinazione di elaborare i dati con sufficiente rapidità, è possibile sostituire temporaneamente la destinazione con una trasformazione Conteggio righe. Se la velocità effettiva risulta notevolmente migliorata, è probabile che la causa delle prestazioni lente sia la destinazione in cui vengono caricati i dati.

Esamina le informazioni nella scheda Avanzamento

Progettazione SSIS fornisce informazioni sia sul flusso di controllo che sul flusso di dati quando si esegue un pacchetto in SQL Server Data Tools (SSDT). Nella scheda Stato sono elencati i contenitori e le attività in ordine di esecuzione, nonché l'ora di inizio e di fine, gli avvisi e i messaggi di errore per ogni contenitore e attività, inclusi quelli relativi al pacchetto stesso. Sono inoltre elencati i componenti del flusso di dati in ordine di esecuzione, nonché informazioni sullo stato, visualizzato in forma di percentuale di completamento, e il numero di righe elaborate.

Per attivare o disattivare la visualizzazione dei messaggi nella scheda Progress, attivare o disattivare l'opzione Debug Progress Reporting nel menu SSIS. La disabilitazione del report di stato consente di migliorare le prestazioni durante l'esecuzione di un pacchetto complesso in SQL Server Data Tools.

Contenuto correlato

Articoli e post di Blog

Video

Vedi anche

Strumenti per la risoluzione dei problemi di sviluppo di pacchetti
Strumenti per la risoluzione dei problemi relativi all'esecuzione dei pacchetti

  • Last updated on