Ssis Moving Average

Import av SQL Server-data ved hjelp av SSIS - Hvilket alternativ er raskest av: Daniel Calbimonte Les kommentarer (27) Relaterte tips: Mer utvikling av integrasjonstjenester Denne artikkelen er nyttig for SSIS-utviklere som ikke vet hvilke oppgaver som er best å bruke i SSIS-prosjekter. Hovedproblemet er at ved utgangen av utviklingen hvis ytelsen er treg, må du gjenoppbygge prosjektet og bytte komponenter. Denne artikkelen viser ulike måter å importere data på, og viser hvilke typer komponenter som fungerer best innen SSIS. Konkurransen vil være mellom følgende komponenter: ODBC-oppgaver ADO NET-oppgaver OLEDB Oppgave SQL Server Destinasjon T-SQL-oppgaver Jeg opprettet forskjellige SSIS-pakker for å teste ytelsen. I denne demonstrasjonen brukte jeg SSIS 2012 og databasen Adventureworks 2012. I denne demonstrasjonen skal jeg importere tabellen AdventureWorks2012.Sales. SalesOrderDetail til test2-databasen som er i samme forekomst av SQL Server. SalesOrderDetails er tabellen med flere rader i AdventureWorks2012. For å opprette database test2 og destinasjonstabellen dbo. OrderDetails, bruk denne T-SQL-koden: Test 1 - ODBC-oppgaver Det første eksemplet vil bruke ODBC Kilde og ODBC-destinasjon som vist nedenfor: Når vi kjører pakken ser vi gjennomsnittet tiden er 5 minutter 57 sekunder for å importere radene: Test 2 - ADO NET Tasks Som lagt merke til, er ODBC ganske treg. Lar prøve en annen tilnærming. Vi skal avkorte destinasjonstabellen først: Vi kan prøve ADO-oppgaver for å importere de samme dataene og kontrollere om disse komponentene er raskere: Gjennomsnittlig forløpt tid i testingen var 11 sekunder. Dette er mye bedre. Test 3 - OLEDB-oppgaver Denne gangen skal vi importere de samme dataene ved hjelp av OLEDB-oppgaver. Igjen vil vi avkorte tabellen i test2-databasen først. Gjennomsnittlig forløpt tid er 5 sekunder. Merk at jeg bruker hurtiglastalternativet med alternativet Tabelllås i OLE DB-destinasjonsoppgaven: Hvis vi ikke bruker hurtigbelastningsalternativet, var gjennomsnittlig forløpt tid 2 minutter og 21 sekunder: OK. Den raske lastalternativet forbedrer ytelsen virkelig. Jeg kommer tilbake til den konfigurasjonen. Hva med OLE DB Source. Som standard bruker jeg alternativet Tabell eller visning i OLE DB Kilde som vist nedenfor: Lar oss bruke en SQL-kommando i stedet som vist nedenfor. Gjennomsnittlig forløpt tid er 2,85 sekunder. Test 4 - SQL Server Destinasjon Nå kan vi prøve å bruke SQL-destinasjonen som destinasjon i stedet for OLE DB Destinasjon: Gjennomsnittlig forløpt tid er 2,5 sekunder. På dette punktet er det det beste alternativet. Test 5 - Utfør T-SQL-oppgave Endelig tror noen mennesker at det beste alternativet er å bruke Task SQL-oppgaven: Jeg bruker en enkel innsatserklæring for å importere data fra en kilde til en annen: Gjennomsnittlig forløpt tid er 1,8 sekunder Endelig har jeg blitt fortalt at hvis spørringen går inn i en lagret prosedyre, er det enda raskere: Lets opprette en lagret prosedyre: Etter å ha opprettet den lagrede prosedyren, skal vi kalle den i Utfør T-SQL-oppgaven: Gjennomsnittlig forløpt tid er 2,12 sekunder . De lagrede prosedyrene forbedrer ikke ytelsen. Lar oss se på tabellen med resultatene: Det kan hende du mener at moralen i historien er å bruke Execute T-SQL-oppgaven i stedet for andre SSIS-oppgaver. I dette eksemplet importerte vi data i samme instans, men dette vil ikke alltid være tilfelle. Så historiens moral er at det er mange alternativer når man oppretter et SSIS-prosjekt, og vi må nøye studere alternativene i ulike scenarier. Det er gode SSIS-verktøy, og vi bruker ikke alltid de beste alternativene. Med hver ny versjon av SSIS legges nye oppgaver til, og ytelsen kan forbedres med eksisterende oppgaver. De viktigste endringene i SSIS for SQL 2008 og 2012 er relatert til ytelsesforbedringer. Neste trinn Hvis du jobber i et SSIS-prosjekt, må du kontrollere at du bruker de beste oppgavene, og også verifisere om det finnes andre SSIS-oppgaver som kan brukes i prosjektet. Pass også på at du følger de beste anbefalingene som anbefales av eksperter: Sist oppdatert: 7132012 Flott lesing og analyse, men jeg har en advarsel å legge til. Hvis du trenger å flytte en stor mengde data, må du ta vare på transaksjonsloggveksten. Dette er ikke en stor bekymring ved å bruke SSIS. For eksempel måtte jeg flytte 1,3 milliarder rader (15 kolonner) og begynte å bruke TSQL som raskt fylte loggene mine. Ved bruk av OLE DB Source og Destination (Bulk Inserts) med rask belastning var det imidlertid liten innvirkning på loggfilen. Torsdag 20. september 2012 - 9:19:12 - vinodhkumar Det er veldig nyttig. god jobb. Takk Monday, 27 August 2012 - 10:54:42 - Orlando Colamatteo Jeg er enig med noen andre om at testbaren er litt opptatt. Hvis du ønsker å flytte data fra ett bord til et annet i samme instans, vil SSIS sjelden være et levedyktig alternativ. Noen form for T-SQL vil nesten helt sikkert overgå en SSIS-operasjon. Et mer realistisk scenario er å flytte data mellom to forskjellige datakilder. Det overvinner hvor dårlig ODBC-destinasjonen utfører, særlig i lys av hva Microsoft har offentlig sagt ved at de vil bevege seg bort fra OLE DB-grensesnitt og standardisere på ODBC i fremtidige produkter: I ODBC-destinasjonen forventer jeg at Microsoft skal laste inn data via bulklast-API som de gjorde med FastLoad-alternativet til OLE DB-destinasjonen. På et eget notat om lasting av data til MySQL med SSIS: Tidligere gjorde jeg noen ytelsestester med Cherry City OLE DB driveren for MySQL, og det er horribelt sakte, da det bare legger inn en rad om gangen. Dette er ikke å nevne det faktum at det krasjet BIDS regelmessig når det utvikles med det. På grunn av mangelen på en fordel vil jeg holde fast ved verktøyene som er innebygd i SSIS, og unngå å måtte installere og konfigurere en tredjepartsdriver. Hvis du bruker SSIS 2005, anbefaler jeg at du bruker en Script-komponent som en destinasjon og utsteder batch-innsatser mot en tilkobling gjort ved hjelp av MySQL ODBC-driveren: msdn. microsoften-uslibraryms135939.aspx Hvis du bruker SSIS 2008, anbefaler jeg at du bruker en ADO NET destinasjon med MySQL ODBC Driver. I mine tester var det bare i stand til å oppnå omtrent 240 raderminute gjennomstrømning til MySQL, noe som er ganske skuffende: msdn. microsoften-uslibrarybb895291 (vsql.105).aspx Hvis du bruker SSIS 2012, anbefaler jeg at du bruker et ODBC-mål med MySQL ODBC Driver. I mine tester ble det bedre enn ADO NET-destinasjonen over 3 til 1, men oppnådde fortsatt bare 800 ruterminute gjennomstrømning, noe som fortsatt var ganske skuffende: msdn. microsoften-uslibraryhh758691 (vsql.110).aspxIntroduction Med utgivelsen av SQL Server 2016 Service Pack 1 In-Memory ColumnStore-teknologien er nå også tilgjengelig i Standard, Web og Even Express og LocalDB Editions. Foruten fordelene med bare 1 kodebase for å opprettholde, vil denne endringen i policy også bli en klar diskplassvarselsparer på grunn av dens høye data deplikasjon og komprimeringsforhold, og sist men ikke minst, det er også en alvorlig ad hoc-spørringsytelse booster Hovedforskjellen mellom SQL-smaker er hvor mye CPU-strøm og minne som er tildelt til oppgaver som (re-) bygging av Clustered ColumnStore Index. For eksempel: med Standardversjonen brukes en enkelt kjerne (maks 100 prosessortid for SQL-prosessen) og spørre om et CCI skjer med maksimalt 2 CPUer (MAXDOP2), i motsetning til å utnytte alle tilgjengelige CPUer i Enterprise Edition. Bygg en Clustered ColumnStore Index (CCI) med SQL Server 2016 Standard Edition: Bygg et CCI med alle 4 tilgjengelige kjerner med SQL Server 2016 Enterprise Edition: Grunntimingen for å laste inn 7.2 GB 60 millioner rader fra en enkelt TPCH-linjemodifiler viser ikke mye av En forskjell mellom smaker når Bulk setter inn dataene direkte i enten et bunkebord eller et bord med en CCI, blir forskjellen klar når vi sammenligner tiden som trengs for å bygge en CCI på et bunkebord eller gjenoppbygge et CCI: For å oppsummere, er det absolutt raskeste måten å ha data tilgjengelig i en tabell med et Clustered ColumnStore Index er å: Last inn i bunke, bygg CCI etterpå med SQL 2016 Ent. Ed. Direkte last inn i CCI For tabeller med et Clustered ColumnStore-indeks som allerede er opprettet, sørg for at du strekker direkte til komprimerte radgrupper for å maksimere gjennomstrømningen. For å gjøre det, bør innsatsbatchstørrelsen være lik eller større enn 100K rader (102400 for å være presis). Mindre batcher vil bli skrevet inn i komprimerte Delta Store-tabeller først før de blir flyttet til sine siste komprimerte Row Group-segmenter, noe som betyr at SQL Server må røre dataene to ganger: Det finnes forskjellige alternativer for å laste data og vi vil gå over de mest brukte de som Bulk Insert-kommandoen, BCP og SSIS. Lar se hva som trengs for å få best ytelse og hvordan man skal overvåke 1) T-SQL Bulk Insert Sett oss med en BULK INSERT-kommandoen: Kontroller dataoverføringshastigheten For å sjekke antall rader som allerede er lastet inn i CCI, selv når Tabelllåsealternativet blir brukt, spør en ny dmv som heter sys. dmdbcolumnstorerowgroupphysicalstats: Denne DMV vil også avsløre de mulige ressursgruppestatene mer detaljert under lasting. Det er fire mulige Row Group-stater mens du laster inn data. Når du ser staten INVISBILE som i bildet nedenfor, betyr det at data blir komprimert til en RowGroup. 0: INVISIBLE (RowGroup er i ferd med å bli bygget fra data i deltabutikken) 1: OPEN160160160160160160160 (RowGroup aksepterer nye poster) 2: CLOSED160160160 (RowGroup er fylt men ikke komprimert av tuple mover-prosessen) 3: COMPRESSED160 ( RowGroup er fylt og komprimert). 4 TOMBSTONE160 (RowGroup er klar til å bli søppel samlet og fjernet) Ved å spesifisere batchstørrelsen med en verdi på 102400 eller høyere, vil du oppnå maksimal ytelse og dataene vil bli streamet og direkte komprimert til sin endelige RG. Denne oppførselen vil vises som KOMPRESSERT. Du kan også sjekke en DMV som ble introdusert med SQL2014 for å sjekke på RowGroup State, som er sys. columnstorerowgroups DMV: Testresultat Bulk setter inn data i et bord med CCI via kommandoen Bulk Insert kan forbedres litt ved å legge til Batchsize102400 og TABLOCK-alternativer. Dette gir en 8 forbedring i gjennomstrømning. 2) BCP. exe BCP-verktøyet brukes fortsatt ganske tungt i mange produksjonsmiljøer, så det er verdt å sjekke det raskt. Som standard setter BCP 1000 rad på den tiden til SQL Server. Tiden det tar å laste 7,2 GB data via BCP: 530 sekunder. or160 113K rowssec RowGroup-tilstanden viser NVISIBLE, noe som betyr at Delta Store brukes med standardinnstillingene. For å sikre at BCP-kommandoen streamer dataene direkte til komprimerte RG-er, må du legge til batchsize b-alternativet med en verdi på minst 102400. Jeg sprang forskjellige tester med større batchstørrelser: opp til 1048576, men 102400 ga best meg den resultat. BCP DB. dbo. LINEITEMCCI i F: TPCHlineitem. tbl S. - c - T - quotquot - b 102400 h tablock RowGroup-tilstanden viser nå COMPRESSED, noe som betyr at vi omgår Delta Store og datastrømmer i komprimerte RG: Resultat: BCP fullført i 457 sekunder eller 133K rader per sekund eller under testen la jeg merke til at standardinnstillingene for SSIS 2016 bruker minnebufferstørrelser som også kan potensielt begrense batchstørrelsen til mindre enn 100K-rader. I eksemplet nedenfor ser du dataene som er landet i deltabutikker: RG-statene er stengt, og deltastorehobtid-feltene er fylt, noe som betyr at deltabutikkene er leveraged. Dette var øyeblikket for å nå ut og sjekke med kolleger som heldigvis har lagt merke til dette, og en løsning er allerede der (se: Data Flow Buffer Auto Sizing-funksjonen fordeler data laster inn i CCI). For å fullt ut utnytte CCI-streaming-funksjonene må du øke standardminnebufferstørrelsesinnstillingene MaxRows: Endre disse til 10x større verdier: 8211 DefaultMaxBufferRows fra 10000 til 1024000 og den viktigste: 8211 DefaultBufferSize fra 10485760 til 104857600. Merk: Den nye AutoAdjustBufferSize-innstillingen skal settes til True når du laster inn svært store rader med data. Endre også verdiene for Destinasjonsadapteren: 8211 Rader per batch: 160 fra ingen til 102400 8211 Maksimal innsatsforbindelse: fra 2147483647 til 102400 Funksjonsparityen introdusert med SQL Server 2016 SP1 åpner et helt nytt spekter av muligheter for å dra nytte av forhåpentligvis gjennomgangene ovenfor hjelper deg med å maksimere Bulk Insert, BCP og SSIS-ytelsen når du laster inn data i et Clustered ColumnStore Index. Hva vil være den absolutt raskeste måten å laste data fra en flatfil i en tabell i SQL Server 2016 Mye har endret seg siden min første post på dette emnet for mange år siden, ike introduksjonen av In-memory optimaliserte tabeller og Updateable Columnstore tabellindekser. Også listen over datatransportkjøretøy å velge mellom vokser: i tillegg til BCP, er kommandoen T-SQL Bulk Insert, SSIS som ETL-verktøy og PowerShell noen nye lagt til, som PolyBase, External R Script eller ADF. I dette innlegget vil jeg begynne med å sjekke hvor mye raskere de nye holdbare ampere som ikke er holdbare i minnetabeller, Setter grunnlinjen for disse testene Im bruker en Azure DS4V2 Standard VM med 8 kerner28 GB RAM og 2 HDD-volumer med vertsbufring RW aktivert. (Begge Luns gir 275 MBsec RW gjennomstrømning, selv om GUI angir en grense på 60MBsec). Jeg genererte en enkel 60 millioner rad7.2 Gigabyte TPCH lineitem flatfil som data for å laste. Som utgangspunkt for bruk til sammenligning vil vi bruke tiden det tar å laste filen inn i et Heap-bord: Denne vanlige Bulk Insert-kommandoen fullføres innen 7 minutter med et gjennomsnitt på 143K rowssec. Aktivering av testdatabasen for minneoptimerte tabeller (i SQL20142016 Enterprise amp developer Edition) introduserte in-memory-tabeller er utviklet for svært rask OLTP med mange små transaksjoner og høy samtidighet, noe som er en helt annen type arbeidsbelastning som masseinnføring, men bare Ut av nysgjerrigheter gir det en prøve. Det finnes 2 typer minnetabeller: slitesterke og ikke-holdbare bord. De slitesterke vil vedvare data på disk, de ikke-slitesterke vil ikke. For å aktivere dette alternativet må vi gjøre litt husholdning og tildele et raskt diskvolum for å være vert for disse filene. Først må du endre databasen for å aktivere innholder MEMORYOPTIMIZEDDATA-alternativet etterfulgt av å legge til en filplassering og filgruppe som vil inneholde de minneoptimerte tabellene: Den tredje tingen å gjøre er å legge til et eget minnebasseng i SQL Server-forekomsten slik at den kan beholde alle dataene vi vil laste inn i minnetabeller som er skilt fra standardminnepuljen: Binde en database til et minnepool Trinnene for å definere et separat minnebasseng og å binde en database til det, er oppført nedenfor: Ekstra minnebassenger administreres via SQL Resource Governor. Det fjerde og siste skrittet er å binde testdatabasen til det nye minnebassenget med kommandoen sys. spxtpbinddbresourcepool.160 For at bindingen skal bli effektiv må vi ta databasen offline og ta den tilbake online. Når du er bundet, kan vi dynamisk endre mengden minne som er tildelt bassenget via ALTER RESOURCE POOL PoolHk WITH (MAXMEMORYPERCENT 80) kommandoen. Bulk Sett inn i Holdbar In-Memory-tabell Nå er vi alle satt sammen med In-memory-alternativet aktivert, vi kan lage en in-memory-tabell. Hvert minneoptimalisert bord må ha minst en indeks (enten en Range - eller Hash-indeks) som er helt (re-) sammensatt i minnet og aldri lagret på disk. Et holdbart bord må ha en deklarert primærnøkkel, som deretter kan støttes av den nødvendige indeksen. For å støtte en primærnøkkel lagde jeg en ekstra rownumber ROWID1-kolonne til tabellen: Angi en batchstørrelse på 1 (opptil 5) Millionrader til kommandoen for innsats for masseinnspilling bidrar til å fortsette data til disk mens masseinnsatsen går i gang (i stedet for å lagre alt på slutten) gjør det minimerer minnetrykket på minnebassenget PookHK vi opprettet. Databelastningen i den holdbare In-Memory-tabellen fullføres på 5 minutter 28 sekunder eller 183K Rowssec. Det er en ok tid, men ikke så mye raskere enn vår grunnlinje. Ser på sys. dmoswaitstats viser at no.1 waitstat er IMPROVIOWAIT som oppstår når SQL Server venter på en bulklast IO for å fullføre. Ser på ytelsestelleren Bulk Copy Rowssec og Disk Write Bytessec viser spyling til diskspikes på 275 MBsec når en batch kom inn (de grønne pigger). Det er maksimalt hva disken kan levere, men forklarer ikke alt. Gitt den lille gevinsten, parkerer vi denne for fremtidig etterforskning. Overvåking av minnesbassenget Via sys. dmresourcegovernorresourcepools dmv kan vi sjekke om vår in-memory-tabell utnytter det nylig opprettede PoolHK-minnet Pool: Utgangen viser at dette er tilfellet at 7,2 GB (noe ekstra for Rowid) ble ukomprimert lastet inn i minnet poolHk-basseng: Hvis du prøver å laste inn flere data enn du har minne tilgjengelig for bassenget, får du en skikkelig melding som denne: Erklæringen er avsluttet. Msg 701, Level 17, State 103, Line 5 Det er utilstrekkelig systemminne i ressursbassenget 8216PookHK for å kjøre dette spørsmålet. Hvis du vil se et nivå dypere på minnesplassallokering, kan du kjøre følgende spørring (hentet fra SQL Server In-Memory OLTP Internals for SQL Server 2016-dokument): Dataene vi nettopp lastet, lagres som en hestekonstruksjon med hash-indeks: Så langt så bra Nå kan vi fortsette og sjekke ut hvordan oppføring i et ikke-holdbart bord utfører Bulk Insert i ikke-holdbar in-memory-tabell For IMND-tabeller trenger vi ikke en primærnøkkel, så vi bare Legg til og ikke-klynget Hash-indeks og sett DÅBARHET SCHEMAONLY. Bulkinsatsen Datainnlasting i det ikke-holdbare bordet fullføres innen 3 minutter med en gjennomstrømning på 335K rowssec (vs 7 minutter) Dette er 2,3x raskere og deretter settes inn i et bunkebord. For datasettingen er dette definitivt en rask seier SSIS Single Bulk Sett inn i et ikke-holdbart bord Tradisjonelt er SSIS den raskeste måten å laste en fil raskt inn på SQL Server fordi SSIS vil håndtere all data forbehandling slik at SQL Server-motoren kan bruke CPU-ticks på å vedvare dataene til disken. Vil dette fortsatt være tilfelle når du setter dataene inn i et ikke-holdbart bord Nedenfor er et sammendrag av testene jeg kjørte med SSIS for dette innlegget: SSIS Fastparse-alternativet og 160 StandardBufferMaxRows og DefaultBufferSize-innstillingene er de viktigste ytelsesforbedringene. Også Native OLE DB (SQLOLEDB.1) leverandøren utfører litt bedre enn SQL Native Client (SQLNCLI11.1). Når du kjører SSIS og SQL Server side ved side, er det ikke nødvendig å øke nettverkspakkestørrelsen.160160 Nettoresultat: En grunnleggende SSIS-pakke som leser en flat filkilde og skriver dataene ut direkte til tabellen Non-Durable via en OLE DB-destinasjon Utfører tilsvarende som Bulk Insert-kommandoen i et IMND-bord: 60 millioner radene lastes inn i 2 minutter 59sekunder eller 335K radssek, identisk med kommandoen Bulk insert. SSIS med balansert datadistributør Men wait8230160 er minnetabellene designet for å fungere med låsfrigøringsfri, slik at vi kan laste data også via flere strømmer. Det er lett å oppnå med SSIS, den Balanced Data Distributøren vil bare ta med det (BDD er oppført i den vanlige delen av SSIS-verktøykassen) Når du legger til BDD-komponenten og setter inn dataene i det samme Ikke-holdbare bordet med 3 strømmer, gir du det beste gjennomslaget. Vi er nå opptil 526000 Rowssec. Se på denne svært flate linjen, med bare 160 av CPU-tid brukt av SQLServer, ser det ut til at vi treffer litt flaskehals: Jeg prøvde raskt å være kreativ ved å utnytte modulo-funksjonen og lagt til 2 flere datastrømmer i pakken (hver behandling 13 av dataene) 160, men det som ikke forbedrer mye (1 min52sek) så et flott emne for å undersøke for en fremtidig post160160 Alternativet In Memory Non-Durable-tabell gir noen alvorlige ytelsesforbedringer for å lagre data lasting data 1,5 ganger raskere med en vanlig Bulk Inser t og opptil 3,6 ganger raskere med SSIS. Dette alternativet, som primært er utformet for å øke hastigheten på OLTP, kan også gjøre en stor forskjell for å krympe batchvinduet raskt. (Fortsett). Lese så fort som mulig fra et bord med SSIS (Del II). Nylig blogget jeg om hvordan jeg skal være selektiv som mulig mens du leser fra og OLE DB Datakilde (del I) og hvordan du laster dataene fra en enkelt flatfil så raskt som mulig inn i et SQL Server-bord. Men du har sikkert lagt merke til at ut av esken leser SSIS data raskere fra en flat fil enn fra en SQL-tabell. I denne artikkelen vil jeg dele mitt siste knep på hvordan du kan øke hastigheten på lesningen fra et enkelt bord, vanligvis minst dobbelt så fort. Tid til å slå, Native OLE DB-kildehastighet Det første trinnet, som alltid, er å sjekke standard gjennomstrømning og varighet ved å telle rader fra Native OLE DB Datakilde. I vårt tilfelle tar det 14 minutter og 6 sekunder å lese alle ca. 180 millioner rader eller 12,89 GB fra et SQL Server-bord med 16 kolonner med data. For sammenligning leser de samme dataene fra en enkelt flatfil tar bare 7 minutter 57 sekunder. Så hvor kommer denne forskjellen fra? I utgangspunktet på grunn av IO-pakkenes størrelse som blir brukt vil jeg si. SSIS leser med 128 kB blokker fra en flat fil og bruker som standard 4KB (standard nettverkspakke størrelse) som kan byttes til maks. 32 KB for å be om data fra SQL Server (32 KB er også maksimum for den nye SQL2008 R2 versjonen). For å hente dataene raskere må vi finne en måte å håndtere flere IO-forespørsler parallelt. For å angi grunnlinjen skal du bare bygge en pakke og telle rader fra Native OLEDB Source. (Ikke glem å endre pakkestørrelsen fra 0 til 32767 i Connection Manager). Windows Performance Monitor-tellere for å sjekke er CPU-belastningen for begge SSIS-prosessene (DtsDebughost når du kjører pakken fra BIDS eller DTEXEC når startet fra kommandolinjen) og SQL Server-prosessen. Kontroller også mengden av byte vi leser: velg IO Read bytessec-telleren fra prosessen.160 Legge til parallellisme ved hjelp av Modulo-algoritmen Optimeringstricket jeg vil påpeke deg, er basert på bruk av Modulo-aritmetiske operatøren som kan være brukes med den numeriske datatypen. Den returnerer resten av ett nummer delt med en annen. Hvis du for eksempel vil lese med 3 parallelle strømmer, kan du bruke modulo 3 (3). Du kan hente de tre utgangsstrømmene ved å spesifisere som gjenværende verdier 0,1 og 2,160 (Ved bruk av verdier mindre enn 0 eller større enn 2 returnerer 0 rader når modulo 3 brukes.) Du kan sjekke utgangen også ved å kjøre spørringen i SSMS eller med BIDS Preview-alternativet fra OLEDB Source Editor. velg fra dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 2 Når du bygger dette spørsmålet inn i en SSIS-pakke, vil du legge merke til at det i utgangspunktet skaper parallellitet, radene er ganske fine delt og leses fra de to datakildene, men dessverre kommer ikke den totale pakkekjøpstidspunktet ned , noe som er litt rart.160 (Tross alt, 160 legger til flere kilder er et optimaliseringstrick vi oppdaget allerede lenge siden, tilbake i 2004 mens du testet en tidlig Beta 2-utgave av SSIS). Angi i pakken de flere datakildene og slå sammen resultatet med Union All-komponenten: Aktivitetsmonitoren viser oss raskt at flere SPID-er faktisk er avfyrt av SSIS-pakken, men mange er suspendert og forårsaker faktisk unødvendig synkronisering: Heldigvis kan dette løses raskt ved å angi forespørselshint OPTION (MAXDOP 1). Dette fjerner synkroniseringskostnaden uten å ofre på gjennomstrømning i dette tilfellet. Lese flere ganger fra samme SQL-tabell Tid for å sette den på prøve og øke antall datakildekomponenter i pakken. Hver OLE DB-datakilde skal peke på samme inngangstabell, 160 bare modifisere modulofaktoren og utgangsseparatoren når du legger til flere. Det beste resultatet jeg vanligvis oppnår mens jeg leser fra samme datakilde 3 eller 4 ganger i parallell: Når vi sjekker perfmon tellere en gang til, vil du se IO-gjennomstrømningen faktisk økte fra i utgangspunktet 21 MBsec til 46,6 MBsec i gjennomsnitt, så det har mer enn doblet CPU-forbruket av både SSIS og SQLServer-prosessen har også økt 160 SSIS fra 100 til 350 (3,5 CPU) og SQLServer bruker også en ekstra CPU. Den totale pakkenes utførelsesvarighet for å lese de samme 180 millioner radene (12,89 GB) 160, ble redusert fra begynnelsen 14 minutter 6 sekunder til bare 6 minutter og 33 sekunder når du leser dataene med 3 parallelle strømmer. Når du skal øke hastigheten på å lese store mengder data fra et enkelt bord i SSIS-pakken, kan du redusere varigheten med mer enn halvparten ved å legge til noen form for parallellitet i pakken din ved hjelp av this160 Modulo trick. Vanligvis leser 160 data fra samme SQL Server-tabell med 3 eller 4 parallelle strømmer det beste resultatet160 Lese så fort som mulig fra et bord med SSIS (del II). 5.0 av 5 basert på 22 karakterer 2 Rob Volk 2010-5-26 03:27 Kan du forklare hvorfor du bruker denne WHERE-setningen: WHERE (CAST (LORDERKEY AS VARCHAR) 3) Å kaste kolonnen til varchar er meningsløs hvis du vil å gjøre en modulo operasjon på det, siden det må gjøre en implisitt gjengitt (tilbake) til en numerisk type. Det kan ikke påvirke SSIS-operasjonene, men det vil sannsynligvis redusere SQL Server-delen. Henk 2010-5-26 10:31 Hei Rob, forklaringen er tittelen på dette innlegget, 8220 for å lese så fort som mulig8221 -) ved å bruke støpingen til varchar, blir det bare litt raskere, ved hjelp av 10 ekstra CPU-ressurser for SQLServer prosess. Men du er i orden, det er litt forvirrende å fjerne avstøpningen fra spørringen, og det vil også gjøre et triks: VELG fra dbo. LINEITEMHash96KeySSD hvor (LORDERKEY 3) 0 alternativ (maxdop 1) 3 Henk 2010-11-8 21:24 hvis du får bare 13 av dataene. Det kan være at du glemte å sette modulfunksjonen riktig i hver av trærne. som: Velg fra dbo. LINEITEMHash96KeySSD hvor (LORDERKEY 3) 0 velg fra dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 1 velg fra dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 2 (Du sier at du behandler 50 mill rader fra en staging db i 6 min 138K rowssec med en SSIS rowcount-komponent kan du sjekke om de totale radene stemmer overens med antall rader i staging databasen. Det vil også fortelle deg hvor fort du kan lese fra datakilden din og den effektive MBytesec. Kanskje er det noe mer rom for optimaliseringer). 4 Dan 2011-2-26 13:08 I8217m ny til parallellisme, så følg meg om spørsmålet mitt er elementært. Men jeg er forvirret på hvordan spørsmålet hint OPTION (MAXDOP 1) ville føre til at spidene til alle løper samtidig. I8217ve leser at dette 8220Suppresserer parallell plangenerering. Operasjonen vil bli utført serially8221 (Funnet ved stackoverflowquestions163917optionmaxdop-1-in-sql-server). Det får meg til å tro at OPTION (MAXDOP 1) bare tillater en CPU. Wouldn8217t det er bedre å bruke alle mulige CPU8217s Henk 2011-2-26 22:38 tricket er at du starter flere, separate, økter til SQL-server, hver økt serverer en del av dataene, og8230 hver sesjon (SPID) er faktisk håndteres av bare 1 CPU (SQL-planlegger) ved å undertrykke parallellisme i hver sesjon, får du ytelse (som vist på bildet, ellers SPIDS har primært status 8220suspended8221, så ikke noe nyttig) Men når du leser fra et partisjonert bord, som er en multithreaded operasjon av natur, vil du se at bruk av et høyere maxdop-nummer kan øke gjennomstrømningen. (les mer om dette emnet på: henkvandervalkoptimizing-sql-in-memory-table-scan-processing-speed. hvor lesing fra et partisjonert bord bruker 88 kerner-)) Håper dette hjelper Brgds, Henk 5 Dan 2011-3-1 20 : 37 Så stopper (MAXDOP 1) cpu'en fra å jobbe med annen prosess samtidig og konsentrere seg om den aktuelle spørringen Henk 2011-3-2 10:38 Da kan du ikke hoppe inn i den konklusjonen: med MAXDOP 1 spørringen vil bli håndtert av en enkelt SQL Scheduler som bruker en enkelt CPU. Men hvis det er flere spørsmål å tjene, vil disse også bli planlagt. 6 Dan 2011-3-4 21:44 Henk, jeg har en ssis pkg hvor jeg har 29 tabeller I8217m som ønsker å laste inn måten du har beskrevet ovenfor. I8217m legger trærne ett tre om gangen og ser på preformansen. Det første treet løp i seg selv fint, men når I8217ve har lagt til en annen, er det 6 sesjoner, men bare 3 løper, 1 sier suspendert og de andre 2 har ingenting i statusen. Jeg satte pkg-egenskapen MaxConcurrentExecutables 3 (Har bare 2 DataFlows for øyeblikket) og har satt hver dataflow8217s EngineThread 3 til å håndtere de tre dataporientene per dataflow. I8217m kjører dette i en jobb på en SQL Server 2005-boks med 8 3.2 Gig-prosessorer og 64 Gig Ram (Som I8217ve aldri sett gå over 50, må innstillingen for amt minne som er allokert til SQL Server justeres). Når jeg ser på aktivitetsmonitoren, forblir prosessorens tid ganske bra i 30 til 50-serien, noe som gjør meg sint å tenke at det er nok prosessortid tilgjengelig, men mine økter er i suspendert tilstand. Noen ide om hva I8217m gjør galt 7 Steve 2011-4-2 19:06 Henk, gode forslag, særlig som moduloperatøren, for å splitte seg inn i hvor mange parallelle strømmer man vil bruke. I8217ve vært i bruk av en sekvensbeholder med flere datakilder inne i den for å få det til å kjøre separate tråder for parallelle belastninger (fra ekstern Oracle-kilde) i stedet for metoden ovenfor med union all og maxdop. Vet du om det er ulempe med å bruke den metoden (forårsaker det at cxpacket synkroniserer venter) Det ser ut til å fungere riktig, og I8217ll prøver å kjøre noen tester, men bare hvis du prøvde dette og visste. Henk 2011-4-2 22:35 Hvis du leser fra forskjellige tabeller ved hjelp av flere datakilder, er det helt greit. Fra det jeg ser på kundesider, er det vanligvis litt tregere å lese fra Oracle-tabeller enn den faktiske skriver inn i en SQLServer-tabell. Du kan sjekke dette selv ved å bruke Rowcount-komponenttricket for å sjekke hvor fort du faktisk kan lese med datakilder. (bruker du separat nedlastbare Attention Microsoft-kontakter for Oracle fra Microsoft Download Center) Hvis det er nødvendig, kan du skrive ut dataene i flere SQL Server-partisjonstabeller ved hjelp av modulo-trikset. (Vennligst don8217t glem å også sjekke med Taskmanager nettverket gjennomføringsnumre :-)) Gi meg beskjed om resultatene lykke til Henk 8 bender 2011-10-26 20:22 9 Nik - Shahriar Nikkhah 2012-7-31 19:14 Hei Henk tror du at 8220OFFSET og FETCH8221 vil hjelpe i dette eksempelet antar jeg det vil, i utgangspunktet vil det spille rollen som 8220WHERE (LORDERKEY 3) 28221. Jeg har ikke testet det ennå. Jeg gjorde hva optimalisering og lastet en 10 GB CSV-fil på 7m51s på et Fast Track. Hva synes du Henk 2013-4-12 19:28 10GB 471 sekunder 21.7 MBsec dette ser ut som en enkelt Bulk Insert-oppgave Kun Er denne gjennomstrømningstiden akseptert for deg Hvis ikke, har du prøvd noen alternativer for å lage parallellitet (med SSIS og modulo-trikset, eller last flere parallelle filer parallelt) Brgds, Henk Dette fungerer bra. Så glad jeg fant denne siden. Jeg har brukt MAXDOP 1 på alle mine store borddrag i mange år. Recently, I started a job where there are quite a few legacy DTS packages that do massive table to table transfers for ETL processes. Splitting the tables using the 3(0-2) worked great. I am even tempted to split them out more to see how many times I can split my source before my netapp bricks itself. 12 Kamil 2014-4-30 08:51 I run some tests of data extraction with the method presented here. I checked it on smaller table 1.5 GB (12 mln rows) on my virtual machine with 2 procesors. The ssis package consists only from ole db data sources, 1 union all and 1 multicast componets. In properties MaximumErrorCount set to 4 (I have only 2 processors) and EngineThreads to default 10. In standard scenario with only 1 data source the ssis pulled data in 30sec. I got the best result with only 2 pararell data sources with time 25sec. I can8217t go to 50 of performance improvment. Have I missed something or this solution works great only for realy big tables Henk 2014-5-8 10:45 Hi Kamil, (1.5 GB 25 seconds 60 MBsec this is close to the limit of a 1Gbit network connection are you using iSCSI for your VM8217s) Just doublecheck the maximum source speed by reading from the source table with the modulo option (2 for 2 cpu8217s) and maxdop 1 with the rowcount as destination (as described in my blog). this will tell you how fast you can read. If you can read much faster than 60 MBsec than this means you have to optimize some other portions of the package 13 GowriShankar 2015-10-27 14:34 Hi Henk, Any idea for non numeric key fields where modulo is not applicable. Henk 2015-10-29 09:54 Hi Gowri, that is indeed a tricky one. would you have the option to add an extra column (Either as part of your source data or generate one on the fly in SSIS in the second please also add doublecheck the total number of rows read..)