Miglioramento delle prestazioni del flusso di dati
In questo argomento sono inclusi alcuni suggerimenti sulla progettazione di pacchetti di Integration Services per evitare problemi di prestazioni comuni. Vengono inoltre fornite informazioni sugli strumenti e le funzionalità che è possibile utilizzare 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 il percorso per l'archiviazione temporanea dei dati del buffer, impostando la proprietà BufferTempStoragePath, e delle colonne contenenti dati BLOB (Binary Large Object), impostando la 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 utilizzato dall'attività, impostando la proprietà DefaultBufferSize, e il numero massimo di righe in ogni buffer, impostando la proprietà DefaultBufferMaxRows. Le dimensioni predefinite del buffer sono di 10 MB e quelle massime di 100 MB. Il numero di righe massimo predefinito è 10.000.
Definire il numero di thread che l'attività può utilizzare durante l'esecuzione, impostando la proprietà EngineThreads. Questa proprietà indica al motore flusso di dati il numero di thread da utilizzare. Il valore predefinito è 5. Il valore minimo è 3. Il motore, tuttavia, non utilizza più thread di quelli necessari, indipendentemente dal valore di questa proprietà. Il motore può inoltre utilizzare 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 Business Intelligence Development Studio. Questa proprietà ha la precedenza sulla 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. Queste dimensioni vengono quindi moltiplicate per il valore di DefaultBufferMaxRows, per ottenere un valore preliminare per 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 le dimensioni minime del buffer e il valore della proprietà DefaultBufferSize, il buffer viene ridimensionato per ottenere il valore più vicino possibile alle dimensioni stimate della riga per il valore di DefaultBufferMaxRows.
Quando si inizia a testare le prestazioni delle attività Flusso di dati, utilizzare i valori predefiniti di 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.
Quando è disponibile una quantità di memoria sufficiente, è consigliabile utilizzare un numero inferiore di buffer di grandi dimensioni anziché molti buffer di piccole dimensioni. In altre parole, è possibile migliorare le prestazioni riducendo il numero totale di buffer necessari per l'archiviazione dei dati e inserendo in ogni buffer il maggior numero possibile di righe. Per determinare il numero e le dimensioni ottimali dei buffer, provare a utilizzare 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 quanto non faccia la mancata ottimizzazione delle dimensioni del buffer. Per determinare il verificarsi o meno del paging, controllare il contatore delle prestazioni "Buffer con spooling" nello snap-in Prestazioni di 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 attività diverse nel pacchetto, Integration Services utilizza due proprietà: MaxConcurrentExecutables e EngineThreads.
Proprietà MaxConcurrentExcecutables
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.
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 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.
[!NOTA]
Quando si esegue un pacchetto in Business Intelligence Development Studio, 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 utilizzata successivamente dai componenti del flusso di dati a valle. È possibile utilizzare 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 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.
Origini
Origine OLE DB
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.
Trasformazioni
Utilizzare i suggerimenti inclusi in questa sezione per migliorare la prestazione delle trasformazioni Aggregazione, Ricerca fuzzy, Raggruppamento fuzzy, Ricerca, Merge Join e Dimensione a modifica lenta.
Trasformazione Aggregazione
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 la scansione 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.
Trasformazioni Ricerca fuzzy e Raggruppamento fuzzy
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.
Trasformazione 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
La trasformazione Merge join include la proprietà MaxBuffersPerInput, che suggerisce il numero massimo dei buffer da mantenere attivi contemporaneamente per ogni input. Tuttavia, il valore impostato per la proprietà MaxBuffersPerInput è solo un suggerimento. La trasformazione Merge join deve quasi sempre aumentare il numero di buffer a un valore maggiore di quello suggerito. In genere, questo aumento è necessario per i motivi seguenti:
Per ottenere dati sufficienti per l'unione da ciascun input.
Per evitare il rischio di un deadlock tra i thread che elaborano i dati.
[!NOTA]
Il numero di buffer utilizzati dalla trasformazione Merge join più diventare molto elevato, con la conseguente richiesta di una notevole quantità di memoria nel computer da parte di Integration Services.
Quando la trasformazione dispone di dati sufficienti da ciascun input e non sussiste il rischio di un deadlock dei thread, la trasformazione viene ripresa utilizzando il valore suggerito dalla proprietà MaxBuffersPerInput.
Il valore predefinito della proprietà MaxBuffersPerInput è 5, ovvero il numero di buffer ottimale nella maggior parte degli scenari. Per migliorare le prestazioni, è possibile provare ad aumentare il numero di buffer. Per ridurre la richiesta di memoria, è possibile provare a diminuire il numero di buffer. Tuttavia, un numero molto ridotto di buffer potrebbe influire negativamente sulle prestazioni e il valore 0 (zero) che disattiva tutte le limitazioni non dovrebbe essere utilizzato.
Trasformazione Dimensione a modifica lenta
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.
Destinazioni
Per ottenere prestazioni migliori con le destinazioni, valutare l'opportunità di utilizzare una destinazione SQL Server e di testarne le prestazioni.
Destinazione SQL Server
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à.
Test delle prestazioni delle destinazioni
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.
Monitoraggio delle prestazioni del pacchetto
In Integration Services sono disponibili funzionalità e strumenti per il monitoraggio delle prestazioni dei pacchetti. La registrazione, ad esempio, consente di acquisire informazioni di run-time su un pacchetto, mentre i contatori delle prestazioni consentono di eseguire il monitoraggio del motore del flusso di dati. Utilizzare i suggerimenti seguenti per determinare le parti del pacchetto che hanno un effetto più marcato sulle prestazioni.
Analisi delle informazioni nella scheda Stato
Quando si esegue un pacchetto in Business Intelligence Development Studio, in Progettazione SSIS vengono visualizzate informazioni sia sul flusso di controllo che sul flusso di dati. 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 abilitare o disabilitare la visualizzazione di messaggi nella scheda Stato, attivare o disattivare l'opzione Debug report di stato del menu SSIS. La disabilitazione del report di stato consente di migliorare le prestazioni durante l'esecuzione di un pacchetto complesso in BI Development Studio.
Configurazione della registrazione nel pacchetto
In Integration Services è disponibile un'ampia gamma di provider di log che consentono la registrazione di informazioni in fase di esecuzione dei pacchetti in tipi di file diversi oppure in SQL Server. È possibile abilitare le voci di log per i pacchetti e per singoli oggetti di pacchetto, ad esempio attività e contenitori. In Integration Services è disponibile un'ampia gamma di attività e contenitori, a ognuno dei quali è associato un set di voci di log descrittive. Un pacchetto che include un'attività Esegui SQL può ad esempio registrare una voce di log in cui è indicata l'istruzione SQL eseguita dall'attività, compresi i valori dei parametri.
Le voci di log includono informazioni svariate, ad esempio l'ora di inizio e l'ora di fine dell'esecuzione di pacchetti e oggetti di pacchetto in base a cui è possibile identificare attività e contenitori con esecuzione lenta. Per ulteriori informazioni, vedere Registrazione dell'esecuzione di pacchetti, Implementazione della registrazione nei pacchetti e Messaggi personalizzati per la registrazione.
Configurazione della registrazione per le attività Flusso di dati
L'attività Flusso di dati offre molte voci di log personalizzate che è possibile utilizzare per monitorare e regolare le prestazioni. È ad esempio possibile monitorare i componenti che potrebbero causare perdite di memoria o tenere traccia del tempo necessario per eseguire un componente specifico. Per un elenco di queste voci di log personalizzate e un output di registrazione di esempio, vedere Attività Flusso di dati.
Utilizzo dell'evento PipelineComponentTime
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.
Passaggio |
Descrizione |
|---|---|
Validate |
Il componente verifica la presenza di impostazioni di configurazione e valori di proprietà validi. |
PreExecute |
Il componente esegue un'unica elaborazione prima di iniziare a elaborare le righe di dati. |
PostExecute |
Il componente esegue un'unica elaborazione dopo avere elaborato tutte le righe di dati. |
ProcessInput |
Il componente di destinazione o di trasformazione elabora le righe di dati in ingresso ricevute da un'origine o da una trasformazione a monte. |
PrimeOutput |
Il componente di origine o di trasformazione riempie i buffer di dati da passare a un componente di destinazione o di trasformazione a valle. |
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:
356 millisecondi dedicati da "Calculate LineItemTotalCost" (3522) a ProcessInput.
79 millisecondi dedicati da "Sum Quantity and LineItemTotalCost" (3619) a ProcessInput.
16 millisecondi dedicati da "Calculate Average Cost" (3662) a ProcessInput.
125 millisecondi dedicati da "Sort by ProductID" (3717) a ProcessInput.
0 millisecondi dedicati da "Load Data" (3773) a ProcessInput.
688 millisecondi dedicati da "Extract Data" (3869) per consentire a PrimeOutput di riempire i buffer in "Output origine OLE DB" (3879).
141 millisecondi dedicati da "Sum Quantity and LineItemTotalCost" (3619) per consentire a PrimeOutput di riempire i buffer in "Output di aggregazione 1" (3621).
16 millisecondi dedicati da "Sort by ProductID" (3717) per consentire a PrimeOutput di riempire i buffer in "Output ordinamento" (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.
Monitoraggio delle prestazioni del motore flusso di dati
In Integration Services è disponibile un set di contatori delle prestazioni per il monitoraggio delle prestazioni del motore flusso di dati. È possibile, ad esempio, tenere traccia della quantità di memoria totale, espressa in byte, utilizzata da tutti i buffer e individuare eventuali componenti la cui memoria è esaurita. Un buffer è un blocco di memoria utilizzato da un componente per l'archiviazione di dati. Per ulteriori informazioni, vedere Monitoraggio delle prestazioni del motore flusso di dati.
Risorse esterne
Articolo tecnico SQL Server 2005 Integration Services: A Strategy for Performance sul sito Web all'indirizzo technet.microsoft.com
Articolo tecnico Integration Services: Performance Tuning Techniques sul sito Web all'indirizzo technet.microsoft.com
Video Tuning Your SSIS Package Data Flow in the Enterprise sul sito Web all'indirizzo msdn.microsoft.com
Video Understanding SSIS Data Flow Bufferssul sito Web all'indirizzo msdn.microsoft.com
|
.gif "Icona di Integration Services (piccola)")
Rimanere aggiornati con Integration ServicesCronologia modifiche
Aggiornamento del contenuto |
|---|
|
Vedere anche