Moving Media Tsql

SQL 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. Originariamente ho scritto un paio di paragrafi su come e perché questo accade e cosa si può fare per impedirlo, ma poi ho trovato questo post che spiega molto meglio - Hash intesa, ordinare e di scambio Spill eventi. Versione Conservare Il terzo utilizzo di tempdb è per l'archivio di versione. Questo è usato per versioni delle righe. le versioni di riga vengono creati quando viene utilizzato l'isolamento dello snapshot o l'opzione lettura istantanea impegnato. Essi sono anche creati durante indice in linea ricostruisce per gli aggiornamenti ed elimina fatta durante il processo di ricostruzione e per la gestione di modifiche ai dati di più set di risultati attivi (MARS). Un'applicazione scritta male (o utente rogue) l'esecuzione di un grande aggiornamento che colpisce molte migliaia di righe quando un livello di isolamento basato versioni delle righe è in uso può causare una rapida crescita in tempdb e avere un impatto negativo le prestazioni IO per gli altri utenti. Di tabelle e indici esplora una scansione di tabella è una scansione di un mucchio. Un indice di scansione è una scansione di un indice cluster o non cluster. Entrambi possono essere la migliore opzione se un indice di copertura non esiste e un sacco di righe sono suscettibili di essere recuperato. Una scansione indice cluster esegue meglio di una scansione di tabella - ancora un altro motivo per cumuli evitando Ma quali sono le cause di una scansione da utilizzare in primo luogo, e come si può fare una cercano più probabile Fuori statistiche aggiornate Prima di controllare gli indici e il codice, fanno assicurarsi che le statistiche sono aggiornate. Abilita quotauto creare statisticsquot. Se quotauto aggiornamento statisticsquot non è abilitato assicurarsi di eseguire un aggiornamento manuale di statistiche regolarmente. Questa è una buona idea, anche se l'aggiornamento quotauto statisticsquot è abilitato, come la soglia di circa 20 righe modificate prima che i calci di aggiornamento automatico in spesso non è sufficiente, in particolare quando si aggiungono nuove righe con una chiave ascendente. Indice Scelta A volte un indice esistente non viene utilizzata. Date un'occhiata a migliorare la sua selettività, eventualmente con l'aggiunta di ulteriori colonne, o modificare l'ordine delle colonne. Considerare se potrebbe essere creato un indice di copertura. Una cerchi è più probabilità di essere eseguito se non saranno necessari ricerche di segnalibro. Vedere questi messaggi del pointquot quottipping da Kimberly Tripp. Il punto di non ritorno. Inefficiente TSQL Il modo in cui una query viene scritto può anche tradursi in una scansione, anche se esiste un indice utile. Alcune delle ragioni di questo sono: espressioni non sargable nella clausola WHERE. quotsargquot significa semplice ragionamento. Quindi spostare calcoli lontano dalle colonne e sulle costanti invece. Così, per esempio, questo non utilizzare l'indice su DataOrdine: DOVE DATEADD (. GIORNO 1. OrderDate) gt GETDATE () considerando che questo sarà utilizzare un indice, se esiste (ed è abbastanza selettivo): DOVE DataOrdine gt DATEADD (DAY. - 1. GETDATE ()) le conversioni implicite in una query può anche provocare una scansione. Vedi questo post da Jonathan Kehayias conversioni implicite che causano Indice Scansioni. Bad sniffing sniffing dei parametri Parametro è una buona cosa. Permette piano di riutilizzo e migliora le prestazioni. Ma a volte si traduce in un piano di esecuzione meno efficiente per alcuni parametri. Indice Indice Manutenzione Ogni deve essere mantenuto. Im non parlare di piani di manutenzione, ma per il fatto che quando le righe vengono inserite, eliminate e aggiornate, gli indici non cluster devono anche essere cambiato. Questo significa ulteriore IO per ogni indice in una tabella. Quindi è un errore di avere più indici del necessario. Controllare che tutti gli indici vengono utilizzati. Verificare la presenza di duplicati e gli indici ridondanti (in cui le colonne in uno sono un sottoinsieme delle colonne in un altro). Controllare per gli indici in cui la prima colonna è identica, ma il resto non sono - a volte questi possono essere uniti. E, naturalmente, test, test, test. Indice Indice frammentazione frammentazione influisce sulle prestazioni IO in diversi modi. scansioni media sono meno efficienti e meno in grado di fare uso di read-ahead spazio vuoto creato nelle pagine riduce la densità dei dati, che significa più leggere IO è necessaria La frammentazione sé è causata da divisioni di pagina, che significa più scrittura IO ci sono un numero di cose che si possono fare per ridurre l'impatto della frammentazione, o per ridurre la quantità di frammentazione. Ricostruire o riorganizzare gli indici Specificare regolarmente un fattore di riempimento inferiore modo che la pagina si divide si verificano meno spesso (anche se non troppo bassa, vedi sotto) Cambiare l'indice cluster di utilizzare una chiave ascendente in modo che le nuove righe vengono aggiunte alla fine, piuttosto che inseriti in un posto a caso nel mezzo inoltrato Records Quando una riga in un mucchio viene aggiornato e richiede più spazio, viene copiato in una nuova pagina. Ma indici non cluster non vengono aggiornati per puntare alla nuova pagina. Invece, un puntatore viene aggiunto alla pagina originale per mostrare dove la riga è spostato. Questo è chiamato un puntatore inoltro, e ci potrebbero essere potenzialmente una lunga catena di questi puntatori per attraversare per trovare gli eventuali dati. Naturalmente, questo significa più IO. Un mucchio non può essere deframmentato ricostruendo l'indice (c'è neanche uno). L'unico modo per farlo è quello di creare un indice cluster sul mucchio, e quindi rilasciarlo in seguito. Essere consapevoli del fatto che questo farà sì che tutti gli indici non cluster per essere ricostruito due volte - una volta per il nuovo indice cluster, e di nuovo quando è caduto. Se ci sono un sacco di questi è una buona idea quella di eliminare gli indici non cluster prima, e di ricreare in seguito. Meglio ancora è quello di evitare cumuli, ove possibile. Accetto ci possono essere casi in cui essi sono la scelta più efficiente (l'inserimento nelle tabelle d'archivio, per esempio), ma sempre considerare se un indice cluster sarebbe una scelta migliore - di solito è. Wasted spazio in un mondo ideale ogni pagina dati su disco (e nella memoria) sarebbe 100 pieno. Ciò significherebbe è necessario il minimo di IO per leggere e scrivere i dati. In pratica, non vi è sprecato spazio in quasi tutte le pagine - a volte molto elevato per cento - e ci sono un sacco di ragioni per cui questo si verifica. basso fattore di riempimento Ive detto fattore di riempimento già. Se è troppo alto, e la pagina si divide stanno verificando quando le righe vengono inserite o aggiornati, è ragionevole ricostruire l'indice, con un fattore di riempimento inferiore. Tuttavia, se il fattore di riempimento è troppo basso si può avere un sacco di spazio sprecato nelle pagine del database, con conseguente più IO e la memoria uso. Questo è uno di quei quotsuck e seequot scenari. A volte è necessario un compromesso. Pagina divide Questo è anche discusso in precedenza. Ma oltre frammentazione, pagina divisioni possono anche provocare spazio sprecato se lo spazio vuoto non viene riutilizzato. La soluzione è quella di deframmentare ricostruendo o riorganizzazione degli indici regolarmente. Scelta spreco di tipi di dati Usare i tipi di dati più piccoli è possibile. E cercare di evitare i tipi di dati a lunghezza fissa, come CHAR (255), a meno che non si aggiorna regolarmente alla lunghezza più lunga e vuole evitare divisioni di pagina. Il ragionamento è semplice. Se si utilizza solo 20 caratteri su 200, cioè 90 lo spreco di spazio, e più IO come risultato. La maggiore densità di dati per pagina meglio. il pensiero pigro potrebbe rendere gli sviluppatori a creare AddressLine1, AddressLine2, ecc come CHAR (255), in quanto essi non sanno che cosa in realtà il più lungo dovrebbe essere. In questo caso, sia fare qualche ricerca, scoprire che il più lungo è di 50 caratteri (per esempio) e ridurli a char (50), o utilizzare un tipo di dati di lunghezza variabile. Schema design Ive già accennato scelta dei tipi di dati di cui sopra, ma ci sono altre decisioni di progettazione schema che possono influenzare la quantità di IO generato da un database dell'applicazione. Il più comune è la progettazione di tabelle che sono troppo larghi. A volte vedo un tavolo con il 20, 30, 50, anche 100 colonne. 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. Window Functions (OVER Clause)Help Make a Difference If I had to name one concept in standard SQL that I thought was the most important one, and that is worth Microsoftrsquos investment for future versions of SQL Server, Irsquod say window functions, hands down, without a doubt. Window functions are a subset of what the standard calls set functions, meaning, functions that are applied to a set of rows. The term window is used to describe the set of rows that the function operates on, and the language provides a clause called OVER where you provide the window specification. So whatrsquos the big deal, and what makes window functions more important than other features that are missing in SQL Server There are so many reasonshellip But first Irsquoll give a bit more background about window functions, and then Irsquoll get to the reasons and demonstrate use caseshellip First, to clarify, SQL Server 2005 already introduced some support for window functionsmdashthe ranking calculations: ROWNUMBER, RANK, DENSERANK and NTILE, and partial support for window aggregate functions with only the partitioning part implemented. SQL Server 2005 was a great release for developers with so many cool and practical T-SQL features. The number of solutions that I simplified and optimized just with the ROWNUMBER function and CTEs is amazing. Still, there are many standard features related to window functions that SQL Server didnrsquot yet implement (as of SQL Server 2008 R2) and that can help address quite a wide variety of business problems with simpler and more efficient solutions. These days the next major release of Microsoft SQL Servermdashversion 11mdashis being developed. These are pivotal days for candidate features where decisions are made whether they will or will not make it to the final release. And even though I think that more complete support for window functions is so important to developers and to the success of SQL Server, Irsquom not sure at all that we will see those in the product. This is time for us as part of the SQL Server community to express our strong opinion. Hopefully Microsoft will realize how important it is for us to have those features in the product, as well as to show that the SQL Server communityrsquos opinion matters. In this article I will explain some of the key features that are missing in SQL Server and why itrsquos important to add support for such features. If you share my opinion, and havenrsquot done so already, you can cast your vote in the following feature request items: Like with any thing in life that yoursquore not aware of, you donrsquot know how it can help you if you donrsquot know that it exists. My feeling is that many developers are not really aware of the capabilities of the standard window functions and therefore Microsoft doesnrsquot see a lot of demand for it. Education and raising the topic to peoplersquos awareness is therefore key to the realization of the benefits, and as a consequence, encourage people to ask Microsoft for more support. The unfortunate part is that all of SQL Serverrsquos leading competitors including Oracle, DB2 and Teradata for some time now already have a far more complete support for window functions. So even though my focus and passion is for SQL Server, I sometimes find myself in the awkward situation of demoing standard SQL window functions on Oracle when teaching or presenting. So whatrsquos missinghellip The most important missing features are probably ordering and framing options for window aggregate functions. Other key features that are still missing are distribution and offset functions, and reusability of window definitions. More details shortly. Why are window functions so powerful SQL is often referred to as a set-based language. The reason is that the language is based on the relational model, which in turn is based, in part, on mathematical set theory. When writing SQL queries yoursquore supposed to deal with a table (or relation, which is a set) as a whole, as opposed to the tablersquos individual rows. Also, since sets have no order, yoursquore not supposed to make any assumptions in regards to the physical ordering of the data. The reality is that for many developers set-based thinking is far from being intuitive, and it can take a few good years to truly think in SQL terms. This is why often developers tend to use cursorsmdashbecause using those feel like an extension to what they already know. Cursors allow you to deal with one row at a time, and also rely on specified order of the data. Window functions have an ingenious design. They do operate on sets, or windows, while allowing you to indicate ordering as part of the calculation where relevant. Not to confuse with cursors, window functions allow defining ordering for the calculation without making any expectations in regards to ordering of the input data given to the query or the output coming out of the query. In other words, no relational concepts are violated. Ordering is only part of the specification of the calculation. Similarly, other common elements in querying problems, like partitioning, framing of applicable rows, are all intuitive parts of the window specification. So in a sense, I see window functions as bridging the big gap that exists between cursoriterative and set-based thinking. Now, thatrsquos a lot of words before showing even one example. So letrsquos look at a few more concrete examples of some of the missing featureshellip Most of the examples Irsquoll show are against a database called InsideTSQL2008. You can find the script creating it here: InsideTSQLbookssourcecodeInsideTSQL2008.zip. In addition, the following view will be used in some of the examples: SET NOCOUNT ON USE InsideTSQL2008 GO IF OBJECTID ( 39Sales. EmpOrders39. 39V39 ) IS NOT NULL DROP VIEW Sales. EmpOrders GO CREATE VIEW Sales. EmpOrders WITH SCHEMABINDING AS SELECT O. empid , DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) AS ordermonth. SUM (OD. qty ) AS qty , CAST ( SUM (OD. qty OD. unitprice (1 - discount )) AS NUMERIC (12. 2 )) AS val , COUNT () AS numorders FROM Sales. Orders AS O JOIN Sales. OrderDetails AS OD ON OD. orderid O. orderid GROUP BY empid. DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) GO Ordering and Framing for Window Aggregate Functions As mentioned, currently window aggregate functions support only a partitioning element. Whatrsquos missing are ordering and framing options. The standard supports an ORDER BY clause to define ordering in the window and ROWS and RANGE clauses that frame the window based on the defined ordering. A classic example that would benefit from ordering and framing is running totals. Consider the following Accounts table definition: CREATE TABLE dbo. Accounts ( actid INT NOT NULL, -- partitioning column tranid INT NOT NULL, -- ordering column val MONEY NOT NULL -- measure CONSTRAINT PKAccounts PRIMARY KEY (actid. tranid ) ) The table represents deposit (positive value) and withdrawal (negative value) transactions in bank accounts. You need to calculate at each point what the account balance was. Like with many querying problems therersquos a partitioning element (actid), ordering element (tranid), and a measure that the calculation applies to (val). Window aggregate functions in standard SQL support all three elements. Herersquos how you would express the query calculating the balance at each point for each account: SELECT actid. tranid. val , SUM (val ) OVER ( PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS balance FROM dbo. Accounts You can achieve such calculations today in SQL Server using a subquery or a join: -- Set-Based Solution Using Subqueries SELECT actid. tranid. val , ( SELECT SUM (S2.val ) FROM dbo. Accounts AS S2 WHERE S2.actid S1.actid AND S2.tranid lt S1.tranid ) AS balance FROM dbo. Accounts AS S1 -- Set-Based Solution Using Joins SELECT S1.actid. S1.tranid. S1.val , SUM (S2.val ) AS balance FROM dbo. Accounts AS S1 JOIN dbo. Accounts AS S2 ON S2.actid S1.actid AND S2.tranid lt S1.tranid GROUP BY S1.actid. S1.tranid. S1.val But besides the fact that these solutions are not as straightforward and intuitive as the one using a window function, therersquos a big problem with the way SQL Server currently optimizes the subquery and join solutions. Assuming you defined a covering index on the partitioning column, followed by the ordering column, and including the aggregated measure, for each row SQL Server will scan all rows with the same partitioning value and an ordering value that is less than or equal to the current. Given p partitions with r rows in average, and fairly even distribution of rows in partitions, the total number of rows processed in such a plan is pr p(r r2)2. This means that in respect to the partition size, the algorithmic complexity, or scaling, of the solution s quadratic (N2). Thatrsquos bad. The window function form lends itself to good optimization, especially with the fast track case like the above (rows between unbounded preceding and current row). It should be straightforward to the optimizer to optimize this query with one ordered scan of the index, translating to simply pr rows being scanned. Another example for running totals is querying a table called EmpOrders with a row for each employee and month, and calculating the cumulative performance for each employee and month in other words, the total value for the employee from the beginning of hisher activity until the current month. Herersquos how you would express it with a window aggregate: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runqty FROM Sales. EmpOrders empid ordermonth qty runqty ----------- ----------------------- ----------- ----------- 1 2006-07-01 00:00:00.000 121 121 1 2006-08-01 00:00:00.000 247 368 1 2006-09-01 00:00:00.000 255 623 1 2006-10-01 00:00:00.000 143 766 1 2006-11-01 00:00:00.000 318 1084 . 2 2006-07-01 00:00:00.000 50 50 2 2006-08-01 00:00:00.000 94 144 2 2006-09-01 00:00:00.000 137 281 2 2006-10-01 00:00:00.000 248 529 2 2006-11-01 00:00:00.000 237 766 . There are many business examples where ordering and framing options can be useful besides calculating account balances. Those include inventory, running totals for reporting, moving averages, and so on. Herersquos an example for a query calculating the average of the last three recorded periods: SELECT empid. ordermonth , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avglastthree FROM Sales. EmpOrders There are also various temporal querying problems where running totals serve part of the solution. For simplicity I showed examples where framing is based on the ROWS clause where you indicate an offset in terms of number of rows. The standard also supports a RANGE clause that allows indicating an offset in terms of values, such as time intervals, as in the following example returning the average of the last three months: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth RANGE INTERVAL 39239 MONTH PRECEDING ) AS sum3mqty FROM Sales. EmpOrders ORDER BY empid. ordermonth The SQL standard defines several offset functions that would make developersrsquo life so much easier compared to the tools available today for similar needs. Among the missing offset functions are LAG and LEAD, returning a value from a row in a given offset from the current row based on specified ordering. For example, the following query will return, for each current order, also the order date of the previous and next orders: SELECT custid. orderdate. orderid , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prvod , LEAD (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS nxtod FROM Sales. Orders custid orderdate orderid prvod nxtod ------- ----------- -------- ----------- ----------- 1 2007-08-25 10643 NULL 2007-10-03 1 2007-10-03 10692 2007-08-25 2007-10-13 1 2007-10-13 10702 2007-10-03 2008-01-15 1 2008-01-15 10835 2007-10-13 2008-03-16 1 2008-03-16 10952 2008-01-15 2008-04-09 1 2008-04-09 11011 2008-03-16 NULL 2 2006-09-18 10308 NULL 2007-08-08 2 2007-08-08 10625 2006-09-18 2007-11-28 2 2007-11-28 10759 2007-08-08 2008-03-04 2 2008-03-04 10926 2007-11-28 NULL . Notice how elegant and intuitive this form is. The default offset is one row, but you can also be explicit if you need an offset that is other than one row, e. g. three rows: SELECT custid. orderdate. orderid , LAG (orderdate. 3 ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prv3od FROM Sales. Orders There are lots of business examples for the usefulness of these functions, like recency calculations, trend analysis, and others. Herersquos an example for a query addressing recency calculations, returning the difference in terms of days between the current and previous orders: SELECT custid. orderdate. orderid , DATEDIFF ( day , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ), orderdate ) AS diff FROM Sales. Orders Other missing offset functions are FIRSTVALUE, LASTVALUE, returning the value from the first or last rows in the partition based on specified ordering. Herersquos an example returning the value of the first and last orders per customer with each order: -- FIRSTVALUE, LASTVALUE SELECT custid. orderdate. orderid. val , FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS valfirstorder , LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS vallastorder FROM Sales. OrderValues custid orderdate orderid val valfirstorder vallastorder ------- ----------- -------- ------- --------------- -------------- 1 2007-08-25 10643 814.50 814.50 933.50 1 2007-10-03 10692 878.00 814.50 933.50 1 2007-10-13 10702 330.00 814.50 933.50 1 2008-01-15 10835 845.80 814.50 933.50 1 2008-03-16 10952 471.20 814.50 933.50 1 2008-04-09 11011 933.50 814.50 933.50 2 2006-09-18 10308 88.80 88.80 514.40 . And herersquos an example calculating the difference between the current order value and the first and last: SELECT custid. orderdate. orderid. val , val - FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS difffirst , val - LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS difflast FROM Sales. OrderValues Standard SQL supports window distribution functions that performing statistical calculations. Specifically it supports the PERCENTRANK and CUMDIST functions, calculating a percentile rank and cumulative distribution. These functions give you a relative rank of a row in respect to other rows in the window partition, expressed as ratiopercent. The specific formulas used by the two variants are: PERCENTRANK: (RK-1)(NR-1), where RK rank, NR number of rows in partition CUMEDIST: NPNR, where NP number of rows preceding or peer with current row (same as next rank - 1) Herersquos an example using these functions: SELECT custid. COUNT () AS numorders , PERCENTRANK () OVER ( ORDER BY COUNT ()) AS percentrank , CUMEDIST () OVER ( ORDER BY COUNT ()) AS cumedist FROM Sales. Orders GROUP BY custid custid numorders percentrank cumedist ------- ---------- ------------ --------- 13 1 0.0000 0.0112 33 2 0.0114 0.0337 43 2 0.0114 0.0337 42 3 0.0341 0.1124 53 3 0.0341 0.1124 . 37 19 0.9545 0.9663 24 19 0.9545 0.9663 63 28 0.9773 0.9775 20 30 0.9886 0.9888 71 31 1.0000 1.0000 Reuse of Window Definition using WINDOW Clause Suppose you need to write several window functions that rely on the same window definition (or part of it). You will end up with a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable. For example, instead of: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runsumqty , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runavgqty , FROM Sales. EmpOrders SELECT empid. ordermonth. qty , SUM (qty ) OVER W1 AS runsumqty , AVG (qty ) OVER W1 AS runavgqty , FROM Sales. EmpOrders WINDOW W1 AS ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As you can see, with the WINDOW clause the code is shorter, more readable, and easier to maintain. I showed just part of the standard support for window functions that SQL Server is still missing. Therersquos more, like window frame exclusion. There are also other set functions not implemented, like ordered set functions, and so on. But here I wanted to make a point in hope that Microsoft will realize how important it is to add such support in SQL Server 11. If you feel so as well, help make a difference by voting for the items, write about the topic, talk about it, increasing peoplersquos awareness. Hopefully this request will find open ears. As a reminder, here are the open items for some of the requests for enhancements: Thank you for expressing so well what I would haveshould have written a long time ago. Personally a SQLSErver fan, I have had to become expert at Oracle to support our platform for the many customers who still think Oracle is better. While my gripes with Oracle would take many pages to begin to express, I DO like its full support of windows functions, especially LEAD and LAG. No reason to add to what has been already so eloquently said in this post. If there is a polling place anywhere to encourage Microsoft to fully support windows function in a future release, please direct me to it Is it too late for 2011 Looking at the OVER clause in BOL at the moment the enhancements seem entirely under whelming msdn. microsoften-uslibraryms18946128SQL.11029.aspx Please Log In or Register to post comments. 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