Ssis glidande medelvärde

Importera SQL Server-data med SSIS - vilket alternativ är snabbast av: Daniel Calbimonte Läs kommentarer (27) Relaterade tips: Mer utveckling av integrationstjänster Denna artikel är användbar för SSIS-utvecklare som inte vet vilka uppgifter som är bäst att använda i en SSIS-projekt. Det största problemet är att i slutet av utvecklingen om prestanda är långsam måste du bygga om projektet och byta komponenter. Denna artikel visar olika sätt att importera data och visar vilka typer av komponenter som fungerar bäst inom SSIS. Tävlingen kommer att ligga mellan följande komponenter: ODBC-uppgifter ADO NET-uppgifter OLEDB-uppdrag SQL Server-destination T-SQL-uppdrag Jag skapade olika SSIS-paket för att testa prestanda. I denna demo använde jag SSIS 2012 och databasen Adventureworks 2012. I denna demo ska jag importera tabellen AdventureWorks2012.Sales. SalesOrderDetail till test2-databasen som ligger i samma instans av SQL Server. SalesOrderDetails är tabellen med fler rader i AdventureWorks2012. För att skapa databasprov2 och destinationstabellen dbo. OrderDetails, använd den här T-SQL-koden: Testa 1 - ODBC-uppgifter Det första exemplet kommer att använda ODBC-källa och ODBC-destination enligt nedan: När vi kör paketet märker vi genomsnittet tiden är 5 minuter 57 sekunder för att importera raderna: Test 2 - ADO NET Tasks Som noterat är ODBC ganska långsam. Låt oss försöka med en annan metod. Vi kommer att avkorta destinationstabellen först: Vi kan prova ADO-uppgifter för att importera samma data och kontrollera om dessa komponenter är snabbare: Den genomsnittliga förflutna tiden i testningen var 11 sekunder. Detta är mycket bättre. Test 3 - OLEDB-uppgifter Den här gången kommer vi att importera samma data med OLEDB-uppgifterna. Återigen kommer vi att avkorta tabellen i test2-databasen först. Den genomsnittliga förflutna tiden är 5 sekunder. Observera att jag använder snabblastalternativet med alternativet Bordslås i OLE DB-destinationsuppgiften: Om vi ​​inte använder snabblastalternativet var den genomsnittliga förflutna tiden 2 minuter och 21 sekunder: OK. Det snabba lastalternativet förbättrar prestanda verkligen. Jag kommer tillbaka till den konfigurationen. Vad sägs om OLE DB Source. Som standard använder jag alternativet Tabell eller vy i OLE DB-källan enligt nedan: Låt oss använda ett SQL-kommando istället som visas nedan. Den genomsnittliga förflutna tiden är 2,85 sekunder. Test 4 - SQL Server Destination Nu kan vi försöka använda SQL-destinationen som destination istället för OLE DB Destination: Den genomsnittliga förflutna tiden är 2,5 sekunder. Vid det här tillfället är det det bästa alternativet. Test 5 - Utför T-SQL-uppgift Slutligen tycker vissa att det bästa alternativet är att använda Execute T-SQL-uppgiften: Jag använder en enkel insert-sats för att importera data från en källa till en annan: Den genomsnittliga förflutna tiden är 1,8 sekunder Slutligen har Ive fått veta att om frågan går in i en lagrad procedur är det ännu snabbare: Låter skapa en lagrad procedur: Efter att ha skapat den lagrade proceduren kommer vi att kalla den i Execute T-SQL-uppgiften: Den genomsnittliga förflutna tiden är 2,12 sekunder . De lagrade procedurerna förbättrar inte prestanda. Låt oss granska tabellen med resultaten: Du kanske tror att historien är att använda Execute T-SQL-uppgiften istället för andra SSIS-uppgifter. I det här exemplet importerade vi data i samma instans, men det kommer inte alltid att vara fallet. Så historiens moral är att det finns många alternativ när man skapar ett SSIS-projekt och vi måste noggrant studera alternativen i olika scenarier. Det finns stora SSIS-verktyg och vi använder inte alltid de bästa alternativen. Med varje ny version av SSIS läggs nya uppgifter till och prestanda kan förbättras med befintliga uppgifter. De viktigaste ändringarna i SSIS för SQL 2008 och 2012 är relaterade till prestandaförbättringar. Nästa steg Om du arbetar i ett SSIS-projekt, se till att du använder de bästa uppgifterna och kontrollera om det finns andra SSIS-uppgifter som kan användas i ditt projekt. Se också till att du följer de bästa praxis som rekommenderas av experterna: Senaste uppdatering: 7132012 Bra läsning och analys, men jag har en försiktighet att lägga till. Om du behöver flytta en stor mängd data måste du ta hand om transaktionsloggstillväxten. Det här är inte så mycket en fråga om att använda SSIS. Till exempel behövde jag flytta 1,3 miljarder rader (15 kolumner) och började använda TSQL som snabbt fyllde mina loggar. Med hjälp av OLE DB Source and Destination (Bulk Inserts) med snabb belastning var det dock liten inverkan på loggfilen. Torsdag den 20 september 2012 - 9:19:12 - vinodhkumar Det är mycket användbart. bra jobb. Tack Måndag 27 augusti 2012 - 10:54:42 - Orlando Colamatteo Jag håller med några andra om att testbiten är lite konstruerad. Om du vill flytta data från ett bord till ett annat i samma instans, kommer SSIS sällan att vara ett lönsamt alternativ. En del av T-SQL kommer nästan säkert att överträffa en SSIS-operation. Ett mer realistiskt scenario är att flytta data mellan två olika datakällor. Det övertygar hur dåligt ODBC-destinationen utför, särskilt i ljuset av vad Microsoft offentligt har sagt genom att de kommer att flytta bort från OLE DB-gränssnitt och standardisera på ODBC i framtida produkter: I ODBC-destinationen förväntade jag mig Microsoft att genomföra lastningen av data via bulklast API som de gjorde med FastLoad-alternativet för OLE DB-destinationen. I en separat anmälan om laddning av data till MySQL med SSIS: Tidigare gjorde jag några prestationstest med Cherry City OLE DB-drivrutinen för MySQL och det är hemskt långsamt eftersom det bara sätter in en rad i taget. Detta ska inte nämna det faktum att det kraschade BIDS regelbundet när det utvecklades med det. Med tanke på bristen på en fördel skulle jag hålla fast vid verktygen inbyggda i SSIS och undvika besväret att installera och konfigurera en tredje parts drivrutin. Om du använder SSIS 2005 rekommenderar jag att du använder en Script-komponent som en destination och utfärdar batchinsatser mot en anslutning som görs med hjälp av MySQL ODBC-drivrutinen: msdn. microsoften-uslibraryms135939.aspx Om du använder SSIS 2008 rekommenderar jag att du använder en ADO NET Destination med MySQL ODBC-drivrutinen. I mina test kunde det bara uppnå cirka 240 raderminute-genomströmning till MySQL vilket är ganska nedslående: msdn. microsoften-uslibrarybb895291 (vsql.105).aspx Om du använder SSIS 2012 rekommenderar jag att du använder en ODBC-destination med MySQL ODBC-drivrutinen. I mina test överträffade den ADO NET-destinationen över 3 till 1 men uppnådde ändå bara 800 raderminuter genomströmning, vilket fortfarande var ganska nedslående: msdn. microsoften-uslibraryhh758691 (vsql.110).aspxIntroduction Med utgåvan av SQL Server 2016 Service Pack 1 In-Memory ColumnStore-tekniken är nu också tillgänglig i Standard, Web och Even Express och LocalDB Editions. Förutom att endast 1 kodbas ska behållas, kommer denna policyändring också att bli en tydlig disklagringsplatsbesparare på grund av dess höga data deplikations - och komprimeringsförhållanden och sist men inte minst är det också en seriös ad hoc-sökprestanda booster Huvudskillnaden mellan SQL-smakerna är hur mycket CPU-ström och minne som allokeras till uppgifter som (åter) byggande av Clustered ColumnStore Index. Till exempel: med standardversionen används en enda kärna (max 100 processortid för sqlservr-processen) och frågar om en CCI händer med högst 2 CPU (MAXDOP2), jämfört med att utnyttja alla tillgängliga processorer i Enterprise Edition. Bygga ett Clustered ColumnStore Index (CCI) med SQL Server 2016 Standard Edition: Bygg en CCI med alla 4 tillgängliga kärnor med SQL Server 2016 Enterprise Edition: Bastidstrenderna för laddning 7.2 GB 60 miljoner rader från en enda TPCH-linjeItem-filer visar inte mycket av En skillnad mellan smakerna när Bulk sätter in data direkt i antingen ett hopbord eller ett bord med en CCI, skillnaden blir tydlig när vi jämför den tid som behövs för att bygga ett CCI på ett hopbord eller bygga upp ett CCI: För att sammanfatta den absoluta snabbaste sättet att ha data tillgänglig i en tabell med ett Clustered ColumnStore Index är att: ladda till höft bygga CCI efteråt med SQL 2016 Ent. Ed. Direktladdning till CCI För tabeller med ett Clustered ColumnStore Index som redan skapats, se till att du direkt strömmar i komprimerade radgrupper för att maximera genomströmningen. För att göra det borde insatsens batchstorlek vara lika eller större än 100 k rader (102400 för att vara exakt). Mindre partier kommer att skrivas in i komprimerade Delta Store-tabeller först innan de flyttas till sin slutliga komprimerade Row Group-segment, vilket innebär att SQL Server måste röra data två gånger: Det finns olika alternativ för att ladda data och vi går över de mest använda sådana, som kommandot Bulk Insert, BCP och SSIS. Låt oss se vad som behövs för att få bästa prestanda och hur man övervakar 1) T-SQL-bulkinsats Låt oss börja med ett BULK INSERT-kommando: Kontrollera dataöverföring För att kontrollera antalet rader som redan har laddats in i CCI, även när Tabellslåsalternativet används, fråga en ny dmv som heter sys. dmdbcolumnstorerowgroupphysicalstats: Denna DMV kommer också att avslöja de möjliga Resource Group-staterna närmare när de laddas. Det finns fyra möjliga Row-grupptillstånd medan du laddar data. När du ser staten INVISBILE som i bilden nedan betyder att data komprimeras till en RowGroup. 0: INVISIBLE (RowGroup är i färd med att byggas från data i Delta Store) 1: OPEN160160160160160160160 (RowGroup accepterar nya poster) 2: CLOSED160160160 (RowGroup är fylld men ännu inte komprimerad av tuple mover-processen) 3: COMPRESSED160 ( RowGroup är fylld och komprimerad). 4 TOMBSTONE160 (RowGroup är redo att skräp samlas och tas bort) Genom att ange batchstorleken med ett värde på 102400 eller högre kommer du att uppnå maximal prestanda och data kommer att bli strömmade och direkt komprimeras till dess slutliga RG detta beteende kommer att dyka upp som KOMPRESSERAD. Du kan också kontrollera en DMV som introducerades med SQL2014 för att kolla på RowGroup State, vilket är sy. columnstorerowgroups DMV: Testresultat Bulk infoga data i en tabell med CCI via kommandot Bulk Insert kan förbättras något genom att lägga till Batchsize102400 och TABLOCK-alternativ. Detta ger en 8 förbättring av genomströmningen. 2) BCP. exe BCP-verktyget används fortfarande ganska tungt i många produktionsmiljöer, så värt att kontrollera det snabbt: som standard sänder BCP 1000 rader vid den tiden till SQL Server. Den tid det tar att ladda 7,2 GB data via BCP: 530 sekunder. or160 113K rowssec RowGroup-läget visar NVISIBLE vilket innebär att med standardinställningarna används Delta Store. För att säkerställa att BCP-kommandot strömmar data direkt i komprimerade RG-er måste du lägga till batchsize b-alternativet med ett värde av minst 102400. Jag körde olika tester med större batchstorlekar: upp till 1048576 men 102400 gav mig bäst de resultat. BCP DB. dbo. LINEITEMCCI i F: TPCHlineitem. tbl S. - c - T - quotquot - b 102400 h tablock RowGroup-tillståndet visar nu COMPRESSED vilket innebär att vi kringgår Delta Store och dataströmmar i komprimerade RG: Resultat: BCP avslutad på 457 sekunder eller 133K rader per sekund eller under testet märkte jag att standardinställningarna för SSIS 2016 använder minnesbuffertstorlekar som också kan begränsa batchstorleken till mindre än 100K rader. I exemplet nedan ser du data som landas i deltabutiker: RG-staterna är stängda och deltastorehobtid-fälten är befolkade, vilket betyder att deltabutikerna är levererade. Det här var dags att nå ut och kolla med mina kollegor som lyckligtvis har lagt märke till detta och en lösning finns redan där (se: Data Flow Buffer Auto Size-förmåga fördelar data laddas i CCI). För att fullt ut utnyttja CCI-streaming-kapaciteterna måste du öka inställningarna för Standard Memory BufferSize amp MaxRows: Ändra dessa till 10x större värden: 8211 DefaultMaxBufferRows från 10000 till 1024000 och den viktigaste: 8211 DefaultBufferSize från 10485760 till 104857600. Obs! Den nya inställningen AutoAdjustBufferSize bör sättas till True när du laddar mycket breda rader med data. Ändra också värdena för destinationsadapteren: 8211 rader per sats: 160 från ingen till 102400 8211 Maximalt infogningsbegränsningsstorlek: från 2147483647 till 102400 Funktionspariteten införd med SQL Server 2016 SP1 öppnar ett helt nytt utbud av möjligheter att dra nytta av förhoppningsvis genomgångarna ovan hjälper dig att maximera Bulk Insert, BCP och SSIS prestanda när du laddar data i ett Clustered ColumnStore Index Vad är det absolut snabbaste sättet att ladda data från en plattfil till en tabell i SQL Server 2016 Mycket har ändrats sedan min första posta på det här ämnet för många år sedan, introducera introduktionen av In-Memory optimerade tabeller och Indexable Columnstore-tabellindex. Även listan över datatransportfordon som ska väljas växer: förutom BCP, kommandot T-SQL Bulk Insert, SSIS som ETL-verktyg och PowerShell finns några nya tillagda, till exempel PolyBase, External R Script eller ADF. I det här inlägget börjar jag med att kontrollera hur mycket snabbare den nya varaktiga förstärkaren inte hållbara In-memory-tabellerna ställer in baslinjen för dessa test. Im använder en Azure DS4V2 Standard VM med 8 kärnor28 GB RAM och 2 HDD-volymer med värdcaching RW aktiverad. (Båda Luns ger 275 MBsec RW-genomströmning även om GUI anger en gräns på 60 MB). Jag genererade en enda 60 miljoner row7.2 Gigabyte TPCH lineitem plattfil som data att ladda. Som baslinje för att användas för jämförelse använder vi den tid det tar för att ladda filen till ett Heap-bord: Detta regelbundna Bulk Insert-kommando slutförs inom 7 minuter med ett genomsnitt på 143K rowssec. Aktivera testdatabasen för minnesoptimerade tabeller (i SQL20142016 Enterprise Amp Developer Edition) introduceras i minnetabellerna är konstruerade för mycket snabb OLTP med många små transaktioner och hög samtidighet, vilket är en helt annan typ av arbetsbelastning som bulkinlägg, men bara Utan nyfikenheter ger det ett försök Det finns 2 typer av minnesbord: hållbara och slitstarka bord. De slitstarka kommer att fortsätta data på disken, de icke-slitstarka kommer inte att användas. För att aktivera det här alternativet måste vi göra lite hushållning och tilldela en snabb diskvolym för att värd dessa filer. Ändra först databasen för att möjliggöra alternativet Innehåller MEMORYOPTIMIZEDDATA följt av att lägga till en filposition och filgrupp som innehåller de minnesoptimerade tabellerna: Det tredje är att lägga till en separat minnespool i SQL Server-förekomsten så att den kan hålla allt de data som vi kommer att ladda in i minnetabeller som är separata från dess standardminnepool: Binda en databas till en minnespool Stegen för att definiera en separat minnespool och binda en databas till den finns nedan: Extra minnespooler hanteras via SQL Resursguvernör. Det fjärde och sista steget är att binda testdatabasen till den nya minnespoolen med kommandot sys. spxtpbinddbresourcepool.160 För att bindningen ska bli effektiv måste vi ta databasen offline och återföra den online. När vi är bundna kan vi dynamiskt ändra mängden minne som tilldelats poolen via kommandot ALTER RESOURCE POOL PoolHk WITH (MAXMEMORYPERCENT 80). Bulk Insert i Durable In Memory-tabell Nu är vi alla inställda när alternativet In-memory är aktiverat, vi kan skapa en in-memory-tabell. Varje minnesoptimerat tablett måste ha minst ett index (antingen en Range - eller Hash-index) som är helt (åter) sammansatt i minnet och lagras aldrig på disken. Ett slitstarkt bord måste ha en deklarerad primär nyckel, som då kan stödjas av det obligatoriska indexet. För att stödja en primär nyckel lade jag till en extra rownumber ROWID1-kolumnen i tabellen: Ange en satsstorlek på 1 (upp till 5) Millionrader till kommandot för inmatning av inlägg hjälper till att fortsätta data till disken medan bulkinsatsen är igång (i stället för att spara allt i slutet) gör så det minskar minnetrycket på minnespoolen PookHK vi skapade. Databelastningen i den hållbara In-Memory-tabellen fullbordas om 5 minuter 28 sekunder eller 183K Rowssec. Det är okej men inte så mycket snabbare än vår baslinje. Titta på sys. dmoswaitstats visar att no.1 waitstat är IMPROVIOWAIT som uppstår när SQL Server väntar på en bulkbelastning IO för att slutföra. Titta på prestandatältet Bulk Copy Rowssec och Disk Write Bytessec visar spolningen till diskspikarna på 275 MBsec en gång en sats kom in (de gröna spikarna). Det är maximalt vad skivan kan leverera men förklarar inte allt. Med tanke på den mindre vinsten kommer vi att parkera den här för framtida utredning. Övervakning av minnesbassängen Via sys. dmresourcegovernorresourcepools dmv kan vi kontrollera om vår in-memory-tabell använder det nyskapade PoolHK-minnet Pool: Utgången visar att detta är fallet då 7,2GB (lite extra för Rowid) fick okomprimerad laddad i minnet poolHk pool: Om du försöker ladda mer data än du har minne tillgängligt för poolen får du ett korrekt meddelande som den här: Anmärkningen har blivit avslutad. Msg 701, Level 17, State 103, Line 5 Det finns otillräckligt systemminne i resurspoolen 8216PookHK för att köra den här frågan. Om du vill se en nivå djupare vid minnesutrymmeallokering på basis av per minnetabell kan du köra följande fråga (taget från SQL Server i minnet OLTP Internals för SQL Server 2016-dokument): De data som vi just laddade lagras som en Hastighetskonstruktion med hashindex: Hittills så bra Nu kan vi fortsätta och kolla in hur iscenesättning i ett icke-hållbart bord utför Bulk Insert i icke-hållbar in-memory-tabell För IMND-tabeller behöver vi inte en primär nyckel så vi bara lägg till och icke-grupperade Hash-index och sätt DURABILITY SCHEMAONLY. Bulkinsatsen Data laddning i det icke-hållbara bordet fullbordas inom 3 minuter med en genomströmning på 335K rowssec (vs 7 minuter). Detta är 2,3x snabbare och sedan sätts in i ett hopbord. För uppspelning av data är detta definitivt en snabb seger SSIS Single Bulk Insert i ett icke-hållbart bord Traditionellt är SSIS det snabbaste sättet att ladda en fil snabbt till SQL Server eftersom SSIS hanterar all data förbehandling så att SQL Server-motorn kan spendera sina CPU-fästingar på att fortsätta data till disken. Ska detta fortfarande vara fallet när du sätter in data i ett icke-hållbart bord Nedan följer en sammanfattning av testen som jag körde med SSIS för det här inlägget: SSIS Fastparse-alternativet och160 StandardBufferMaxRows och DefaultBufferSize-inställningarna är huvudprestanda. Även Native OLE DB (SQLOLEDB.1) - leverantören utför något bättre än SQL Native Client (SQLNCLI11.1). När du kör SSIS och SQL Server sida vid sida behöver du inte öka nätverkspaketstorleken.160160 Nettoresultat: Ett grundläggande SSIS-paket som läser en platt filkälla och skriver ut data direkt till tabellen Non-Durable via en OLE DB-destination utför liknande som kommandot Bulk Insert i ett IMND-bord: de 60 miljoner raderna laddas i 2minutes 59seconds eller 335K rowssec, identiskt med kommandot Bulk insert. SSIS med balanserad datadistributör Men wait8230160 in-memory-tabellerna är utformade för att fungera låsa amplåsfri så det betyder att vi kan ladda data också via flera strömmar. Det är lätt att uppnå med SSIS. Den balanserade datadistributören kommer bara med det (BDD är listad i den gemensamma delen av SSIS-verktygslådan) Att lägga till BDD-komponenten och sätta in data i samma icke-hållbart bord med 3 strömmar ger bästa möjliga genomströmning: vi är nu upp till 526000 Rowssec Titta på denna mycket plana linje med endast 160 av CPU-tid som används av SQLServer verkar det som om vi slår lite flaskhals: Jag försökte snabbt vara kreativ genom att utnyttja modulo-funktionen och tillade 2 fler dataflöden i paketet (varje behandling 13 av data) 160 men det som inte förbättrar mycket (1 min52sek) så ett bra ämne att undersöka för en framtida post160160 Alternativet In Memory Non-Durable-tabell ger en viss seriös prestationsförbättring för att lagra dataöverföringsdata 1,5 gånger snabbare med en vanlig Bulk Inser t och upp till 3,6 gånger gånger snabbare med SSIS. Det här alternativet, som främst är utformat för att påskynda OLTP, kan också göra en stor skillnad för att snabbt krympa ditt partifönster. (Fortsätt). Läser så snabbt som möjligt från ett bord med SSIS (del II). Nyligen bloggade jag om hur man ska vara selektiv som möjligt medan du läser från och OLE DB-datakälla (del I) och hur du laddar data från en enda plattfil så snabbt som möjligt till ett SQL Server-bord. Men du märkte säkert att Out of the box läser SSIS data snabbare från en platt fil än från en SQL-tabell. I den här artikeln delar jag mitt senaste trick på hur man påskyndar läsningen från ett enda bord, vanligtvis minst dubbelt så snabbt. Tiden att slå, Native OLE DB-källhastigheten Det första steget är som alltid att kontrollera standardmängden och varaktighet genom att räkna rader från Native OLE DB Data Source. I vårt fall tar det 14 minuter och 6 sekunder att läsa alla ca. 180 miljoner rader eller 12,89 GB från ett SQL Server-bord med 16 kolumner data. För jämförelse att läsa samma data från en enda platt fil tar det bara 7 minuter 57 sekunder. Så var kommer denna skillnad från? Först och främst på grund av den IO Packet-storlek som används som jag skulle säga. SSIS läser med 128 KB block från en platt fil och använder som standard 4KB (standard nätverkspaketstorlek) som kan bytas till max. 32 KB för att begära data från SQL Server (32 KB är också maximalt för den nya SQL2008 R2-versionen). För att hämta data snabbare måste vi hitta ett sätt att hantera fler IO-förfrågningar parallellt. Ange baslinjen bara ett paket och räkna raderna från den indiska OLEDB-källan. (Glöm inte att ändra paketstorlek från 0 till 32767 i Connection Manager). Windows Performance Monitor-räknare som ska kontrolleras är CPU-belastningen för både SSIS-processen (DtsDebughost när du kör paketet från BIDS eller DTEXEC när den startas från kommandoraden) och SQL Server-processen. Kontrollera även mängden byte som vi läser: välj IO Read bytessec-räknaren från processen.160 Lägga till parallellitet med hjälp av Modulo-algoritmen Optimeringstricket som jag vill påpeka är baserad på att använda den Modulo-aritmetiska operatören som kan vara används med den numeriska datatypen. Den returnerar återstoden av ett nummer dividerat med en annan. Om du till exempel vill läsa med 3 parallella strömmar kan användningen använda modulo 3 (3). Du kan hämta de 3 utmatningsflödena genom att ange som återstående värden 0,1 och 2,160 (Använda värden mindre än 0 eller större än 2 returnerar 0 rader när modulo 3 används.) Du kan också kontrollera utmatningen genom att köra frågan i SSMS eller med BIDS Preview-alternativet från OLEDB Source Editor. välj från dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 2 När du bygger den här frågan i ett SSIS-paket, kommer du märka att det i första hand skapar parallellitet, raderna är ganska snygga och läsas från de båda datakällorna, men tyvärr kommer den totala paketkörningstiden inte ner , vilket är lite konstigt.160 (Det är trots allt 160 att lägga till fler källor, ett optimeringsverktyg vi upptäckte redan för länge sedan, tillbaka 2004 när vi testade ett tidigt Beta 2-utgåvan av SSIS). Ange i ditt paket de flera datakällorna och slå samman resultatet med Union All-komponenten: Aktivitetsmonitorn visar oss snabbt att flera SPIDS är avfyras av SSIS-paketet, men många är upphängda och orsakar faktiskt onödig synkronisering: Lyckligtvis kan detta lösas snabbt genom att ange sökfrågan OPTION (MAXDOP 1). Detta tar bort synkroniseringskostnaden utan att offra på genomströmning i det här fallet. Läsa flera gånger från samma SQL-tabell Tid för att prova det och öka antalet datakällkomponenter i paketet. Varje OLE DB-datakälla ska peka på samma inmatningstabell, 160 modifiera bara modulofaktorn och utdataseparatorn när du lägger till mer. Det bästa resultatet som jag normalt uppnår medan du läser från samma datakälla 3 eller 4 gånger parallellt: När vi kontrollerar perfmonräknarna en gång till ser du att IO-genomströmningen faktiskt steg upp från initialt 21 MBsec till 46,6 MBsec i genomsnitt, så det har mer än fördubblats CPU-konsumtionen av både SSIS och SQLServer-processen har också ökat 160 SSIS från 100 till 350 (3,5 CPU) och SQLServer använder även en extra CPU. Den totala paketkörningsperioden för att läsa samma 180 miljoner rader (12,89 GB) 160 minskade från initialt 14 minuter 6 sekunder till endast 6 minuter och 33 sekunder när du läste data med 3 parallella strömmar. När du måste påskynda läsning av stora mängder data från ett enda bord i ditt SSIS-paket kan du minska längden med mer än hälften genom att lägga till någon form av parallellitet i ditt paket med hjälp av this160 Modulo-tricket. Normalt ger read160-data från samma SQL Server-tabell med 3 eller 4 parallella strömmar det bästa resultatet160 Läser så snabbt som möjligt från ett bord med SSIS (del II). 5.0 av 5 baserat på 22 omdömen 2 Rob Volk 2010-5-26 03:27 Kan du förklara varför du använder den här WHERE-klausulen: VAR (CAST (LORDERKEY AS VARCHAR) 3) Att kasta kolumnen till varchar är meningslöst om du vill att göra en modulo operation på den, eftersom det kommer att behöva göra en implicit gjuten (tillbaka) till en numerisk typ. Det kan inte påverka SSIS-operationerna men det kommer sannolikt att sakta ner SQL Server-delen. Henk 2010-5-26 10:31 Hej Rob, förklaringen är titeln på det här inlägget, 8220 för att läsa så snabbt som möjligt8221 -) med hjälp av gjutningen till varchar gör det bara lite snabbare, med 10 extra CPU-resurser för SQLServer bearbeta. Men you8217re right, it8217s lite förvirrande att ta bort gjutningen från frågan kommer också att göra trick: VÄLJ från dbo. LINEITEMHash96KeySSD VAR (LORDERKEY 3) 0 alternativ (maxdop 1) 3 Henk 2010-11-8 21:24 om du bara får 13 av data. Det kan hända att du glömde att ställa in modulfunktionen korrekt i var och en av träden. som: välj från dbo. LINEITEMHash96KeySSD VAR (LORDERKEY 3) 0 välj från dbo. LINEITEMHash96KeySSD VAR (LORDERKEY 3) 1 välj från dbo. LINEITEMHash96KeySSD VAR (LORDERKEY 3) 2 (Du säger att du behandlar 50 mill rader från en staging db i 6 min 138K rowssec med en SSIS rowcount-komponent kan du kontrollera om de totala radnumren matchar antalet rader i din databas. Det kommer också att berätta hur snabbt du kan läsa från din datakälla och den effektiva MBytesec. Kanske finns det lite mer utrymme för optimeringar). 4 Dan 2011-2-26 13:08 I8217m ny till parallellitet så förlåt mig om min fråga är elementär. Men jag är förvirrad på hur sökfrågan OPTION (MAXDOP 1) skulle leda till att spetsarna alla körs samtidigt. I8217ve läser att detta 8220Supphänger parallell plangenerering. Operationen kommer att utföras serially8221 (Funnet vid stackoverflowquestions163917optionmaxdop-1-in-sql-server). Det får mig att tro att OPTION (MAXDOP 1) endast tillåter en CPU. Wouldn8217t det är bättre att använda alla möjliga CPU8217s Henk 2011-2-26 22:38 tricket är att du startar flera separata sessioner till SQL-servern, varje session tjänar en del av data, och8230 varje session (SPID) är hanteras faktiskt av bara 1 CPU (SQL-schemaläggare) genom att undertrycka parallellitet inom varje session du får prestanda (som visas på bilden, annars har SPIDS primär status 8220suspended8221, så det gör inte något användbart) Men när du läser från ett partitionerat bord, vilket är en multithreaded operation av naturen, kommer du att se att använda ett högre maxdop nummer kan öka genomströmningen. (läs mer om detta ämne på: henkvandervalkoptimizing-sql-in-memory-table-scan-processing-speed. där läsning från en partitionerad tabell använder 88 kärnor)) Hoppas det hjälper Brgds, Henk 5 Dan 2011-3-1 20 : 37 Så stoppar (MAXDOP 1) cpu från att arbeta med andra processer samtidigt och koncentrerar sig på aktuell fråga Henk 2011-3-2 10:38 Dan, du kan inte hoppa in i den slutsatsen: med MAXDOP 1 frågan kommer att hanteras av en enda SQL Scheduler som använder en enda CPU. Men om det finns flera frågor att tjäna, kommer dessa också att schemaläggas. 6 Dan 2011-3-4 21:44 Henk, jag har en ssis pkg där jag har 29 tabeller I8217m som vill ladda om hur du har beskrivit ovanstående. I8217m lägger träden ett träd i taget och tittar på preformansen. Det första trädet sprang sig själv, men när I8217ve har lagt till en annan, finns det 6 session ids men endast 3 körs, 1 säger suspenderad och de andra 2 har inget i statusen. Jag ställer in egenskapen pkg MaxConcurrentExecutables 3 (Har bara 2 DataFlows för tillfället) och har satt varje dataflow8217s EngineThread 3 för att hantera de tre datapiplinierna per dataflöde. I8217m kör det här i ett jobb i en SQL Server 2005-låda med 8 3.2 Gig-processorer och 64 Gig Ram (vilken I8217ve aldrig sett över 50, kanske inställningen för minnesminne som är allokerad för SQL Server behöver justeras). Om man tittar på aktivitetsmonitorn stannar processorns tid ganska i 30 till 50-serien vilket gör mig arg att det finns tillräckligt många processortider tillgängliga, men mina sessioner är i suspenderat tillstånd. Något aning vad I8217m gör fel 7 Steve 2011-4-2 19:06 Henk, Bra förslag, särskilt som moduloperatören att dela i exakt hur många parallella strömmar man vill använda. I8217ve har använt en sekvensbehållare med flera datakällor inuti den för att få den att köra separata trådar för parallellbelastningar (från fjärran Oracle source) istället för din metod ovan med facket all och maxdop. Vet du om det finns nackdelar med att använda den här metoden (orsakar det att cxpacket-synkronisering väntar) Det verkar fungera korrekt, och I8217ll försöker köra några tester men bara om du försökte detta och visste. Henk 2011-4-2 22:35 Om du läser från olika tabeller med flera datakällor är det helt bra. Från vad I8217ve sett på kundwebbplatser är det normalt lite långsammare att läsa från Oracle-tabeller än att skriva in i SQLServer-tabellen. Du kan kontrollera det här med hjälp av Rowcount-komponenttricket för att kontrollera hur snabbt du faktiskt kan läsa med bara datakällorna. (använder du separata nedladdningsbara Attention Microsoft-kontakter för Oracle från Microsoft Download Center) Om det behövs kan du skriva ut data i flera SQL Server-partitionstabeller med modulo-tricket. (Vänligen don8217t glömmer att även kolla med Taskmanager nätverket genomströmning nummer :-)) Låt mig veta dina resultat lycka till Henk 8 bender 2011-10-26 20:22 9 Nik - Shahriar Nikkhah 2012-7-31 19:14 Hi Henk tror du att 8220OFFSET och FETCH8221 kommer att hjälpa till i det här exemplet antar jag att det kommer, i princip vill det spela rollen som 8220WHERE (LORDERKEY 3) 28221. Jag har inte testat det ännu. Jag gjorde vad optimering och laddade en 10 GB CSV-fil på 7m51s på ett Fast Track. Vad tycker du Henk 2013-4-12 19:28 10GB 471 Seconds 21.7 MBsec Det här ser ut som en enda Bulk Insert-uppgift Är denna genomströmningstid accepterad för dig? Om inte, har du försökt några alternativ för att skapa parallellitet (med SSIS och modulo trick eller ladda flera mindre filer parallellt) Brgds, Henk Det fungerar bra. Så glad att jag hittade den här sidan. Jag har använt MAXDOP 1 på alla mina stora borddrag i många å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..)