Risoluzione dei problemi delle prestazioni dei pacchetti
Data aggiornamento: 15 settembre 2007
In Integration Services sono disponibili funzionalità e strumenti per la risoluzione dei problemi relativi alle prestazioni dei pacchetti. La registrazione, ad esempio, consente di acquisire informazioni di run-time sui pacchetti, mentre i contatori delle prestazioni consentono di eseguire il monitoraggio del motore flusso di dati. In questo argomento vengono fornite informazioni su queste funzionalità e sono disponibili suggerimenti per la progettazione dei pacchetti in modo da evitare problemi di prestazioni comuni.
Progettazione del flusso di dati in modo da garantire prestazioni migliori
È possibile risolvere i problemi relativi alle prestazioni dei flussi di dati nei pacchetti testando i risultati dei suggerimenti seguenti nel pacchetto:
Ottimizzazione delle query
Numerosi componenti del flusso di dati utilizzano query in operazioni di estrazione di 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 aver scelto le colonne desiderate, tuttavia, è necessario rivedere la query in modo che includa solo le colonne effettivamente utilizzate. È possibile formulare la query digitandola o utilizzando Generatore query. La rimozione delle colonne superflue rende più efficiente il flusso di dati di un pacchetto. Se le righe sono di dimensioni più piccole, infatti, è possibile inserirne un numero maggiore nel buffer e l'elaborazione di tutte le righe del set di dati risulta meno laboriosa.
[!NOTA] Quando si esegue un pacchetto in Business Intelligence Development Studio, nella scheda Stato di Progettazione SSIS vengono elencati gli eventuali avvisi, compreso l'avviso relativo alle colonne di dati rese disponibili da un'origine ma successivamente non utilizzate dai componenti del flusso di dati a valle. È possibile utilizzare la proprietà RunInOptimizedMode per rimuovere queste colonne automaticamente.
Configurazione delle proprietà dell'attività Flusso di dati
È 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, il valore di questa proprietà corrisponde a quello della variabile di ambiente TEMP. È possibile specificare altre cartelle per inserire i file temporanei in un disco rigido diverso o per suddividerli tra 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 e quello minimo è 2. Il motore, tuttavia, non utilizza un numero di thread maggiore a 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.
Informazioni sulla modalità utilizzata dal flusso di dati per ridimensionare i 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.
Modifica del ridimensionamento dei buffer
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.
[!NOTA] Le proprietà delle attività Flusso di dati illustrate in questa sezione devono essere impostate separatamente per ogni attività Flusso di dati di un pacchetto.
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.
Se i dati di origine sono ordinati perché inseriti nell'origine già ordinati o per la presenza della clausola ORDER BY nella query SELECT, è possibile specificare un hint per segnalare che i dati sono già ordinati in modo da evitare l'utilizzo di una trasformazione Ordinamento per soddisfare i requisiti di ordinamento di alcune trasformazioni a valle. Le trasformazioni Unione e Merge Join, ad esempio, richiedono input ordinati. Per specificare un hint, è necessario impostare la proprietà IsSorted dell'output di un componente del flusso di dati a monte su True e specificare le colonne chiave di ordinamento in cui i dati sono ordinati. Per ulteriori informazioni, vedere Procedura: Impostazione di attributi di ordinamento su un output.
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. Se, ad esempio, il flusso di dati utilizza una trasformazione Multicast per la copia del set di dati, è possibile ordinare il set di dati una sola volta prima che venga eseguita la trasformazione anziché ordinare più output dopo la trasformazione.
Per ulteriori informazioni, vedere Trasformazione Ordinamento, Trasformazione Unione, Trasformazione Merge join e Trasformazione Multicast.
Ottimizzazione della trasformazione Dimensione a modifica lenta
La Configurazione guidata dimensioni a modifica lenta e la trasformazione Dimensione a modifica lenta sono strumenti di utilizzo generale che soddisfano le necessità della maggior parte degli utenti. Tuttavia, il flusso di dati generato dalla procedura guidata non è ottimizzato a livello di prestazioni.
Nella trasformazione Dimensione a modifica lenta, i componenti più lenti sono in genere le trasformazioni Comando OLE DB che eseguono UPDATE rispetto a una singola riga per volta. È più frequentemente possibile migliorare le prestazioni della trasformazione Dimensione a modifica lenta sostituendo queste trasformazioni Comando OLE DB con i componenti di destinazione. Questi componenti di destinazione salvano tutte le righe da aggiornare in una tabella di gestione temporanea. È quindi possibile aggiungere un'attività Esegui SQL che esegue un solo SQL UPDATE di Transact basato su set rispetto a quello con tutte le righe contemporaneamente.
Gli utenti avanzati possono progettare un flusso di dati personalizzato per l'elaborazione della dimensione a modifica lenta che viene ottimizzata per le grandi dimensioni. Per una discussione e un esempio di questo approccio, vedere la sezione, "Unique dimension scenario", nel white paper Microsoft, Project REAL: Business Intelligence ETL Design Practices.
Ottimizzazione delle aggregazioni nella trasformazione Aggregazione
La trasformazione Aggregazione include alcune proprietà che è possibile utilizzare per migliorare le prestazioni. Se si conosce il numero esatto o approssimativo dei valori chiavi del set di dati, è possibile impostare le proprietà Keys e KeysScale. È inoltre possibile specificare il numero esatto e approssimativo delle chiavi previste per un'operazione COUNT DISTINCT impostando le proprietà CountDistinctKeys e CountDistinctScale. Grazie a queste proprietà, la trasformazione non riorganizza i totali memorizzati nella cache, con un conseguente miglioramento delle prestazioni.
Se in un flusso di dati è necessario creare più aggregazioni, è consigliabile eseguire questa operazione utilizzando una sola trasformazione Aggregazione anziché più trasformazioni. Questo consente di migliorare le prestazioni soprattutto quando le aggregazioni corrispondono a un subset di altre aggregazioni, 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. È tuttavia consigliabile adottare questo approccio solo quando la memoria rappresenta un vincolo, in quanto l'esecuzione di più aggregazioni in un'unica trasformazione Aggregazione comporta la serializzazione delle operazioni di aggregazione.
Per ulteriori informazioni, vedere Trasformazione Aggregazione.
Configurazione della regolazione dei buffer nella trasformazione Merge join
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, poiché questo valore impedisce la regolazione e, a seconda del carico di dati e della quantità di memoria disponibile, è possibile che l'esecuzione del pacchetto non venga completata.
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.
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. Per ulteriori informazioni, vedere Trasformazione Conteggio righe.
Monitoraggio delle prestazioni del pacchetto
In Integration Services sono disponibili funzionalità e strumenti per il monitoraggio delle prestazioni dei pacchetti. 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.
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.
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.
Vedere anche
Attività
Risoluzione dei problemi di sviluppo dei pacchetti
Concetti
Risoluzione dei problemi relativi all'esecuzione di pacchetti
Risoluzione dei problemi relativi a Integration Services
Guida in linea e informazioni
Cronologia modifiche
| Versione | Cronologia |
|---|---|
15 settembre 2007 |
|
17 luglio 2006 |
|
5 dicembre 2005 |
|