Wie kann ich dafür sorgen, dass mein SSIS-Prozess mehr Ressourcen verbraucht und schneller läuft?

Question

Ich habe einen täglichen ETL-Prozess in SSIS, der mein Lager aufbaut, sodass wir Tagesberichte bereitstellen können.

Ich habe zwei Server – einen für SSIS und den anderen für die SQL Server-Datenbank. Der SSIS-Server (SSIS-Server01) ist eine Box mit 8 CPUs und 32 GB RAM. Die SQL Server-Datenbank (DB-Server) ist eine weitere Box mit 8 CPUs und 32 GB RAM. Beide sind virtuelle VMWare-Maschinen.

In seiner stark vereinfachten Form liest SSIS 17 Millionen Zeilen (ca. 9 GB) aus einer einzigen Tabelle auf dem DB-Server, repivotiert sie auf 408 Millionen Zeilen, führt einige Nachschlagevorgänge und eine Menge Berechnungen durch und aggregiert sie dann wieder auf ca. 8 Millionen Zeilen, die jedes Mal in eine brandneue Tabelle auf demselben DB-Server geschrieben werden (diese Tabelle wird dann in eine Partition verschoben, um Tagesberichte bereitzustellen).

Ich habe eine Schleife, die Daten im Umfang von 18 Monaten gleichzeitig verarbeitet – insgesamt also 10 Jahre an Daten. Ich habe 18 Monate gewählt, basierend auf meiner Beobachtung der RAM-Nutzung auf dem SSIS-Server – nach 18 Monaten verbraucht er 27 GB RAM. Wenn dieser Wert höher ist, beginnt SSIS mit dem Puffern auf der Festplatte und die Leistung sinkt drastisch.

Hier ist mein Datenflusshttp://img207.imageshack.us/img207/4105/dataflow.jpg

ich benutzeDer Balanced Data Distributor von Microsoftum Daten über 8 parallele Pfade zu senden und so die Ressourcennutzung zu maximieren. Ich führe eine Vereinigung durch, bevor ich mit der Arbeit an meinen Aggregationen beginne.

Hier ist das Task-Manager-Diagramm vom SSIS-Server

Hier ist ein weiteres Diagramm, das die 8 einzelnen CPUs zeigt

Wie Sie diesen Bildern entnehmen können, steigt der Speicherverbrauch langsam auf etwa 27 G, da immer mehr Zeilen gelesen und verarbeitet werden. Die CPU-Auslastung liegt jedoch konstant bei etwa 40 %.

Die zweite Grafik zeigt, dass wir nur 4 (manchmal 5) von 8 CPUs verwenden.

Ich versuche, den Prozess schneller laufen zu lassen (er nutzt nur 40 % der verfügbaren CPU).

Wie kann ich diesen Prozess effizienter gestalten (wenig Zeit, möglichst viele Ressourcen)?

Answer 1

Nach den guten Vorschlägen von bilinkc und ohne zu wissen, wo der Engpass ist, würde ich noch ein paar andere Dinge ausprobieren.

Wie Sie bereits bemerkt haben, sollten Sie an Parallelität arbeiten und nicht mehr Daten (Monate) im selben Datenfluss verarbeiten. Sie haben bereits Transformationen parallel ausgeführt, aber Quelle und Ziel (sowie Aggregation) laufen nicht parallel! Lesen Sie also bis zum Ende und denken Sie daran, dass Sie sie auch parallel ausführen sollten, um Ihre CPU-Leistung zu nutzen. Und vergessen Sie nicht, dass SieSpeichergebunden (eine unbegrenzte Anzahl von Monaten kann nicht in einem Batch aggregiert werden), daher besteht die Vorgehensweise („Scale-Out“) darin, einen Datenblock abzurufen, ihn zu verarbeiten und ihn so schnell wie möglich in die Zieldatenbank zu übertragen. Dies erfordert die Beseitigung gemeinsamer Komponenten (eine Quelle, eine Vereinigung aller Komponenten), da jeder Datenblock auf die Geschwindigkeit dieser gemeinsamen Komponenten beschränkt ist.

Quellbezogene Optimierung:

• Probieren Sie mehrere Quellen (und Ziele) im selben Datenfluss anstelle eines Balanced Data Distributor aus. Sie verwenden einen gruppierten Index für die Datumsspalte, damit Ihr Datenbankserver Daten in datumsbasierten Bereichen schnell abrufen kann. Wenn Sie das Paket auf einem anderen Server ausführen als dem, auf dem sich die Datenbank befindet, erhöhen Sie die Netzwerkauslastung.

Transformationsbezogene Optimierung:

• Müssen Sie wirklich „Union All“ vor dem Aggregieren durchführen? Wenn nicht, werfen Sie einen Blick auf die zielbezogene Optimierung für mehrere Ziele.
• SatzKeys, KeyScale und AutoExtendFactorfür die Aggregatkomponente, um erneutes Hashing zu vermeiden – wenn diese Eigenschaften falsch eingestellt sind, wird während der Paketausführung eine Warnung angezeigt. Beachten Sie, dass die Vorhersage optimaler Werte für Batches mit einer festen Anzahl von Monaten einfacher ist als für eine unendliche Anzahl (wie in Ihrem Fall 18 und steigend).
• Erwägen Sie die Aggregation und (Un-)Pivotierung in SQL Server, anstatt dies in einem SSIS-Paket zu tun - SQL Server übertrifft Integration Services bei diesen Aufgaben; natürlich kann die Transformationslogik so beschaffen sein, dass die Aggregation vor der Durchführung einiger Transformationen im Paket verboten ist.
• Wenn Sie beispielsweise monatliche Daten in der Datenbank aggregieren (und pivotieren/entpivotieren) können, versuchen Sie dies in der Quellabfrage oder in der Zieldatenbank mit SQL. Abhängig von Ihrer Umgebung kann das Schreiben in eine separate Tabelle in der Zieldatenbank, das Erstellen eines Indexes und SELECT INTO mit Aggregierung mit SQL schneller sein als dies im Paket zu tun. Beachten Sie, dass die Parallelisierung solcher Aktivitäten Ihren Speicher stark beansprucht.
• Am Ende haben Sie einen Multicast. Ich weiß nicht, wie viele Zeilen dort landen, aber beachten Sie Folgendes: Schreiben Sie in das Ziel rechts (auf dem Screenshot) und füllen Sie dann die Datensätze in der SQL-Abfrage in das Ziel links ein (um eine zweite Aggregation zu vermeiden und Ressourcen freizugeben – SQL Server erledigt dies wahrscheinlich viel schneller).

Zielbezogene Optimierung:

• verwendenSQL Server-Zielwenn möglich (Paket muss auf demselben Server wie Datenbank ausgeführt werden und Zieldatenbank muss SQL Server sein); beachten Sie, dass eine exakte Übereinstimmung der Spaltendatentypen erforderlich ist (Pipeline -> Tabellenspalten)
• erwägen Sie die EinstellungWiederherstellungsmodellzu Simple in Ihrer Zieldatenbank (Data Warehouse)
• Ziele parallelisieren - statt Vereinigung aller + Aggregat + Ziel separate Aggregate und separate Ziele (zur gleichen Tabelle) verwenden; hierbei sollten Sie berücksichtigenPartitionierungIhre Zieltabelle und das Platzieren von Partitionen in separaten Dateigruppen; wenn Sie Daten Monat für Monat verarbeiten, erstellen Sie Partitionen nach Monat und verwenden Sie Partitionsumschaltung

Scheint, als wäre ich mir nicht sicher, welchen Weg ich mit der Parallelität einschlagen soll. Sie können Folgendes versuchen:

• Wenn Sie mehrere Quellen in einen einzigen Datenfluss einfügen, müssen Sie die Transformationslogik und das Ziel für jede Quelle kopieren und einfügen
• Ausführen mehrerer Datenflüsse parallel, wobei jeder Datenfluss nur einen Monat verarbeitet
• Ausführen mehrerer Pakete parallel, wobei jedes Paket einen Datenfluss hat, der nur einen Monat verarbeitet; und ein Masterpaket zur Steuerung der Ausführung jedes (Monats-)Pakets – dies ist die bevorzugte Methode, da Sie das Paket wahrscheinlich nur einen Monat lang ausführen werden, sobald Sie mit der Produktion beginnen
• oder wie zuvor, jedoch mit Balanced Data Distributor und Union All und Aggregate

Bevor Sie irgendetwas anderes tun, sollten Sie einen kurzen Test durchführen: Nehmen Sie Ihr Originalpaket, ändern Sie es so, dass es 1 Monat lang verwendet wird, erstellen Sie eine exakte Kopie, die einen weiteren Monat verarbeitet, und führen Sie diese Pakete parallel aus. Vergleichen Sie es mit Ihrem Originalpaket, das 2 Monate lang verarbeitet. Machen Sie dasselbe für 2 separate 6-Monats-Pakete und ein einzelnes 12-Monats-Paket gleichzeitig. Ihr Server sollte dann mit voller CPU-Auslastung laufen.

Versuchen Sie, eine übermäßige Parallelisierung zu vermeiden, da Sie mehrere Schreibvorgänge zum Ziel haben werden. Sie möchten also nicht 18 parallele Monatspakete starten, sondern für den Anfang lieber 3 oder 4.

Und schließlich bin ich der festen Überzeugung, dass der Speicher- und Ziel-E/A-Druck beseitigt werden muss.

Bitte informieren Sie uns über Ihre Fortschritte.

Answer 2

VerwendenProcess Explorerum etwas mehr Ressourcennutzung (Speicher und IO) aufzudecken. Beachten Sie, dass das Disk-IO-Diagramm etwas irreführend sein kann, da die Spitzen im Diagramm oft auf die Caching-Fähigkeiten der Festplatten zurückzuführen sind. Wenn also Disk-IO der Engpass ist, wird dies im Diagramm nicht immer sofort angezeigt.

In manchen Fällen kann es hilfreich sein, ein RAM-Laufwerk zu installieren und die temporären Verzeichnisse dort abzulegen. Ich habe erfolgreichDieses hierum die Zeit zu reduzieren, die unsere Build-Maschine benötigt, um jede Nacht einen vollständigen Build durchzuführen und Tests auszuführen. Ich bin mir jedoch nicht sicher, ob SSIS davon profitieren würde.

Answer 3

(ich poste meine ursprüngliche Antwort noch einmal, habe BDD nicht berücksichtigt)

Letztendlich ist jede Verarbeitung an einen von vier Faktoren gebunden

• Erinnerung
• CPU
• Scheibe
• Netzwerk

Der erste Schritt besteht darin, den limitierenden Faktor zu identifizieren und dann zu bestimmen, ob Sie ihn beeinflussen können (mehr davon erwerben oder die Nutzung reduzieren).

Komponentenauswahl

Der Grund, warum der Speicher Ihres Servers erschöpft ist, wenn Sie mehr als 18 Monate arbeiten, hängt damit zusammen, warum die Verarbeitung so lange dauert.Pivot- und Aggregattransformationensind asynchrone Komponenten. Jeder Zeile, die von der Quellkomponente kommt, sind N Bytes Speicher zugewiesen. Derselbe Daten-Bucket besucht alle Transformationen, lässt ihre Operationen anwenden und wird am Ziel geleert. Dieser Speicher-Bucket wird immer wieder verwendet.

Wenn eine asynchrone Komponente hinzukommt, wird die Pipeline geteilt. Der Bucket, der diese Datenzeile transportiert hat, muss nun in einen neuen Bucket geleert werden, um die Pipeline abzuschließen. Das Kopieren von Daten zwischen Ausführungsbäumen ist ein kostspieliger Vorgang in Bezug auf Ausführungszeit und Speicher (könnte sich verdoppeln). Dies verringert auch die Möglichkeit für die Engine, einige der Ausführungsmöglichkeiten zu parallelisieren, während sie auf den Abschluss der asynchronen Vorgänge wartet. Eine weitere Verlangsamung der Vorgänge ergibt sich aus der Art der Transformationen. Das Aggregat ist eine vollständig blockierende Komponente, sodassalledie Daten müssen eintreffen und verarbeitet werden, bevor die Transformation eine einzelne Zeile an die nachgelagerten Transformationen freigibt.

Wenn möglich, können Sie die Pivot- und/oder Aggregat-Tabelle auf den Server übertragen? Dadurch sollten die im Datenfluss verbrachte Zeit und die verbrauchten Ressourcen verringert werden.

Sie können versuchen, die Anzahl der parallelen Vorgänge zu erhöhen, die der Engine zur Verfügung stehen.Jamies Artikel,SQL CATs Artikel

Wenn Sie wirklich wissen möchten, wo Ihre Zeit im Datenfluss verbracht wird, protokollieren Sie die OnPipelineRowsSent für eine Ausführung. Dann können Sie dies verwendenAbfrageum es auseinander zu nehmen (nachdem sysssislog durch sysdtslog90 ersetzt wurde)

Netzwerkübertragung

Basierend auf Ihren Diagrammen scheint es nicht so, als ob die CPU oder der Speicher auf einer der beiden Boxen beansprucht werden. Ich glaube, Sie haben angegeben, dass sich Quell- und Zielserver auf einer einzigen Box befinden, das SSIS-Paket jedoch auf einer anderen Box gehostet und verarbeitet wird. Sie zahlen nicht unerhebliche Kosten für die Übertragung dieser Daten über das Kabel und wieder zurück. Ist es möglich, die Daten auf dem Quellserver zu verarbeiten? Sie müssten dieser Box mehr Ressourcen zuweisen, und ich drücke die Daumen, dass es sich um eine große, leistungsstarke VM handelt und das kein Problem ist.

Wenn das nicht möglich ist, versuchen Sie die EinstellungenPaketgrößeEigenschaft des Verbindungsmanagers auf 32767 und sprechen Sie mit dem Netzwerkbetreiber darüber, ob Jumbo-Frames für Sie geeignet sind. Beide Tipps finden Sie im Abschnitt „Optimieren Sie Ihr Netzwerk“.

Ich bin nicht sehr gut mit Festplattenzählern, aber Sie sollten in der Lage sein zu erkennen, ob die Wartetypen mit der Festplatte zusammenhängen.