In questo video viene descritto come migliorare le prestazioni del flusso di dati in un pacchetto Integration Services. Verranno illustrate le procedure per ottimizzare le fasi seguenti del flusso di dati:

Estrazione
Trasformazione
Caricamento

Questi suggerimenti per l'ottimizzazione delle prestazioni possono essere applicati nel corso della progettazione, dello sviluppo e dell'esecuzione del flusso di dati.

Riconoscimenti

Grazie a Thomas Kejser per il contributo al materiale per la serie di video di SQL Server SSIS: progettazione e ottimizzazione delle prestazioni. Questo video è il secondo della serie.

Grazie a Carla Sabotta e Douglas Laudenschlager per le indicazioni e i preziosi commenti e suggerimenti.

Trascrizione del video

Timestamp video	Audio
00:00	Salve, sono David Noor, Senior Development Lead per SQL Server Integration Services qui in Microsoft. Benvenuti alla visione di questo video sull'ottimizzazione del flusso di dati del pacchetto SSIS nell'organizzazione. Questa è la seconda parte di una serie in quattro parti, intitolata SSIS: progettazione e ottimizzazione delle prestazioni. Nella prima parte, Denny vi ha mostrato il modo migliore per misurare e comprendere le prestazioni dei pacchetti SSIS. In questa puntata partiremo da quella base e vedremo come migliorare le prestazioni del flusso di dati del pacchetto SSIS. Inizieremo identificando i temi comuni in ogni flusso di dati e vedremo come scegliere le parti del flusso di dati su cui intervenire in modo prioritario per provare a migliorare le prestazioni. Una volta individuati i punti in cui si presentano problemi di prestazioni, potrete utilizzare molti accorgimenti per rendere il flusso di dati più veloce ed efficiente. Esamineremo una serie di suggerimenti specifici applicabili durante la progettazione, lo sviluppo e addirittura l'esecuzione del flusso di dati. Iniziamo! La maggior parte dei flussi di dati dei pacchetti riguarda il processo di estrazione, trasformazione e caricamento di dati critici, in alcuni casi denominato ETL. Ottimizzare i flussi di dati significa ottimizzare ciascuna delle fasi: estrazione, trasformazione e caricamento. Per quanto riguarda l'estrazione, vedremo come ottimizzare i componenti di origine SSIS e le query che eseguono in modo da ottenere le migliori prestazioni con il minimo impatto sui sistemi di origine. Ottimizzare le trasformazioni nei flussi di dati significa definire l'attività da svolgere e scegliere l'approccio ottimale per realizzarla eliminando i passaggi non necessari e, talvolta, modificando le query di origine in modo che eseguano automaticamente le trasformazioni. Infine, ottimizzare il caricamento del flusso di dati significa ottimizzare i componenti di destinazione SSIS, le reti, il blocco e altri fattori che possono ostacolare il corso del flusso mentre tenta di caricare dati nella destinazione prescelta. In questo video parleremo di suggerimenti pratici che potrete utilizzare per individuare possibilità di ottimizzazione in tutte e tre fasi del flusso di dati. Prima di esaminare questi suggerimenti, voglio però ricordarvi che i problemi di prestazioni non sono in genere di facile e completa soluzione. Nessuno dei suggerimenti che vi fornirò potrà risolvere tutti i problemi di prestazioni che potreste rilevare nel vostro flusso di dati. I suggerimenti che vedremo funzionano bene nei casi comuni, ma non sarebbe una buona idea applicarli in modo generalizzato al vostro problema specifico. Il miglior modo di procedere è sempre quello di capire qual è lo scopo dei vostri flussi di dati, assicuravi che siano progettati in modo adeguato a raggiungere tale scopo, misurarne le prestazioni correnti e apportarvi modifiche in modo iterativo, verificando di volta in volta se la modifica ha migliorato le prestazioni. Inoltre, quando esaminate un problema di prestazioni, vi può facilmente capitare di concentrarvi su una specifica tecnologia o un determinato componente, magari perché li conoscete meglio di altri. Provate invece a pensare al vostro flusso di dati nel contesto di un intero ecosistema: molto probabilmente più database relazionali, una rete, sistemi operativi, file system, tanti componenti. Quante più informazioni avete su questo intero ecosistema, tanto più completo sarà il quadro delle reali problematiche di prestazioni di ogni componente e tanto più sarete in grado di apportare modifiche più efficaci e meno rischiose.
03:38	Inizieremo proprio dove inizia il flusso di dati, dall'estrazione. Quando utilizzate come origine SQL Server o un altro database con un'interfaccia bulk, dovreste provare ad aumentare le dimensioni del pacchetto. In genere, l'impostazione predefinita 4096 di SQL Server dà buoni risultati, ma poiché l'estrazione sposterà grandi quantità di dati, potrebbe essere utile aumentare questo valore. Perché questa modifica abbia effetto, dovrete chiedere al vostro amministratore di rete di abilitare "Frame jumbo" anche sulla vostra rete. Dovrete però testare l'effetto di questa modifica sul vostro pacchetto. Se utilizzate la stessa gestione connessione per operazioni bulk come nel caso di un'origine OLE DB e per operazioni su riga singola (come nel caso di un comando OLE DB), sarebbe preferibile creare una seconda gestione connessione per le operazioni con il comando OLE DB e utilizzarvi una dimensione di pacchetto minore. Come già detto, se ETL è in esecuzione su Windows 2008 e utilizzate un computer multicore con più NIC, potete migliorare leggermente le prestazioni di rete impostando l'affinità tra schede di rete e singoli core. Per maggiori informazioni cercate un post di blog chiamato Scaling Heavy Network Traffic with Windows sul sito MSDN. Una delle cose più facili da fare per ottimizzare l'estrazione è ottimizzare le query che state utilizzando. Può talora sembrare più veloce selezionare la tabella da cui effettuare l'estrazione e recuperare tutte le colonne, ma otterrete risultati decisamente migliori se selezionerete solo le colonne realmente necessarie. In questo modo non solo ridurrete il traffico di rete e la quantità di memoria utilizzata, ma probabilmente anche l'attività di I/O necessaria al server database per soddisfare la vostra query. Come mostrato in questo caso, per SQL Server potete utilizzare anche un hint per indicare di non generare blocchi condivisi nel corso dell'operazione SELECT, in modo che la query possa leggere eventuali dati dirty o di cui non è stato eseguito il commit. Riservate questo accorgimento per i casi in cui dovete assolutamente ottenere le migliori prestazioni e siete sicuri che la lettura di dati dirty sarà sempre adatta al processo ETL. Le ricerche possono essere considerate in termini di estrazione o trasformazione. Nell'uno o nell'altro caso, dovreste applicare alcune delle idee che vi ho illustrato nella diapositiva precedente. In questo caso, diventa ancora più essenziale selezionare solo le colonne necessarie, non solo per l'ottimizzazione della rete, ma anche per l'ottimizzazione della cache in memoria. Se utilizzate SQL Server 2008, una delle migliori funzionalità per le prestazioni è l'aggiunta di una nuova cache di ricerca condivisa. La condivisione di una cache di ricerca vi consente di recuperare i dati di riferimento una sola volta e riutilizzarli in più operazioni di ricerca nel pacchetto o anche in più pacchetti se salvate la cache in un file. Se avete più componenti di ricerca che fanno riferimento alla stessa tabella, questa nuova opzione vi consentirà di aumentare notevolmente la velocità dei pacchetti. Utilizzarla è molto semplice. Create una gestione connessione Cache nel pacchetto, popolate la cache utilizzando la nuova trasformazione Cache, quindi modificate le ricerche in modo che facciano riferimento a questa connessione cache per i dati di riferimento.
06:29	Passiamo alla seconda fase di ETL: la trasformazione. Nella trasformazione lavorate con dati già caricati nel flusso, cercando di prepararli alla fase di caricamento. In SSIS ogni trasformazione appare come oggetto trascinabile nell'area di progettazione, ma non tutte le trasformazioni sono uguali. Esaminiamo i tre tipi di trasformazioni che esistono in SSIS: Le trasformazioni sincrone, o basate su righe, operano su buffer di dati sul posto. Non effettuano copie dei buffer o delle righe di dati mentre attraversano il flusso, ma trasformano i dati direttamente nel buffer. Questo processo rende le trasformazioni sincrone piuttosto veloci. Esempi di trasformazioni sincrone sono Conversione dati, Colonna derivata e Ricerca. La trasformazioni asincrone, di blocco parziale, sono diverse. In questo tipo di trasformazioni i dati devono essere sempre disponibili. A questo scopo, la trasformazione copia i dati in arrivo in buffer interni e consuma memoria, talvolta in grande quantità. Man mano che i dati continuano a entrare nel flusso, queste trasformazioni procedono e scrivono i dati di output, liberando la memoria interna corrispondente che hanno occupato fino a quel momento. Una volta completato il flusso di dati, le trasformazioni liberano tutta la memoria utilizzata. Tuttavia, fino a quel momento, consumano una notevole quantità di dati. Esempi di questi tipi di trasformazioni sono Merge, Merge Join e Union All. Quando nel vostro flusso di dati sono presenti queste trasformazioni, cercate di ottimizzare. Ci sono copie ridondanti di dati? Ci sono Merge Join o Union che potreste spostare ed effettuare direttamente nel sistema di origine? Mi è capitato in passato di vedere pacchetti in cui, invece di impostare un JOIN in una query di origine, hanno recuperato tutti i dati da due tabelle tramite origini OLE DB e poi hanno utilizzato una trasformazione Merge Join nel pacchetto per realizzare il join, anche se avrebbero potuto semplicemente scrivere un'istruzione SQL Join nell'origine, con un processo che sarebbe stato molto più veloce grazie alla capacità del database di ottimizzare la query. Cercate queste opportunità per consolidare e ridurre il numero di trasformazioni asincrone. In SQL Server 2008 abbiamo apportato molti miglioramenti all'utilità di pianificazione dell'attività Flusso di dati per aumentare le prestazioni dei flussi di dati complessi e utilizzare in modo più efficiente le CPU disponibili. Se avete iniziato a utilizzare SSIS in SQL Server 2005, è possibile che abbiate introdotto una trasformazione Union All nel flusso di dati per separare ed eseguire in parallelo gli alberi di esecuzione in modo artificiale. Questo non dovrebbe essere più necessario. Grazie ai miglioramenti che abbiamo apportato a SQL Server 2008, questo accorgimento non è più necessario né consigliato. Rimuovete questi componenti Union All artificiali e dovreste ottenere migliori prestazioni. Il terzo gruppo, le trasformazioni asincrone di blocco, si potrebbero definire versioni estreme del gruppo precedente, perché devono avere a disposizione TUTTI i dati di input prima di poter scrivere i dati di output. L'utilizzo di queste trasformazioni in flussi con batch di dati di grandi dimensioni può spesso rallentare il flusso di dati in modo significativo. Se utilizzate questo tipo di trasformazioni in flussi di dati di grandi dimensioni, assicuratevi di non farlo in modo ridondante. Se avete due aggregazioni o due ordinamenti degli stessi dati nello stesso flusso, riorganizzate il pacchetto per provare a ridurre le operazioni a una sola. Ora che vi ho illustrato i suggerimenti basilari possiamo esaminare qualche suggerimento più specifico: Spesso nei flussi di dati è necessario trasformare il tipo di dati di una colonna. Provate a farlo una sola volta e utilizzate tipi di dati il più possibile limitati per mantenere compatti i buffer di dati. Anche il cast dei dati occupa tempo della CPU e se l'intero flusso di dati può utilizzare un solo tipo per una data colonna, valutate la possibilità del cast di tale colonna nell'origine dati utilizzando le funzioni SQL CAST o CONVERT o l'equivalente nel sottolinguaggio SQL dei vostri database. Ho accennato a questo concetto mostrandovi una delle diapositive precedenti: un modo per ottimizzare le trasformazioni è pensare alla loro destinazione e non esitare a utilizzare il codice SQL nelle origini se questo risulta vantaggioso. Ad esempio, se state effettuando ordinamenti sui dati di origine, valutate la possibilità di spostare queste operazioni verso la parte alta del flusso, inserendo clausole ORDER BY nel codice SQL eseguito sull'origine. Il database può essere in grado di restituire i dati ordinati in modo molto più efficiente del flusso di dati. Riservate il componente Ordinamento per i casi in cui dovete ordinare dati che sono stati uniti da più origini. In modo analogo, alcune aggregazioni possono essere effettuate più rapidamente nell'origine utilizzando una clausola GROUP BY e una funzione di aggregazione SQL. Se utilizzate SQL Server 2008 e il componente Dimensione a modifica lenta, date un'occhiata alla nuova funzionalità MERGE di SQL Server. MERGE è in grado di ottenere la maggior parte dei risultati del componente Dimensione a modifica lenta, ma con un numero inferiore di round trip sulla rete. Non dimenticate poi i vantaggi della funzionalità SQL INSERT INTO. Se il flusso di dati è semplice e l'origine e la destinazione sono sulla stessa istanza di database, potreste essere in grado di ottenere lo stesso spostamento di dati in modo notevolmente più rapido con una sola istruzione SQL e tenendo lo spostamento interamente all'interno del database. In questi casi, INSERT INTO può essere eseguito con una velocità decisamente superiore di un flusso di dati, poiché i dati non devono mai uscire dal server. Infine, se dovete eseguire caricamenti incrementali, valutate la possibile alternativa di un semplice ricaricamento. In alcuni sistemi che ho visto viene impiegato molto tempo nel rilevamento del delta per evitare di ricaricare i dati, ma le risorse di I/O e CPU utilizzate a questo scopo finiscono con il rendere il processo in definitiva più lento.
11:59	Esaminiamo adesso la fase di caricamento. Quando eseguite il caricamento in SQL Server, avete a disposizione due opzioni efficienti: La prima opzione è un componente Destinazione SQL Server. Questo componente utilizza memoria condivisa tra il flusso di dati e il motore di database per caricare i dati in modo rapido, ma funziona solo se il flusso di dati viene eseguito sempre sullo stesso computer di SQL Server. Inoltre, la destinazione SQL Server presenta alcuni limiti documentati rispetto alla gestione degli errori. L'altra opzione per un caricamento rapido in SQL Server è la destinazione OLE DB, che spesso viene eseguita quasi alla stessa velocità della destinazione SQL. In tutti questi casi, l'utilizzo di una dimensione di commit pari a 0 produrrà il caricamento più veloce. È una procedura abbastanza comune, inoltre, eliminare gli indici nel sistema di destinazione in caso di caricamenti di grandi dimensioni, ma è opportuno attenersi ad alcune linee di guida per stabilire quando l'operazione vada effettuata. Una linea guida comune è quella di scegliere di eliminare gli indici in base all'aumento percentuale che si prevede verrà causato dal caricamento e in base ai tipi di indici della tabella: Se avete un solo indice cluster sulla tabella, non eliminatelo. I dati della tabella sono ordinati in base a questa chiave e il tempo impiegato per eliminare, inserire e ricreare l'indice sarebbe equivalente a quello necessario a effettuare il caricamento con l'indice cluster incluso. Se avete un solo indice non cluster sulla tabella, valutate la possibilità di eliminarlo se il caricamento provoca un aumento di circa il 100% della dimensione dei dati. Questa è una regola empirica non precisa, ma grosso modo non vale la pena eliminare e ricreare l'indice se la dimensione della tabella non raddoppia. Se avete più indici sulla tabella, è più difficile creare una regola empirica. Personalmente, tendo a pensare in termini di intervalli di aumento del 10%. Ad esempio, per i caricamenti inferiori al 10% del volume corrente può essere conveniente lasciare gli indici. Ma la cosa migliore in questo caso è sperimentare e misurare. Se state eseguendo il caricamento in una tabella su cui sono in corso altre attività, dovrete mettere in atto una strategia. I caricamenti bulk probabilmente bloccheranno più o meno tutta la tabella di destinazione, quindi vorrete verificare che questo sia accettabile oppure utilizzare il partizionamento. Se dovete eseguire il caricamento in un database operativo, potreste impostare una partizione di caricamento, in modo che i dati operativi attuali restino attivi. Se il caricamento è lento, verificate quale altra attività è in corso sulla tabella o sulla partizione e assicuratevi che non ci siano altre attività concorrenti. Per una guida eccellente sul miglioramento delle prestazioni di caricamenti bulk e partizioni, cercate l'articolo SQLCAT intitolato The Data Loading Performance Guide sul sito MSDN. Inoltre, quando eseguite caricamenti, assicuratevi di utilizzare TRUNCATE, e non DELETE, per cancellare i dati, in modo che l'eliminazione non sia transazionale. Le destinazioni che utilizzano una connessione di rete sono inoltre soggette agli stessi problemi di rete che ho descritto in precedenza. Provate ad aumentare la dimensione del pacchetto e ad abilitare "Frame jumbo" sulla rete per ottenere prestazioni ottimali nella fase di caricamento.
15:02	Con questo si conclude la seconda parte della serie sulle prestazioni. Uno speciale ringraziamento a Thomas per tutte le informazioni che hanno costituito la base di questa serie di video e a Carla e Douglas per la loro assistenza nella creazione della serie. Per ulteriori informazioni su questi argomenti, fate riferimento a Top 10 SQL Server Integration Services Best Practices. Grazie dell'attenzione. Vi invitiamo a guardare le altre tre parti di questa serie di video intitolata SSIS: progettazione e ottimizzazione delle prestazioni. Speriamo che il video vi sia stato utile e ci auguriamo di ricevere i vostri commenti al riguardo. Nell'angolo superiore destro della home page del video è disponibile un collegamento per dare una valutazione e un commento.

Condividi tramite

Ottimizzazione del flusso di dati del pacchetto SSIS nell'organizzazione (video di SQL Server)

Riepilogo del video

Riconoscimenti

Trascrizione del video

Vedere anche

Altre risorse

Guida e informazioni

Risorse aggiuntive