Miglioramento delle prestazioni del flusso di dati

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 il funzionamento di questa proprietà, considerare un pacchetto di esempio contenente tre attività 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.

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.

Numerosi componenti del flusso di dati utilizzano query in operazioni di estrazione dei dati dalle origini o in operazioni di ricerca per la creazione di tabelle di riferimento. La query predefinita utilizza la sintassi SELECT * FROM <nomeTabella>, con cui vengono restituite 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 utilizzare, è 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 utilizzare Generatore query.

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 utilizzati da un componente a valle. Questo preordinamento può verificarsi quando la query SELECT utilizza una clausola ORDER BY o quando i dati vengono inseriti nell'origine come già ordinati. Per tali dati di origine preordinati, è possibile fornire un hint indicante che i dati sono ordinati e pertanto evitare l'utilizzo di una trasformazione Ordinamento per rispondere ai 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 del flusso di dati a monte su True.

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

Per ulteriori informazioni, vedere Procedura: Ordinamento dei dati per le trasformazioni Unione 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, utilizza 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 ulteriori informazioni, vedere Trasformazione Ordinamento, Trasformazione Unione, Trasformazione Merge join e Trasformazione Multicast.

Quando si utilizza un'origine OLE DB per recuperare dati da una vista, selezionare "Comando SQL" come modalità di accesso ai dati e immette 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.

La trasformazione Aggregazione include le proprietà Keys, KeysScale, CountDistinctKeys e 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 distinct che dovrebbero risultare da un'operazione Distinct Count, 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 utilizzano 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 utilizza la clausola GROUP BY e l'aggregazione AVG, è possibile migliorare le prestazioni combinando clausola e aggregazione in una sola trasformazione. L'esecuzione di più aggregazioni all'interno di una trasformazione Aggregazione, tuttavia, comporta la serializzazione delle operazioni di aggregazione e può pertanto influire sulle prestazioni quando è necessario calcolare più aggregazioni indipendentemente.

Per ulteriori informazioni, vedere Trasformazione Aggregazione.

Per informazioni sull'ottimizzazione della prestazione delle trasformazioni Ricerca fuzzy e Raggruppamento fuzzy, vedere il white paper Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005.

È 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.

La trasformazione Merge join include la proprietà MaxBuffersPerInput, che specifica il numero massimo dei buffer che possono essere contemporaneamente attivi per ogni input. È possibile utilizzare questa proprietà per ottimizzare la quantità di memoria utilizzata dai buffer e, di conseguenza, le prestazioni della trasformazione. Aumentando il numero dei buffer aumenta anche la quantità di memoria utilizzata dalla trasformazione, migliorando le prestazioni. Il valore predefinito di MaxBuffersPerInput è 5, ovvero il numero di buffer ottimale nella maggior parte degli scenari. Per ottimizzare le prestazioni può essere necessario utilizzare un numero di buffer lievemente diverso, ad esempio 4 o 6. Se possibile, è consigliabile evitare di utilizzare un numero di buffer molto basso. Se ad esempio si imposta MaxBuffersPerInput su 1 anziché su 5, le prestazioni subiranno un calo significativo. È inoltre consigliabile evitare di impostare MaxBuffersPerInput su 0 o un valore minore. Questo intervallo di valori indica che non viene applicata alcuna limitazione della larghezza di banda e che, a seconda del caricamento di dati e della quantità di memoria disponibile, il pacchetto potrebbe non essere completato.

Per evitare che si verifichi un deadlock, la trasformazione Merge join può aumentare temporaneamente il numero dei buffer utilizzati oltre il valore specificato da MaxBuffersPerInput. Dopo la risoluzione della condizione di deadlock viene ripristinato il valore configurato di MaxBuffersPerInput.

Per ulteriori informazioni, vedere Trasformazione Merge join.

La Configurazione guidata dimensioni a modifica lenta e la trasformazione Dimensione a modifica lenta sono strumenti di utilizzo generale in grado di rispondere alle esigenze della maggior parte degli utenti. Il flusso di dati generato dalla procedura guidata, tuttavia, non è ottimizzato per le prestazioni.

I componenti più lenti nella trasformazione Dimensione a modifica lenta sono in genere le trasformazioni Comando OLE DB che eseguono istruzioni UPDATE su una singola riga per volta. Il modo più efficace per migliorare le prestazioni della trasformazione Dimensione a modifica lenta consiste pertanto nel sostituire le trasformazioni Comando OLE DB. È possibile sostituire tali trasformazioni con componenti di destinazione che salvano tutte le righe da aggiornare in una tabella di gestione temporanea. È quindi possibile aggiungere un'attività Esegui SQL per l'esecuzione di un singola istruzione UPDATE di Transact-SQL 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.

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

In alcuni casi il salvataggio di dati nelle destinazioni potrebbe richiedere tempi più lunghi di quelli previsti. Per stabilire se ciò è dovuto a un'elaborazione lenta dei dati nella destinazione, è 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.

La voce di log personalizzata più utile è probabilmente l'evento PipelineComponentTime. Questa voce di log indica il numero di millisecondi che ogni componente del flusso di dati dedica a ognuno dei cinque passaggi principali dell'elaborazione. Nella tabella seguente sono descritti tali passaggi dell'elaborazione, i quali verranno considerati dagli sviluppatori Integration Services come i metodi principali di un oggetto PipelineComponent.

Quando si abilita l'evento PipelineComponentTime, in Integration Services viene registrato un messaggio per ogni passaggio dell'elaborazione eseguito da ogni componente. Nelle voci di log seguenti viene illustrato un subset dei messaggi registrati dal pacchetto di esempio CalculatedColumns di Integration Services:

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

Queste voci di log indicano che l'attività Flusso di dati ha dedicato la maggior parte del tempo ai passaggi seguenti, riportati in ordine decrescente:

• L'origine OLE DB denominata "Extract Data" ha dedicato 688 ms al caricamento dei dati.

• La trasformazione Colonna derivata denominata "Calculate LineItemTotalCost" ha dedicato 356 ms per l'esecuzione dei calcoli sulle righe in ingresso.

• La trasformazione Aggregazione denominata "Sum Quantity and LineItemTotalCost" ha dedicato in tutto 220 ms (141 per PrimeOutput e 79 per ProcessInput) per l'esecuzione di calcoli e il passaggio di dati alla trasformazione successiva.