Moving Media Sql Server

SQL Server metriche di performance del disco Parte 2 altre misure importanti prestazioni del disco nella parte precedente della serie di misurazioni delle prestazioni di SQL Server, abbiamo presentato le più importanti e utili metriche di performance del disco. Ora, ben mostrano le altre misure importanti prestazioni del disco corrente coda del disco Lunghezza Indica il numero di richieste di disco attualmente in attesa così come richieste in fase di manutenzione. Con riserva di ampie variazioni a meno che il carico di lavoro ha raggiunto uno stato stazionario e hanno raccolto un numero sufficiente di campioni per stabilire un modello. 1 Gli spettacoli metriche come molte operazioni di IO sono in attesa di essere scritti o letti dal disco rigido e quanti sono attualmente trattati. Se il disco rigido non è disponibile, queste operazioni sono in coda e saranno trattati quando il disco diventa disponibile. Il sottosistema intero disco ha una sola coda La lunghezza metrica corrente coda del disco in Windows Performance Monitor è disponibile sia per disco fisico e logico. In alcune versioni precedenti di Performance Monitor, questo contatore è stato chiamato Disk Queue Length La coda valore di lunghezza del disco corrente dovrebbe essere inferiore al 2 per asse del disco. Si noti che questo non è di per logica, ma per disco fisico. Se più grande, questo indica un potenziale collo di bottiglia del disco, in modo da ulteriori indagini e monitorare altri parametri del disco è raccomandato. Inizia con il monitoraggio del disco Time (spiegato di seguito). picchi frequenti dovrebbero essere studiati sistemi di array di dischi, come RAID o SAN avere un gran numero di dischi e controller, il che rende le code su tali sistemi più breve. Poiché il doesnt metrica indica l'accodamento per disco, ma per l'intero array, alcuni amministratori di database ritiene che il monitoraggio Current Disk Queue Length su array di dischi non è necessario altro scenario in cui corrente coda del disco La lunghezza può essere fuorviante è quando i dati vengono memorizzati nella cache del disco. Esso verrà segnalato come messe in coda per la scrittura e quindi la coda valore di lunghezza del disco corrente sarà superiore a quello effettivo Lunghezza media coda del disco La lunghezza metrica media coda del disco mostra le informazioni simile a Current Disk Queue Length. solo il valore non è attuale, ma media su un periodo di tempo specifico. La soglia è lo stesso per la metrica precedente fino a 2 per disco. Per i sistemi a disco, il valore consigliato è inferiore al 2 per unità disco singolo in un array. Per esempio, in un array di 6 dischi il valore Current Disk Queue Length di 12 significa che la coda è di 2 per ogni disco ci sono altri due parametri simili a media coda del disco Lunghezza 8211 Average Disk Leggi Lunghezza coda e media scrittura del disco Lunghezza coda. Come i loro nomi indicano che mostrano la lunghezza media della coda per le operazioni in attesa di disco da leggere o scrivere su disco Time Questo contatore indica un problema del disco, ma va osservato in concomitanza con il contatore Lunghezza corrente coda del disco per essere veramente informativo. Ricordiamo anche che il disco potrebbe essere un collo di bottiglia prima della Tempo disco raggiungendo 100 2 La metrica Tempo disco indica come occupato il disco è la manutenzione leggere e scrivere le richieste, ma come si è detto, la sua non è una chiara indicazione di un problema, come i suoi valori può essere normale, mentre C'è un grave problema di prestazioni del disco. Il suo valore è il valore di Lunghezza media coda del disco rappresentato in percentuale (cioè moltiplicato per 100). Se media coda del disco La lunghezza è 1, Disk Il tempo è 100 Che cosa può essere fonte di confusione è che i valori Tempo disco possono essere più di 100, che costerà logico. Questo accade se il valore di Lunghezza media coda del disco è maggiore di 1. Se media coda del disco La lunghezza è di 3, Disk Il tempo è 300, che non significa che i processi stanno utilizzando 3 volte più tempo del disco di disposizione, né che vi è un collo di bottiglia Se si dispone di un array del disco rigido, il tempo totale del disco per tutti i dischi viene mostrato, senza l'indicazione di quanti dischi sono disponibili e quale disco sta avendo la più alta tempo disco. Ad esempio, disco Tempo pari a 500 potrebbe indicare buone prestazioni (nel caso in cui si dispone di 6 dischi), o molto male (nel caso in cui si ha solo 1 disco). Non si può dire senza conoscere l'hardware della macchina come questo contatore può essere fuorviante, alcuni amministratori di database non usano come ci sono altri parametri più semplici e indicativi che mostrano le prestazioni del disco Se il valore è superiore al 90 per disco, è necessario ulteriori indagini. In primo luogo, controllare il valore Lunghezza corrente coda del disco. Se la sua superiore alla soglia (2 per ogni disco fisico), monitorare se i valori elevati si verificano di frequente. Se la macchina non viene utilizzata solo per SQL Server, altre applicazioni ad alta intensità di risorse potrebbero causare colli di bottiglia del disco, quindi le prestazioni di SQL Server saranno sofferenza. Se questo è il caso, è possibile spostare queste applicazioni per un'altra macchina e utilizzando una macchina dedicata per SQL Server soltanto Se questo non è il caso, o non può essere fatto, è possibile spostare alcuni dei file a un altro database disco o archivio macchina database e backup del log delle transazioni, utilizzando un disco più veloce, o l'aggiunta di ulteriori dischi ad un array Tempo lettura disco e la scrittura del disco Tempo il Tempo lettura disco e scrittura su disco metriche di tempo sono simili a disco Tempo. solo mostrando le operazioni letti o scritti su disco, rispettivamente. Essi sono in realtà la lunghezza media del disco Leggi coda e media Disk scrivere i valori lunghezza della coda presentati in percentuale. I valori di questi parametri spettacolo può essere altrettanto fuorviante, in quanto Disk volta su un sistema di allineamento di tre dischi, se un disco legge 50 del tempo (Tempo lettura disco 50), l'altro si legge 85 del tempo, e il terzo è inattivo, Tempo lettura disco è 135 e media disk Leggi Lunghezza coda 1.35. A prima vista, Tempo lettura disco pari a 135 si presenta come un problema, ma la sua non. Esso non significa che i dischi sono occupati 135 del tempo. Per ottenere un valore reale, si dovrebbe dividere il valore con il numero di dischi e youll ottenere 1363 45, che indica le prestazioni normale tempo di inattività Il disco è inattivo quando non l'elaborazione di leggere e scrivere le richieste di questa misura la percentuale di tempo il disco era inattiva durante l'intervallo di campionamento. Se questo contatore scende al di sotto del 20 per cento, il disco di sistema è saturo. Si può considerare la sostituzione del disco di sistema corrente con un sistema di disco più veloce. 3 Se il valore è inferiore a 20, il disco non è in grado di servire tutti leggere e scrivere le richieste in modo tempestivo. Prima di optare per la sostituzione del disco, verificare se la sua possibile rimuovere alcune applicazioni per un'altra macchina Free Space Oltre Performance Monitor di Windows, questa metrica è disponibile in Windows Explorer nelle schede di computer e disco Properties. Mentre Performance Monitor mostra la percentuale di spazio libero su disco disponibile, Windows Explorer mostra la quantità di GB Questa misura la percentuale di spazio libero sull'unità disco logica selezionata. Prendere nota se questo scende al di sotto del 15 per cento, come si rischia di rimanere a corto di spazio libero per il sistema operativo per memorizzare i file critici. Una soluzione ovvia è quella di aggiungere più spazio su disco. 3 Se il valore mostra picchi improvvisi senza ovvie ragioni, ulteriori indagini è richiesto differenza della maggior parte della memoria e del processore di SQL Server metriche di performance, le metriche del disco possono essere molto ingannevole. Potrebbero non indicano chiaramente un problema di prestazioni il loro valore potrebbe essere ok, quando in realtà c'è un problema serio del disco, mentre i loro stranamente alti valori potrebbe mostrare prestazioni normali, come dimostrano i valori per una serie di dischi. Poi si tratta di parametri di matrice, la conoscenza configurazione hardware è necessario leggere correttamente. Nonostante questi aspetti negativi metriche del disco, sono necessari per le prestazioni di SQL Server troubleshootingSQL Server IO prestazioni Tutto quello che c'è da considerare prestazioni di SQL Server IO è fondamentale per le prestazioni complessive. L'accesso ai dati su disco è molto più lento nella memoria, in modo da ottenere il massimo dal disco locale e SAN è fondamentale. C'è un sacco di consigli sul web e nei libri di prestazioni di SQL Server IO, ma io havent trovato una singola fonte messa in vendita di tutto per prendere in considerazione. Questo è il mio tentativo di portare tutte le informazioni in un unico posto. Quindi, ecco un elenco di tutto ciò che mi viene in mente che possono influire sulle prestazioni IO. Ho ordinato andando alle dischi fisici e spostando il filo al server e infine lo schema di codice e database. Impossibile disco Quando un disco si guasta in un array di dischi avrà bisogno di essere sostituito. L'impatto sulle prestazioni prima della sostituzione dipende dalla configurazione di array di storage e RAID utilizzato. RAID 5 e RAID 6 uso distribuiti parità, e questo la parità viene utilizzato per calcolare la legge quando un disco non riesce. Leggere le prestazioni perde il vantaggio di lettura da più dischi. Questo è anche vero, anche se in misura minore, su RAID 1 (mirroring) array. Legge perde il vantaggio di leggere da più strisce per i dati sul disco in errore, e le scritture possono essere leggermente più lento a causa dell'aumento dei tempo di ricerca medio. Scrivi cache quando una transazione è impegnata, la scrittura per il log delle transazioni deve completare prima che la transazione viene contrassegnata come commessi. Ciò è essenziale per garantire l'integrità delle transazioni. E 'usato per essere che la cache di scrittura non è stato consigliato, ma un sacco di recenti array di storage avere cache batteria tampone che sono certificati per l'utilizzo con SQL Server. Se si ha la possibilità di variare la distribuzione della memoria tra lettura e scrittura della cache, cercare di allocare il più possibile per la cache di scrittura. Questo perché SQL Server esegue una propria cache di lettura attraverso il pool di buffer, in modo che qualsiasi cache di lettura supplementare sul controller del disco non ha alcun beneficio. Thin provisioning Il thin provisioning è una tecnologia fornito da alcune reti SAN per cui la memorizzazione su disco effettivamente utilizzato è appena sufficiente per i dati, pur figurando al server di essere pieno di dimensioni, con un sacco di spazio libero. Quando il disco totale assegnato a tutti i server supera la quantità di storage fisico, questo è noto come over-provisioning. Alcuni fornitori di SAN cercano di affermare che le prestazioni non è interessato, ma questo non è sempre vero. Ho visto questo problema di recente su un array 3PAR. Letture sequenziali erano significativamente più lenta su LUN con thin provisioning. Il passaggio a LUN provisioning di spessore più che raddoppiato la velocità di lettura sequenziale. Dove sono i dischi Sono in cui si pensa che siano E 'perfettamente possibile essere collegato a un array di storage, ma anche per le richieste di IO di passare attraverso tale array ad un altro. Questo a volte è fatto come un modo economico per aumentare lo spazio su disco - con l'hardware esistente che viene sottoutilizzato è meno costoso rispetto all'acquisto di più dischi. Il problema è che questo introduce un altro elemento nel percorso ed è dannoso per le prestazioni - e il DBA può anche non essere a conoscenza di esso. Assicuratevi di sapere come il SAN è configurato. Smart Tiering Questo si chiama cose diverse da diversi fornitori. L'array di archiviazione consisterà di due o più tipi di disco, di diversa prestazioni e costi. Ci sono i più lenti dischi 10K - questi sono i più economici. Poi ci sono i 15K dischi. Questi sono più veloci ma più costosi. E poi ci possono essere alcuni SSD super-veloci. Questi sono anche più costosi, anche se il prezzo sta scendendo. Intelligente tiering migra i dati tra i vari livelli in modo che i dati più comunemente si accede si trova sulla memorizzazione più veloce, mentre i dati meno comunemente usato scende verso il basso per l'archiviazione più lento. Questo va bene in linea di principio, ma tu sei il DBA. Si dovrebbe già sapere che i dati devono essere accessibili in modo rapido e che può essere più lento. Vuoi davvero un algoritmo prendere questa decisione per voi e le attività di manutenzione periodica può confondere il tutto comunque. Si consideri un carico di indice ricostruisce esecuzione durante la notte. Consente di supporre che l'ultima base di dati da elaborare è un database di archiviazione - vuoi questo è essere monopolizzavano l'SSD quando gli utenti login prima cosa al mattino, mentre il database mission critical sta languendo nel livello inferiore Questa è una semplificazione eccessiva, ovviamente. Gli algoritmi più livelli sono più sofisticati di questo, ma il mio punto stand. Si dovrebbe decidere le priorità per i dati di SQL Server. Non lasciare che i fornitori di SAN (o gli amministratori di storage) a convincere il contrario. replica livello livello di storage Storage Replication è una funzione di disaster recovery che copia blocco di dati di livello dal primario SAN ad un altro - spesso situati in un centro dati indipendente. I venditori SAN sostengono alcun impatto sulle prestazioni, e questo è vero se configurato correttamente. Ma ho visto la replica di mal configurato hanno un grave impatto sulle prestazioni. Un cliente ha subito un paio di anni di scarse prestazioni IO. Quando mi unii a loro ho chiesto se la replica dello storage era responsabile. Mi è stato detto di non essere così sciocco - il venditore ha verificato e non è il problema - deve essere di per sé un paio di mesi dopo SQL Server sono stato contattato ancora una volta - che avevano spento la replica, mentre nel processo di passaggio a una nuova data center e indovinate un po 'Scrivi la latenza migliorata di un ordine di grandezza. Lasciatemelo ripetere questo è stato causato da una cattiva configurazione e la maggior parte di replica dello storage non influisce notevolmente le prestazioni. Ma la sua un'altra cosa da considerare se siete alle prese con prestazioni di SQL Server IO. Host Bus Adapter Controllare che la SAN e HBA firmware sono compatibili. A volte, quando si aggiorna una SAN, gli HBA sui server sono trascurati. Questo può causare errori irregolari, o anche fare il deposito inaccessibile. Date un'occhiata alla profondità di coda HBA. Un difetto comune è 32, che non può essere ottimale. Alcuni studi hanno dimostrato che l'aumento di questo per 64 o superiore può migliorare le prestazioni. Si potrebbe anche peggiorare le cose, a seconda del carico di lavoro, SAN marca e modello, layout del disco, ecc Quindi testare a fondo, se potete. Alcuni amministratori di storage scoraggiare la modifica della coda HBA come pensano tutti vorranno lo stesso sui loro server e storage array saranno sommersi. E theyre destra, troppo persuaderli che è solo per te. Promettimi di non dirlo a nessuno. Qualunque cosa. Basta avere la profondità di coda in più se si pensa che andrà a beneficio delle prestazioni. Troppi server quando una società forchette su una piccola fortuna su una storage area network, vogliono ottenere rapporto qualità-prezzo. Così, naturalmente, ogni nuovo server che arriva viene collegato in modo che possa fare uso di tutto quello spazio bel disco. Questo va bene fino a un paio di server comincerà a emettere un sacco di richieste di IO e altri utenti si lamentano di un rallentamento delle prestazioni. Questa è una cosa che vedo più volte in tanti clienti, e non esiste una soluzione facile. Il doesnt società vuole o non può permettersi di acquistare un altro SAN. Se pensi che questo è un problema per voi, mettere un programma insieme di tutti i lavori - su tutti i server - e cercare di riprogrammare alcuni in modo che il carico di lavoro è distribuito in modo più uniforme. Partizione di allineamento e formattazione ho brevemente ricordare l'allineamento delle partizioni, anche se Windows 2008 utilizza un offset di 1 MB di default quindi questo è un problema minore rispetto al passato essere. Io non sono convinto che un sacco di reti SAN moderni beneficiare molto dalla pratica. Ho eseguito un test su un EVA a pochi anni fa e ho trovato solo un miglioramento 2. Tuttavia, una piccola percentuale è ancora la pena di lottare. Purtroppo si dovrà abbattere i volumi e ricreare le partizioni se questo deve essere fissato su un sistema esistente. Questo non è probabilmente vale la fatica a meno che non si sta cercando per ogni ultimo pollice di prestazioni. La formattazione è un'altra cosa che dovrebbe essere eseguito correttamente. SQL Server archivia i dati nelle pagine 8KB, ma questi vengono recuperati in blocchi di 8, chiamati estensioni. Se i dischi sono formattati con unità di allocazione 64KB, questo può avere un vantaggio significativo delle prestazioni. Multipathing Se non si utilizza disco locale allora si dovrebbe avere una certa ridondanza integrato nel sottosistema di archiviazione. Se si dispone di una SAN si dispone di una complessa rete di HBA, tessuto, switch e controller tra SQL Server e dei dischi. Ci dovrebbero essere almeno due HBA, interruttori, ecc e questi dovrebbero essere tutti collegati tra loro in modo tale che ci sono percorsi multipli per i dischi. Questa ridondanza è in primo luogo per l'alta disponibilità, ma se il multipathing è stato configurato come activeactive si può vedere i benefici di prestazioni pure. Network Attached Storage Dal SQL Server 2008 R2 è stato possibile creare, ripristinare o collegare un database in una condivisione di file. Questo ha un certo numero di possibili utilizzi, e in particolare per gli ambienti DevTest può rendere più facile la gestione della capacità, ed effettuare lo spostamento dei database tra server molto più veloce. La domanda da porsi, però, è quotDo si vuole veramente questo in Performance productionquot non sarà buono come unità locali o di SAN. Ci sono componenti aggiuntivi nella catena, quindi affidabilità possono non essere così buona. E utilizzando la rete, i dati utilizza la stessa infrastruttura, come tutto il resto del traffico TCPIP, che ancora una volta potrebbe influire sulle prestazioni. Ma c'è una buona notizia Mentre la disponibilità è ancora una preoccupazione, miglioramenti in SMB su Windows Server 2012 (e tramite un aggiornamento a Windows Server 2008 R2) hanno reso molto più velocemente. Ho visto una citazione da un dipendente di Microsoft da qualche parte che ha sostenuto 97 delle prestazioni di storage locale. Non posso trovare la citazione ora, e non mi ricordo se era misurazione della latenza o il throughput. La frammentazione del disco Quanto spesso si usa lo strumento di deframmentazione dischi sul PC per analizzare e deframmentare l'unità C: Quanto spesso si fa a controllare la frammentazione sui dischi sui server SQL per la maggior parte delle persone che non è neanche lontanamente spesso, scommessa Ill. Eppure volume di frammentazione è altrettanto dannoso per le prestazioni di SQL Server in quanto è al vostro PC. È possibile ridurre la probabilità di frammentazione del disco in diversi modi: i dati di pre-dimensioni e file di log, piuttosto che fare affidamento su auto-crescita con incrementi Set di auto-crescita a valori sensibili invece del evitare il default 10 restringimento di dati e file di log Mai, mai utilizzare l'opzione di database autoshrink Assicurarsi dischi sono dedicati a SQL Server e non condivisi con altre applicazioni È possibile controllare la frammentazione utilizzando lo stesso strumento come sul PC. Utilità di deframmentazione dischi è disponibile su tutte le versioni server di Windows. Un altro modo per controllare è tramite la classe Win32Volume in WMI. Questo bit di PowerShell riporta la percentuale di frammentazione del file per tutti i volumi su un determinato server. Se si dispone di una significativa frammentazione ci sono un paio di modi per risolvere il problema. Mia opzione preferita è la seguente, ma richiede un certo tempo morto. Arrestare i servizi SQL il backup dei file sul disco (in particolare mdf, NDF e ldf - è meglio che curare) Eseguire lo strumento di deframmentazione dischi di Windows avviare i servizi SQL Controllare il log degli errori per garantire l'assenza di errori durante l'avvio Run CHECKDB contro tutti i database ( tranne tempdb). Non ho mai visto la causa della corruzione strumento di deframmentazione, ma non potete essere troppo attenti Un'altra opzione che pretende molto richiedono tempi di inattività è quello di utilizzare uno strumento di terze parti come Diskeeper. Questo può essere molto efficace a fissare e prevenire la frammentazione del disco, ma costa denaro e utilizza un driver di filtro - vedere i miei commenti qui sotto. Filtrare Driver Un driver di filtro è un pezzo di software che si trova tra una richiesta di IO e la scrittura su disco. Esso consente la scrittura per essere esaminato e respinto, modificato o controllato. Il tipo più comune di driver di filtro viene installato un software anti-virus. Se non si desidera un software anti-virus controllando ogni singola scrittura ai file di database. Tu non anche lo vogliono controllare i backup sia, o scrive nel log degli errori, o traccia di default. Se si dispone di software antivirus installato, è possibile specificare le esclusioni. Escludere tutte le cartelle utilizzate da SQL Server, più le unità utilizzate dai dati e file di log, più le cartelle utilizzate per i backup. Ancora meglio è quello di disattivare il controllo AV on-line, e pianificare una scansione in un momento tranquillo. OLTP e BI sullo stesso server È raro trovare un sistema che è puramente OLTP. La maggior parte avrà una sorta di elemento di segnalazione pure. Purtroppo, i due tipi di carico di lavoro non sempre coesistono felicemente. Ive stato letto un sacco di articoli di Joe Chang, e in un articolo che spiega il motivo per cui questo è il caso. In sostanza, i piani di query OLTP recuperare file in piccoli lotti (meno di una soglia di 25 righe) e queste richieste IO sono gestite in modo sincrono dal motore di database, il che significa che aspettano i dati da recuperare prima di continuare. Grandi carichi di lavoro BI e le query di reporting, spesso con piani paralleli, emettere richieste asincrone IO e sfruttare appieno la capacità HBA di coda richieste. Di conseguenza, le richieste OLTP devono mettersi in fila dietro le richieste di BI, causando prestazioni OLTP a degradare in modo significativo. Auto-grow e Instant File di inizializzazione E 'bene avere aumento automatico delle dimensioni abilitato, solo per precauzione, anche se si dovrebbe anche dati pre-size e file di log in modo che è raramente necessaria. Tuttavia, cosa succede se un file di dati cresce e non si dispone di inizializzazione file immediata abilitato Soprattutto se l'auto-crescere è troppo grande. Tutto IO contro il file deve aspettare per la crescita di file per completare, e questo può essere segnalato nei quotIOs infami presi più di 15 secondi per completequot messaggio nel log degli errori. inizializzazione istantaneo non aiuterà con la crescita di registro, in modo da assicurarsi di registro incrementi di auto-crescita non sono troppo alti. Per ulteriori informazioni su inizializzazione file immediata e su come attivarlo, vedere questa inizializzazione collegamento file di database. E mentre in tema di auto-crescere, vedere la sezione di riempimento proporzionale, qui di seguito. Log delle transazioni delle prestazioni Per quanto tempo il log delle transazioni scrive prendere meno di 1ms Più di 5 ms Guarda le statistiche virtuali di file, contatori delle prestazioni, o la WRITELOG tempo di attesa per vedere se la latenza di registro di scrittura è un problema per voi. Scrive per il log delle transazioni sono sequenziali, e quindi la testina di scrittura sul disco dovrebbe essere idealmente dove è stato dall'ultimo scrittura di registro. Questo non significa il tempo di ricerca, e tempi di scrittura incredibilmente veloce. E dal momento che una transazione non può commettere fino a quando il registro è indurito su disco, si basano su questi veloce scrive per un sistema performante. Consigli per anni è stato per il log delle transazioni per ogni database di essere sul proprio disco. E questo consiglio è ancora buono per disco locale, e per alcuni array di storage. Ma ora che un sacco di reti SAN hanno una propria cache di scrittura supportata da batteria, questo consiglio non è così critica come ha usato essere. A condizione che la cache è abbastanza grande per far fronte alle raffiche di picco di attività di scrittura (e vedere i miei commenti precedenti circa l'assegnazione di più di cache di scrittura che a letture) si ottiene una latenza molto bassa. Così che cosa se non avete il lusso di un mega-bucks SAN e carichi di cache di scrittura Poi il consiglio thats di stato in giro dal momento che il 1990 è ancora valido: file di log di una transazione per database sul proprio disco RAID 1, RAID 10 o RAID 01 Quindi, supponendo che si sono soddisfatti del layout del file di log, che altro si potrebbe rallentare il registro scrive file di log virtuale Anche se un log delle transazioni viene scritto in sequenza, il file stesso può diventare frammentato internamente. Quando viene creato è costituito da blocchi diversi chiamati file di log virtuali. Ogni volta che viene coltivato, manualmente o automaticamente, vengono aggiunti diversi file di registro virtuali. Un log delle transazioni che cresce più volte può finire con migliaia di file di log virtuali. Avere troppi VLF possono rallentare la registrazione e possono anche rallentare i backup dei log. È inoltre necessario fare attenzione ad evitare VLF che sono troppo grandi. Un file di log virtuale inattivo non viene cancellato fino al raggiungimento del fine e il successivo inizia ad essere utilizzato. Per il modello di recupero completo, questo non accade fino a quando il backup del log successivo. Così un backup del log improvvisamente hanno molto più lavoro per, e può causare problemi di prestazioni, mentre si svolge. La risposta per un grande registro delle transazioni è quello di impostare una dimensione iniziale di massima 8000MB, e poi manualmente crescere in pezzi di 8000MB fino alla dimensione di destinazione. Ciò si traduce in massima dimensione VLF di 512 MB, senza creare un eccessivo numero di VLF. Nota: questo consiglio è solo per la crescita manuale. Non auto crescere da 8000MB Tutte le transazioni nel database si ferma mentre lo spazio extra viene inizializzato. Aumento automatico dovrebbe essere molto più piccolo - ma cercare di dimensionare manualmente il file in modo che crescono auto è improbabile che possa essere necessario. Log Manager Limiti Il motore di database pone dei limiti sulla quantità di log che può essere in volo in qualsiasi momento. Questo è un limite per-database e dipende dalla versione di SQL Server in uso. SQL Server limita il numero di IO in sospeso e MB al secondo. I limiti variano con la versione e se a 32 bit o 64 bit. Vedere Diagnostica di log delle transazioni Problemi e limiti del Log Manager per maggiori dettagli delle prestazioni. Per questo la latenza di scrittura deve essere il più basso possibile. Se si prende 20ms scrivere sul registro delle transazioni, e si sono limitati a 32 IO in volo alla volta, il che significa un massimo di 1600 transazioni al secondo, ben al di sotto quello che un sacco di database OLTP di grandi dimensioni richiede. Questo sottolinea anche l'importanza di mantenere delle transazioni di dimensioni ridotte, come una grande operazione, concettualmente, potrebbe contenere fino altre operazioni mentre si impegna. Se si pensa che questi limiti sono che interessano registro delle prestazioni in scrittura nei database ci sono diversi modi per affrontare il problema: I lavori per aumentare le prestazioni in scrittura di registro Se si è minimamente le operazioni di login è possibile passare il database per utilizzare il modello di recupero di massa registrate. Attenzione però - un backup del log contenente un'operazione di registrazione minima deve essere ripristinato in pieno. Punto nel tempo di ripristino non è possibile. Dividere un database ad alto volume in 2 o più database, come i limiti di registro si applicano per database non sequenziale Registro attività Ci sono azioni eseguite dal motore di database che spostare la testina di scrittura dalla fine del file di registro. Se le operazioni sono ancora in fase di commessi mentre questo accade, si ha un cercano in testa e registro delle prestazioni peggiora. Le operazioni che leggono dai file di log includono rollback delle operazioni di grandi dimensioni, backup dei log e la replica (l'agente di lettura log). C'è poco che si possa fare la maggior parte di questi, ma evitando grandi rollback è qualcosa che dovrebbe essere affrontato alla progettazione e fase di sviluppo di un'applicazione. Proporzionale riempimento tabelle molto attivi possono essere inseriti in un gruppo di file che ha più file di dati. Questo può migliorare le prestazioni in lettura se sono su diversi dischi fisici, e può migliorare le prestazioni in scrittura, limitando contesa nelle pagine di allocazione (particolarmente vero per tempdb). Si perde un po 'di vantaggio, però, se non approfittare della algoritmo di riempimento proporzionale. riempimento proporzionale è il processo mediante il quale il database tenta di allocare nuove pagine in proporzione alla quantità di spazio libero in ogni file di dati nel gruppo di file. Per ottenere il massimo beneficio assicurarsi che ogni file è la stessa dimensione, ed è sempre coltivata dallo stesso incremento. Questo è sia per la crescita manuale e automatico. Una cosa da essere consapevoli è come funziona la crescita automatica. SQL Server fa del suo meglio per riempire le file alla stessa velocità, ma uno sarà sempre riempire appena prima degli altri, e questo file sarà poi automaticamente crescerà da sola. Questo poi diventa più nuove assegnazioni di pagina rispetto agli altri e diventa un hotspot temporaneo fino a quando gli altri anche auto a crescere e recuperare il ritardo. Questo è improbabile che causare problemi per la maggior parte delle basi di dati, anche se per tempdb potrebbe essere più evidente. Flag di traccia 1117 fa sì che tutti i file di dati in un gruppo di file per crescere insieme, per cui vale la pena considerare se questo è un problema per voi. Personalmente avrei preferito dimensionare manualmente i file in modo che la crescita automatica isnt necessario. Configurazione tempdb Consente di iniziare con un paio di cose che tutti d'accordo su: file di tempdb devono essere immessi sul memorizzazione più veloce disponibile. SSD locale è l'ideale, e da SQL Server 2012 questo è ancora possibile in un cluster pre-formato dei dati e dei file di registro, come la crescita auto può causare problemi di prestazioni, mentre si verifica Nuovi oggetti temporanei vengono creati per tutto il tempo, in modo da contese nel GAM , pagine SGAM e PFS può essere un problema in alcuni ambienti e ora alcune differenze di opinione: C'è un sacco di consigli in tutto il web per creare un file di dati tempdb per core per ridurre l'allocazione contesa. Paul Randall non è d'accordo (Un mito DBA di SQL Server al giorno: (1230) tempdb dovrebbe sempre avere un file di dati per core di processore). Dice che troppi file può effettivamente peggiorare le cose. La sua soluzione è quella di creare un minor numero di file e di aumentare solo se necessario C'è di più consigli, più volte ripetuto, per separare i file di tempdb da altri database e metterli sui propri assi fisici. Joe Chang non è d'accordo e ha un buon argomento per l'utilizzo del pool comune di dischi. (Dati, log e il posizionamento di file Temp). Ill lasciare decidere cosa fare opzione di database AutoShrink Il AutoShrink è stato intorno sin da quando ho iniziato ad usare SQL Server, causando un sacco di problemi di prestazioni per le persone che hanno permesso senza pienamente rendersi conto di quello che fa. Spesso un'applicazione di terze parti verrà installato un database con questa opzione abilitata, e il DBA può non notarlo fino a tardi. Allora perché è cattivo Due motivi: è sempre utilizzato in combinazione con auto di crescere, e il ciclo continuo di crescere-shrink-crescere provoca un enorme quantità di frammentazione del disco fisico. Ive ha già coperto questo argomento in precedenza in questo articolo Mentre esegue la strizzacervelli c'è un sacco di ulteriore IO, che rallenta il sistema per tutto il resto disattivarlo. Allocare spazio sufficiente per i dati ei file di log, e dimensioni di conseguenza. E non dimenticate di risolvere tutti che la frammentazione mentre siete a questo. Memoria insufficiente Questo è un articolo su prestazioni di SQL Server IO, non la memoria. Così io non voglio coprire in dettaglio qui - che è un argomento per un altro articolo. Voglio solo ricordare che SQL Server ama memoria - più sono e meglio. Se l'intero database (s) si inserisce nella memoria youll avere un sistema molto più veloce, evitando tutto ciò che lento IO. La mancanza di memoria può portare a pagine sporche essere lavata su disco più spesso per far spazio a più pagine lette. La mancanza di memoria può anche portare ad un aumento tempdb IO, come più piani di lavoro per le operazioni di ordinamento e hash hanno per lo spooling sul disco. In ogni caso, il punto di questa sezione è davvero a fare una dichiarazione: Riempire i server con la quantità di memoria si può permettere, e tanto quanto l'edizione di SQL Server e Windows in grado di affrontare. SQL Server 2014 ha una nuova funzione che permette alcune tabelle da conservare nella memoria, e accessibili tramite le stored procedure in modo nativo compilati. Alcuni riprogettazione di alcuni il codice esistente può essere necessaria per approfittare di questo, ma sembra che un grande incremento delle prestazioni per quei sistemi OLTP che iniziano ad usarlo. Alta L'utilizzo di tempdb tempdb può essere un grande consumatore di IO e può influenzare le prestazioni complessive se utilizzato eccessivamente. Vale la pena di guardare le varie ragioni per il suo utilizzo, e di esaminare il sistema per assicurarsi che avete ridotto al minimo questi il ​​più lontano possibile. oggetti temporanei creati dagli utenti il ​​più comune di questi sono tabelle temporanee, le variabili di tabella e cursori. Se vi è un alto tasso di creazione questo può portare alla pagina allocazione contesa, pur aumentando il numero di file di dati di tempdb può in parte alleviare questo. I processi che creano molto grandi tabelle temporanee o le variabili di tabella sono un grande no-no, in quanto questi possono causare un sacco di IO. Oggetti interni Il motore di database di creare tavoli da lavoro in tempdb per la gestione di hash join, l'ordinamento e lo spooling di set di risultati intermedi. Quando le operazioni di ordinamento o hash join bisogno di più memoria di quanto è stato concesso che si riversano su disco (utilizzando tempdb) e vedrete avvertimenti Hash e ordinamento avvertenze nella traccia di default. I originally wrote a couple of paragraphs about how and why this happens and what you can do to prevent it, but then I found this post that explains it much better - Understanding Hash, Sort and Exchange Spill Events . Version Store The third use of tempdb is for the version store. This is used for row versioning. Row versions are created when snapshot isolation or read committed snapshot option is used. They are also created during online index rebuilds for updates and deletes made during the rebuild and for handling data modifications to multiple active result sets (MARS). A poorly written application (or rogue user) performing a large update that affects many thousands of rows when a row versioning based isolation level is in use may cause rapid growth in tempdb and adversely impact IO performance for other users. Table and Index Scans A table scan is a scan of a heap. An index scan is a scan of a clustered or non-clustered index. Both may be the best option if a covering index does not exist and a lot of rows are likely to be retrieved. A clustered index scan performs better than a table scan - yet another reason for avoiding heaps But what causes a scan to be used in the first place, and how can you make a seek more likely Out of date statistics Before checking indexes and code, make sure that statistics are up to date. Enable quotauto create statisticsquot. If quotauto update statisticsquot is not enabled make sure you run a manual statistics update regularly. This is a good idea even if quotauto update statisticsquot is enabled, as the threshold of approximately 20 of changed rows before the auto update kicks in is often not enough, especially where new rows are added with an ascending key. Index Choice Sometimes an existing index is not used. Have a look at improving its selectivity, possibly by adding additional columns, or modifying the column order. Consider whether a covering index could be created. A seek is more likely to be performed if no bookmark lookups will be needed. See these posts on the quottipping pointquot by Kimberly Tripp. The Tipping Point . Inefficient TSQL The way a query is written can also result in a scan, even if a useful index exists. Some of the reasons for this are: Non-sargable expressions in the WHERE clause. quotsargquot means Simple ARGument. So move calculations away from the columns and onto the constants instead. So for example, this will not use the index on OrderDate: WHERE DATEADD ( DAY. 1. OrderDate ) gt GETDATE () Whereas this will use an index if it exists (and it is selective enough): WHERE OrderDate gt DATEADD ( DAY. - 1. GETDATE ()) Implicit conversions in a query may also result in a scan. See this post by Jonathan Kehayias Implicit Conversions that cause Index Scans . Bad Parameter Sniffing Parameter sniffing is a good thing. It allows plan re-use and improves performance. But sometimes it results in a less efficient execution plan for some parameters. Index Maintenance Every index has to be maintained. Im not talking about maintenance plans, but about the fact that when rows are inserted, deleted and updated, the non-clustered indexes also have to be changed. This means additional IO for each index on a table. So it is a mistake to have more indexes than you need. Check that all indexes are being used. Check for duplicates and redundant indexes (where the columns in one are a subset of the columns in another). Check for indexes where the first column is identical but the rest are not - sometimes these can be merged. And of course, test, test, test. Index Fragmentation Index fragmentation affects IO performance in several ways. Range scans are less efficient, and less able to make use of read-ahead reads Empty space created in the pages reduces the density of the data, meaning more read IO is necessary The fragmentation itself is caused by page splits, which means more write IO There are a number things that can be done to reduce the impact of fragmentation, or to reduce the amount of fragmentation. Rebuild or reorganize indexes regularly Specify a lower fill factor so that page splits occur less often (though not too low, see below) Change the clustered index to use an ascending key so that new rows are appended to the end, rather than inserted in a random place in the middle Forwarded Records When a row in a heap is updated and requires more space, it is copied to a new page. But non-clustered indexes are not updated to point to the new page. Instead, a pointer is added to the original page to show where the row has moved to. This is called a forwarding pointer, and there could potentially be a long chain of these pointers to traverse to find the eventual data. Naturally, this means more IO. A heap cannot be defragmented by rebuilding the index (there isnt one). The only way to do this is to create a clustered index on the heap, and then drop it afterwards. Be aware that this will cause all non-clustered indexes to be rebuilt twice - once for the new clustered index, and again when it is dropped. If there are a lot of these it is a good idea to drop the non-clustered indexes first, and recreate them afterwards. Better still is to avoid heaps where possible. I accept there may be cases where they are the more efficient choice (inserting into archive tables, for example), but always consider whether a clustered index would be a better option - it usually is. Wasted Space In an ideal world every data page on disk (and in memory) would be 100 full. This would mean the minimum of IO is needed to read and write the data. In practise, there is wasted space in nearly all pages - sometimes a very high percent - and there are a lot of reasons why this occurs. Low fill factor Ive mentioned fill factor already. If it is too high, and page splits are occurring when rows are inserted or updated, it is sensible to rebuild the index with a lower fill factor. However, if the fill factor is too low you may have a lot of wasted space in the database pages, resulting in more IO and memory use. This is one of those quotsuck it and seequot scenarios. Sometimes a compromise is needed. Page splits This is also discussed above. But as well as fragmentation, page splits can also result in wasted space if the empty space is not reused. The solution is to defragment by rebuilding or reorganizing indexes regularly. Wasteful Choice of Data Types Use the smallest data types you can. And try to avoid the fixed length datatypes, like CHAR(255), unless you regularly update to the longest length and want to avoid page splits. The reasoning is simple. If you only use 20 characters out of 200, that is 90 wasted space, and more IO as result. The higher density of data per page the better. Lazy thinking might make developers create AddressLine1, AddressLine2, etc as CHAR(255), because they dont actually know what the longest should be. In this case, either do some research, find out that the longest is 50 characters (for example) and reduce them to CHAR(50), or use a variable length data type. Schema Design Ive already mentioned choice of data types above, but there are other schema design decisions that can affect the amount of IO generated by an application database. The most common one is designing tables that are too wide. I sometimes see a table with 20, 30, 50, even 100 columns. This means fewer rows fit on a page, and for some extreme cases there is room for just one row per page - and often a lot of wasted space as well (if the row is just slightly wider than half a page, thats 50 wasted). If you really do need 50 columns for your Customer table, ask yourself how many of these are regularly accessed. An alternative is to split into 2 tables. Customer, with just a few of the commonly used columns, and CustomerDetail with the rest. Of course, the choice of which columns to move is important. You dont want to start joining the tables for every query as that defeats the object of the exercise. Page or Row Compression Compression is another way of compacting the data onto a page to reduce disk space and IO. Use of row or page compression can dramatically improve IO performance, but CPU usage does increase. As long as you are not already seeing CPU bottlenecks, compression may be an option to consider. Be aware that compression is an Enterprise edition feature only. Backup Compression Since SQL Server 2008 R2, backup compression has been available on Standard edition as well as Enterprise. This is major benefit and I recommend that it be enabled on all instances. As well as creating smaller backups, it is also quicker and means less write IO. The small increase in CPU usage is well worth it. Enable it by default so that if someone sets off an ad hoc backup it will have minimal IO impact. Synchronous MirroringAlwaysOn High safety mode in database mirroring, or synchronous commit mode in AlwaysOn, both emphasise availability over performance. A transaction on the mirroring principal server or primary replica does not commit until it receives a message back from the mirror or secondary replica that the transaction has been hardened to the transaction log. This increases transactional latency, particularly when the servers are in different physical locations. Resource Governor in 2014 Up until and including SQL Server 2012 resource governor has only been able to throttle CPU and memory usage. Finally the ability to include IO in a resource pool has been added to SQL Server 2014. This has obvious use as a way of limiting the impact of reports on the system from a particular user, department or application. Gathering The Evidence There are a lot of ways you can measure SQL Server IO performance and identify which areas need looking at. Most of what follows is available in SQL CoPilot in graphical and tabular form, both as averages since last service start and as snapshots of current activity. Wait Types Use sys. dmoswaitstats to check number of waits and wait times for IOCOMPLETION, LOGBUFFER, WRITELOG and PAGEIOLATCH. Use this script to focus on the IO wait types: SELECT waittype. waitingtaskscount. waittimems - signalwaittimems AS totalwaittimems , 1. ( waittimems - signalwaittimems ) CASE WHEN waitingtaskscount 0 THEN 1 ELSE waitingtaskscount END AS avgwaitms FROM sys. dmoswaitstats WHERE waittype IN ( IOCOMPLETION. LOGBUFFER. WRITELOG. PAGEIOLATCHSH. PAGEIOLATCHUP. PAGEIOLATCHEX. PAGEIOLATCHDT. PAGEIOLATCHKP ) This shows averages since the last service restart, or since the wait stats were last cleared. To clear the wait stats, use DBCC SQLPERF (sys. dmoswaitstats, CLEAR) You can also check sys. dmoswaitingtasks to see what is currently being waited for. Virtual File Stats Query sys. dmiovirtualfilestats to find out which data and log files get the most read and write IO, and the latency for each file calculated using the stall in ms. SELECT d. name AS databasename. mf. name AS logicalfilename. numofbytesread. numofbyteswritten. numofreads. numofwrites. 1. iostallreadms ( numofreads 1 ) avgreadstallms. 1. iostallwritems ( numofwrites 1 ) avgwritestallms FROM sys. dmiovirtualfilestats (NULL, NULL) vfs JOIN sys. masterfiles mf ON vfs. databaseid mf. databaseid AND vfs. FILEID mf. FILEID JOIN sys. databases d ON mf. databaseid d. databaseid Performance Counters There are two ways of looking at performance counters. Select from sys. dmosperformancecounters, which shows all the SQL Server counters, or use Windows Performance Monitor (perfmon) to see the other OS counters as well. Some counters to look at are: SQL Server:Buffer Manager Lazy writessec The number of times per second that dirty pages are flushed to disk by the Lazy Writer process. An indication of low memory, but listed here as it causes more IO. Checkpoint pagessec The number of dirty pages flushed to disk per second by the checkpoint process. Page readssec Number of physical pages read from disk per second Page writessec Number of physical pages written to disk per second Readahead pagessec Pages read from disk in advance of them being needed. Expect to see high values in BI workloads, but not for OLTP SQL Server:Access Methods Forwarded recordssec Should be as low as possible. See above for explanation of forwarded records. Full scanssec The number of unrestricted full scans. Use of UDFs and table variables can contribute to this, but concentrating on seeks will help to keep the value down Page splitssec The number of page splits per second - combining splits due to pages being added to the end of a clustered index as well as quotgenuinequot splits when a row is moved to a new page. Use the technique from the link in the section on index fragmentation, above, to get a more accurate breakdown Skipped ghosted recordssec For information about ghosted records see An In-depth Look at Ghost Records in SQL Server Workfiles createdsec A measure of tempdb activity Worktables createdsec A measure of tempdb activity SQL Server:Databases Log bytes flushedsec The rate at which log records are written to disk Log flush wait time The duration of the last log flush for each database Log flush waitssec The number of commits per second waiting for a log flush Logical Disk Avg Disk secsRead Avg Disk secsWrite Avg Disk Read bytessec Avg Disk Write bytessec Using the sys. dmosperformancecounters DMV, a lot of counters display a raw value, which has to be monitored over time to see values per second. Others have to be divided by a base value to get a percentage. This makes this DMV less useful unless you perform these calculations and either monitor over time or take an average since the last server restart. This script uses the tempdb creation date to get the number of seconds since the service started and calculates the averages for these counters. It also retrieves all other counters and calculates those that are derived from a base value. USE master SET NOCOUNT ON DECLARE upsecs bigint SELECT upsecs DATEDIFF ( second. createdate. GETDATE ()) FROM sys. databases WHERE name tempdb SELECT RTRIM ( objectname ) objectname. RTRIM ( instancename ) instancename. RTRIM ( countername ) countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 65792 UNION ALL SELECT RTRIM ( objectname ), RTRIM ( instancename ), RTRIM ( countername ), 1. CAST ( cntrvalue AS bigint ) upsecs FROM sys. dmosperformancecounters WHERE cntrtype 272696576 UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 100. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 537003264 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND RTRIM ( v. countername ) base RTRIM ( b. countername ) UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 1. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073874176 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND REPLACE ( RTRIM ( v. countername ), (ms). ) Base RTRIM ( b. countername ) ORDER BY objectname. instancename. countername Dynamic Management Views and Functions As well as the DMVs in the above scripts, there are a number of others that are useful for diagnosing SQL Server IO performance problems. Here are all the ones I use. Ill add some sample scripts when I get the time: sys. dmoswaitstats sys. dmiovirtualfilestats sys. dmosperformancecounters sys. dmiopendingiorequests sys. dmdbindexoperationalstats sys. dmdbindexusagestats sys. dmdbindexphysicalstats sys. dmosbufferdescriptors It can also be useful to see what activity there is on the instance. Here are your options: The Profiler tool is quick and easy to use - you can start tracing in a matter of seconds. However, there is some overhead and it may impact performance itself - especially when a lot of columns are selected. A server side trace is a better option. A server-side trace has less of an impact than running Profiler. It has to be scripted using system stored procedures, but Profiler has the ability to generate the script for you. Extended Event Sessions Extended events were first introduced in SQL Server 2008, and have been considerably enhanced in SQL 2012. They are very lightweight, and the use of server-side traces and Profiler is now deprecated. Nevertheless, use of extended events may impact performance of high transaction systems if you are not careful. Use an asynchronous target and avoid complicated predicates to limit the overhead. There are a number of tools for gathering performance data from your servers. SQLIO is a simple tool that creates a file on disk and tests latency and throughput for randomsequential IO, at various block sizes and with a variable number of threads. These are all fully configurable. SQLIO is a great way of getting a baseline on a new server or storage, for future comparison. Third party tools are another option for viewing performance metrics. Some show you what is happening on the server right now. Others are built into more complex (and expensive) monitoring solutions. Performance metrics obtained on virtual servers are unreliable. Performance counters and wait stats may give the impression that everything is OK, when it is not. I recommend the use of the performance monitoring tools provided by the VM vendor. In the case of VMWare, this is very easy to use and is built into Virtual Center. This turned into a much bigger article than I expected - SQL Server IO performance is a big subject I started with everything I knew, and double checked my facts by searching the web and checking books. In the process I learnt a whole lot of new stuff and found a lot of useful links. It has been a useful exercise. Hopefully this has been useful for you too. Importing SQL Server Data Using SSIS - Which Option is Fastest By: Daniel Calbimonte Read Comments (27) Related Tips: More Integration Services Development This article is useful for SSIS developers who do not know which tasks are best to use in an SSIS projects. The main problem is that at the end of development if performance is slow then you will need to rebuild the project and change components. This article shows various ways of importing data and shows which types of components perform best within SSIS. The contest will be between the following components: ODBC Tasks ADO NET Tasks OLEDB Task SQL Server Destination T-SQL Tasks I created different SSIS packages in order to test performance. In this demo I used SSIS 2012 and the database Adventureworks 2012 . In this demo I am going to import the table AdventureWorks2012.Sales. SalesOrderDetail to the test2 database which is on the same instance of SQL Server. SalesOrderDetails is the table with more rows in AdventureWorks2012. In order to create the database test2 and the destination table dbo. OrderDetails, use this T-SQL code: Test 1 - ODBC Tasks The first example will use ODBC Source and ODBC Destination as shown below: When we run the package we notice the average time is 5 minutes 57 seconds to import the rows: Test 2 - ADO NET Tasks As noticed, ODBC is pretty slow. Lets try another approach. We are going to truncate the destination table first: Lets try ADO tasks to import the same data and verify if these components are faster: The average elapsed time in my testing was 11 seconds. This is much better. Test 3 - OLEDB Tasks This time we are going to import the same data using the OLEDB Tasks. Again we will truncate the table in the test2 database first. The average elapsed time is 5 seconds. Note that I am using the fast load option with the Table Lock option in the OLE DB Destination Task: If we do not use the fast load option, the average elapsed time was 2 minutes and 21 seconds: OK. The fast load option really improves performance. I will return to that configuration. What about the OLE DB Source. By default I am using the option Table or view in the OLE DB Source as shown below: Lets use a SQL Command instead as shown below. The average elapsed time is 2.85 seconds . Test 4 - SQL Server Destination Now, lets try to use the SQL Destination as the destination instead of OLE DB Destination: The average elapsed time is 2.5 seconds. At this point it is the best option. Test 5 - Execute T-SQL Task Finally, some people think that the best option is to use the Execute T-SQL Task: I am using a simple insert statement to import data from one source to another: The average elapsed time is 1.8 seconds Finally Ive been told that if the query runs inside a stored procedure it is even faster: Lets create a stored procedure: After creating the stored procedure we are going to call it in the Execute T-SQL Task: The average elapsed time is 2.12 seconds . The stored procedures does not improve performance. Lets review the table with the results: You may think the morale of the story is to use the Execute T-SQL Task instead of other SSIS tasks. In this example we were importing data on the same instance, but this will not always be the case. So the morale of the story is that there are many alternatives when creating a SSIS project and we have to carefully study the alternatives in different scenarios. There are great SSIS tools and we do not always use the best options. With each new version of SSIS new tasks are added and performance may be improved with existing tasks. The main changes in SSIS for SQL 2008 and 2012 are related to performance improvements. Next Steps If you are working in a SSIS project make sure you are using the best tasks and also verify if there are other SSIS tasks that can be used in your project. Also make sure you are following the best practices recommended by the experts: Last Update: 7132012 Great read and analysis, but I have one caveat to add. If you need to move a large amount of data, you need to take care of the transaction log growth. This is not a much of a concern using SSIS. For instance, I needed to move 1.3 billion rows (15 columns) and began using TSQL which quickly filled my logs. However, using OLE DB Source and Destination (Bulk Inserts) with fast load, there was little impact to the log file. Thursday, September 20, 2012 - 9:19:12 AM - vinodhkumar Its very useful. great job. Thanks Monday, August 27, 2012 - 10:54:42 AM - Orlando Colamatteo I agree with some others that the testbed is a bit contrived. If youre looking to move data from one table to another on the same instance then SSIS will rarely be a viable option. Some form of T-SQL will almost certainly outperform an SSIS operation. A more realistic scenario is moving data between two disparate data sources. It is surpising how poorly the ODBC Destination performs, especially in light of what Microsoft has publicly said in that they will be moving away from OLE DB interfaces and standardizing on ODBC in future products: In the ODBC Destination I expected Microsoft to implement the loading of data via the bulk load API as they did with the FastLoad option of the OLE DB Destination. On a separate note regarding loading data into MySQL with SSIS: In the past I did some performance tests with the Cherry City OLE DB driver for MySQL and it is horribly slow as it only inserts one row at a time. This is not to mention the fact that it crashed BIDS regularly when developing with it. Given the lack of a benefit I would stick with the tools built into SSIS and avoid the hassle of installing and configuring a third party driver. If youre using SSIS 2005 I would recommend using a Script Component as a Destination and issuing batch-inserts against a connection made using the MySQL ODBC Driver: msdn. microsoften-uslibraryms135939.aspx If youre using SSIS 2008 I would recommend using an ADO NET Destination with the MySQL ODBC Driver. In my tests it was only able to achieve about 240 rowsminute throughput to MySQL which is quite disappointing: msdn. microsoften-uslibrarybb895291(vsql.105).aspx If youre using SSIS 2012 I would recommend using an ODBC Destination with the MySQL ODBC Driver. In my tests it outperformed the ADO NET Destination over 3 to 1 but still only achieved about 800 rowsminute throughput, which was still quite disappointing: msdn. microsoften-uslibraryhh758691(vsql.110).aspx