In diesem Video wird die Steigerung der Datenflussleistung in einem Integration Services-Paket veranschaulicht. Sie erfahren, wie die folgenden Phasen des Datenflusses optimiert werden können:

Extrahieren
Transformieren
Laden

Diese Optimierungstipps zum Steigern der Leistung können beim Entwerfen, Entwickeln und Ausführen des Datenflusses angewendet werden.

Danksagungen zum Video

Vielen Dank an Thomas Kejser für seinen Beitrag zum Material der Reihe SSIS: Designing and Tuning for Performance SQL Server Video Series. Dies ist das zweite Video der Reihe.

Vielen Dank an Carla Sabotta und Douglas Laudenschlager für die Beratung und ihr wertvolles Feedback.

Videotranskript

Video-Zeitstempel	Audio
00:00	Hallo, mein Name ist David Noor. Ich bin Leiter der Entwicklungsabteilung für SQL Server Integration Services bei Microsoft. Willkommen bei diesem Video zum Optimieren des SSIS-Paketdatenflusses im Unternehmen. Bei diesem Video handelt es sich um den zweiten Teil einer vierteiligen Reihe mit dem Titel SSIS: Designing and Tuning for Performance. Im ersten Teil der Reihe hat Denny gezeigt, wie die Leistung von SSIS-Paketen am besten gemessen und interpretiert wird. In dieser Folge werden wir darauf aufbauen und uns ansehen, wie die Leistung des SSIS-Paketdatenflusses gesteigert werden kann. Zunächst werden wir die Themen identifizieren, die alle Datenflüsse gemeinsam haben. Anschließend überlegen wir, in welchen Bereichen des Datenflusses Sie mit dem Optimieren der Leistung beginnen sollten. Sobald spezifische Leistungsprobleme ermittelt wurden, kann eine Menge unternommen werden, um den Datenfluss schneller und effizienter zu gestalten. Zum Schluss gebe ich Ihnen ein paar Tipps, die Sie beim Entwerfen, Entwickeln und auch beim Ausführen des Datenflusses anwenden können. Los geht´s! In den meisten Paketdatenflüssen liegt der Schwerpunkt auf dem Extrahieren, Transformieren und Laden wichtiger Daten. Dieser Vorgang wird auch als ETL bezeichnet. Der Datenfluss wird optimiert, indem die einzelnen Phasen, d. h. Extrahieren, Transformieren und Laden, optimiert werden. Im Hinblick auf die Extraktion überlegen wir, wie die SSIS-Quellkomponenten und die ausgeführten Abfragen optimiert werden können, um die höchste Leistung mit den geringsten Auswirkungen auf die Quellsysteme zu erzielen. Beim Optimieren der Transformationen im Datenfluss betrachten wir die erforderlichen Arbeitsschritte und wählen die beste Herangehensweise aus, um diese Arbeit zu erledigen, indem wir unnötige Schritte auslassen. Gelegentlich werden auch die ursprünglichen Quellabfragen geändert, um die Transformationen zu automatisieren. Unter dem Optimieren des Ladevorgangs Ihres Datenflusses verstehen wir schließlich das Optimieren von SSIS-Zielkomponenten, Netzwerken, Sperren und anderen Faktoren, die den Datenfluss beim Laden der Daten am Zielort beeinträchtigen können. In diesem Video werde ich Ihnen praktische Tipps zum Ermitteln von Optimierungsmöglichkeiten für alle drei Phasen des Datenflusses geben. Beachten Sie zunächst, dass es bei der Behandlung von Leistungsproblemen in der Regel keinen Königsweg gibt. Es gibt keinen Tipp, mit dem alle im Datenfluss auftretenden Leistungsprobleme behoben werden können. Die Tipps funktionieren in allgemeinen Fällen sehr gut. Allerdings sollte ein Problem nicht einfach mit einer Reihe von Tipps überschüttet werden. Es ist stets am besten, zunächst zu verstehen, was mit den Datenflüssen beabsichtigt wird. Anschließend sollten Sie überprüfen, ob die Datenflüsse diesen Zielen entsprechen und die aktuelle Leistung messen. Durch iterative Änderungen am Datenfluss können Sie dann wiederum erkennen, ob die Leistung erhöht wurde oder nicht. Es passiert schnell, dass man sich bei der Behandlung eines Leistungsproblems auf eine bestimmte Technologie oder Komponente fixiert. Dies geschieht insbesondere dann, wenn die Technologie oder der Code vertraut sind. Versuchen Sie, sich den Datenfluss als eine Art Ökosystem vorzustellen, das sich aus mehreren relationalen Datenbanken, einem Netzwerk, verschiedenen Betriebs- und Dateisystemen sowie vielen anderen Komponenten zusammensetzt. Je mehr Sie über das gesamte Ökosystem wissen, desto besser können Sie erkennen, wo die Leistungsprobleme der einzelnen Teile liegen. Diese können Sie dann effektiver und mit geringerem Risiko ändern.
03:38	Beginnen wir am Anfang des Datenflusses, beim Extrahieren. Wenn Sie SQL Server oder eine beliebige andere Datenbank mit einer Massenschnittstelle als Quelle verwenden, sollten Sie versuchen, die Paketgröße zu erhöhen. Der SQL Server-Standardwert 4096 funktioniert in der Regel gut. Da beim Extrahieren jedoch große Mengen von Daten verschoben werden, ist eine Erhöhung nützlich. Dazu sollten Sie den Netzwerkadministrator bitten, die Großrahmen-Funktion für das Netzwerk zu aktivieren. Überprüfen Sie jedoch, welche Auswirkungen dies auf Ihr Paket hat. Wenn Sie für Massenvorgänge wie OLE DB-Quellen und für einzelne Zeilen (z. B. OLE DB-Befehle) denselben Verbindungs-Manager verwenden, sollten Sie unter Umständen einen zweiten Verbindungs-Manager für die OLE DB-Befehle verwenden und eine kleinere Paketgröße auswählen. Wenn Sie den ETL-Vorgang unter Windows 2008 auf einem Computer mit mehreren Kernen und mehreren NICs ausführen, erzielen Sie eine höhere Netzwerkleistung, indem Sie den einzelnen Kernen Netzwerkkarten zuordnen. Weitere Informationen finden Sie auf der MSDN-Website in dem Blogeintrag Scaling Heavy Network Traffic with Windows (in englischer Sprache). Eines der einfachsten Mittel zum Optimieren der Extraktion ist das Optimieren der verwendeten Abfragen. Es mag manchmal einfacher erscheinen, beim Extrahieren einer Tabelle einfach die ganze Tabelle auszuwählen und alle Spalten abzurufen. Sie erhalten jedoch wesentlich bessere Ergebnisse, wenn Sie nur die Spalten auswählen, die wirklich erforderlich sind. Dadurch werden nicht nur der Netzwerkverkehr und der verwendete Speicher verringert. Auch der Datenbankserver kommt beim Ausführen der Abfrage wahrscheinlich mit weniger E/A-Vorgängen aus. Wie hier gezeigt, können Sie in SQL Server zudem über einen Hinweis angeben, dass während der Auswahl keine gemeinsamen Sperren ausgeben werden. Auf diese Weise kann die Abfrage Daten lesen, die möglicherweise modifiziert wurden oder für die kein Commit ausgeführt wurde. Verwenden Sie diese Möglichkeit nur in Fällen, in denen unbedingt höchste Leistung erforderlich ist. Dadurch können Sie sich sicher sein, dass das Lesen modifizierter Daten für den ETL-Auftrag stets möglich ist. Suchvorgänge können sowohl als Extraktionen als auch als Transformationen betrachtet werden. So oder so ist es ratsam, einige der Ideen aus der vorherigen Folie anzuwenden. Hier ist es noch wichtiger, nur erforderliche Spalten auszuwählen. Dies dient nicht nur der Entlastung des Netzwerks, auch der Cache im Arbeitsspeicher wird optimiert. Der neue, freigegebene Suchcache in SQL Server 2008 ist ein ausgezeichnetes Feature zur Leistungssteigerung. Wenn ein Suchcache freigegeben ist, können die Verweisdaten direkt abgerufen und in mehreren Suchvorgängen des Pakets wiederverwendet werden. Wenn der Cache in einer Datei gespeichert wird, können Sie ihn sogar paketübergreifend verwenden. Wenn Sie mehrere Suchkomponenten auf dieselbe Tabelle verweisen, sollten Sie diese neue Option ausprobieren. Die Geschwindigkeit der Pakete kann damit wesentlich erhöht werden. Die Verwendung ist ganz einfach. Erstellen Sie in Ihrem Paket einen Cacheverbindungs-Manager, füllen Sie den Cache mithilfe der neuen Cachetransformation auf, und ändern Sie anschließend Ihre Suchvorgänge, sodass sie die Verweisdaten über diese Cacheverbindung abrufen.
06:29	Kommen wir nun zur zweiten Phase von ETL, der Transformation. Bei der Transformation arbeiten Sie mit Daten, die bereits in den Datenfluss geladen wurden, und versuchen, diese vor dem Laden anzupassen. In SSIS wird jede Transformation als Objekt angezeigt, dass Sie auf Ihren Entwurf ziehen können. Doch nicht alle Transformationen sind gleich. Werfen wir einen Blick auf die drei Arten von Transformationen in SSIS: Synchrone oder zeilenbasierte Transformationen werden in Datenpuffern vor Ort ausgeführt. Beim Durchlaufen werden keine Kopien der Datenpuffer oder der Zeilen erstellt. Die Daten werden direkt im Puffer transformiert. Synchrone Transformationen sind daher ziemlich schnell. Synchrone Transformationen sind zum Beispiel die Datenkonvertierung, eine abgeleitete Spalte oder eine Suche. Teilweise blockierende, asynchrone Transformationen unterscheiden sich hiervon. Bei diesen Transformationen müssen die Daten beibehalten werden, damit die Transformation ausgeführt werden kann. Dazu werden die Daten von der Transformation in interne Puffer kopiert. Hierfür ist Speicher erforderlich, bisweilen sehr viel Speicher. Bei diesen Transformationen wird während des Einlesens der Daten mit dem Schreiben von Ausgabedaten begonnen. Dabei wird der entsprechende interne Speicher freigegeben, in dem die Daten enthalten waren. Nach dem Abschluss des Datenflusses wird der gesamte verwendete Speicher freigegeben. Jedoch verarbeiten diese Transformationen in der Regel eine erhebliche Datenmenge, bis es soweit ist. Beispiele für diese Art der Transformation sind Zusammenführen, Zusammenführungsverknüpfungen und UNION ALL. Suchen Sie nach Optimierungsmöglichkeiten, wenn Ihr Datenfluss diese Transformationen enthält. Sind redundante Kopien vorhanden? Gibt es Zusammenführungsverknüpfungen oder Unions, die Sie in das Quellsystem verschieben können? Ich habe Pakete gesehen, in denen statt dem Ausführen von JOIN für die Quellabfrage alle Daten mithilfe von OLE DB-Quellen aus zwei Tabellen abgerufen wurden, und anschließend im Paket eine Zusammenführungsverknüpfung verwendet wurde. Stattdessen hätte einfach eine SQL-Verknüpfung an der Quelle erstellt werden können. Dies wäre aufgrund der Optimierung der Abfrage in der Datenbank viel schneller gewesen. Suchen Sie nach solchen Möglichkeiten, um die Anzahl asynchroner Transformationen zu verringern. In SQL Server 2008 haben wir einiges an der Datenfluss-Taskplanung geändert, um komplexe Datenflüsse leistungsfähiger zu machen und verfügbare CPUs besser zu nutzen. Wenn Sie SSIS aus SQL Server 2005 kennen, haben Sie in Ihrem Datenfluss möglicherweise eine UNION ALL-Transformation verwendet, um die Ausführungsstrukturen künstlich aufzuteilen und zu parallelisieren. Dies sollte nicht mehr notwendig sein. Aufgrund der Verbesserungen in SQL Server 2008 sind diese Transformationen nicht mehr erforderlich. Entfernen Sie diese UNION ALL-Komponenten, um die Leistung zu verbessern. Die dritte Gruppe, die blockierenden asynchronen Transformationen, sind so etwas wie Extremversionen der vorherigen Gruppe. Diese Transformationen müssen ALLE Eingabedaten vor dem Schreiben von Ausgabedaten enthalten. Das Verwenden dieser Transformationen in Datenflüssen mit großen Datenbatches kann den Datenfluss oft deutlich verlangsamen. Wenn Sie diese Transformationen in umfangreichen Datenflüssen verwenden, achten Sie darauf, Redundanz zu vermeiden. Überarbeiten Sie das Paket, wenn dieselben Daten im selben Fluss zweimal aggregiert oder sortiert werden. Kommen wir nach diesen Grundlagen noch zu einigen speziellen Tipps: Bei Datenflüssen muss häufig der Datentyp einer Spalte transformiert werden. Versuchen Sie, dies nur einmal auszuführen, und verwenden Sie möglichst eingeschränkte Datentypen, um die Datenpuffer überschaubar zu halten. Auch für die Umwandlung von Daten wird CPU-Zeit benötigt, und wenn vom gesamten Datenfluss ein Typ für eine bestimmte Spalte verwendet werden kann, sollten Sie in Betracht ziehen, diese Spalte in der Datenquelle mit den SQL-Funktionen CAST oder CONVERT umzuwandeln, bzw. mit deren Entsprechungen im SQL-Dialekt Ihrer Datenbank. Wie ich bereits einige Folien zuvor erwähnt habe, sollten Sie beim Optimieren Ihrer Transformationen genau über deren Einsatzort nachdenken. Scheuen Sie nicht davor zurück, SQL in Ihren Quellen zu Ihrem Vorteil zu nutzen. Ziehen Sie zum Beispiel beim Sortieren von Quelldaten in Betracht, im SQL in Ihrer Quelle ORDER BY-Klauseln zu verwenden. Ihre Datenbank kann die sortierten Daten möglicherweise viel effizienter zurückgeben als der Datenfluss. Verwenden Sie die Sortierkomponente im Datenfluss nur, um Daten zu sortieren, die aus unterschiedlichen Quellen zusammengeführt werden. Ebenso können einige Aggregationen mit einer GROUP BY- und einer SQL-Aggregatfunktion in der Quelle schneller ausgeführt werden. Wenn Sie SQL Server 2008 und die Slowly Changing Dimension (SCD)-Komponente verwenden, sollten Sie sich mit der neuen MERGE-Funktion in SQL Server vertraut machen. MERGE kann viele Aufgaben von SCD ausführen, benötigt jedoch wesentlich weniger Netzwerkroundtrips. Denken Sie außerdem an die INSERT INTO-Funktion von SQL. Wenn es sich um einen einfachen Datenfluss handelt, und sich sowohl Quelle als auch Ziel in derselben Datenbankinstanz befinden, können Sie die Daten möglicherweise viel schneller mit einer einzigen SQL-Anweisung verschieben, mit der die Daten vollständig innerhalb der Datenbank verschoben werden. In diesen Fällen kann INSERT INTO wesentlich schneller ausgeführt werden als ein Datenfluss, da die Daten auf dem Server verbleiben. Zuletzt sollten Sie beim inkrementellen Laden auch die Alternative eines einfachen Neuladens im Auge behalten. Einige Systeme, die ich gesehen habe, verbrauchen viel Zeit für die Deltaerkennung, um das erneute Laden zu vermeiden, jedoch machen die hierfür verwendete E/A und CPU die Abarbeitung wesentlich langsamer.
11:59	Kommen wir zur Ladephase. Beim Laden in SQL Server stehen zwei Optionen zur Verfügung: Bei der ersten Option handelt es sich um die SQL Server-Zielkomponente. Diese Komponente nutzt zwischen dem Datenfluss und dem Datenbankmodul freigegebenen Speicher, um die Daten rasch zu laden, kann aber nur verwendet werden, wenn der Datenfluss immer auf demselben Computer ausgeführt wird wie SQL Server. Zusätzlich sind für das SQL Server-Ziel einige dokumentierte Beschränkungen bezüglich der Fehlerbehandlung zu beachten. Die andere Option für schnelles Laden in SQL Server ist das OLE DB-Ziel, das oftmals fast so schnell ausgeführt wird wie das SQL-Ziel. Bei beiden Optionen führt das Verwenden einer Commitgröße von 0 zum schnellsten Laden. Es ist eine allgemein übliche Vorgehensweise, Indizes bei großen Ladevorgängen auch im Zielsystem löschen, allerdings sollten Sie einige Richtlinien für den Zeitpunkt beachten. Eine allgemeine Richtlinie ist das Löschen von Indizes auf Grundlage des durch das Laden verursachten prozentuellen Auslastungsanstiegs und der Arten der in der Tabelle enthaltenen Indizes: Wenn sich ein einzelner gruppierter Index in Ihrer Tabelle befindet, löschen Sie diesen nicht. Die Tabelle ist nach diesem Schlüssel geordnet, und die Zeit, die zum Löschen, Einfügen und Neuerstellen benötigt wird, ist praktisch nie kleiner als die für das Laden mit gruppiertem Index. Wenn sich in Ihrer Tabelle ein einzelner nicht gruppierter Index befindet, sollten Sie in Betracht ziehen, diesen zu löschen, wenn durch das Laden die Datengröße um etwa hundert Prozent ansteigt. Dies ist eine Faustregel, kein exakter Wert; im Allgemeinen ist es jedoch wahrscheinlich, dass es nicht lohnenswert ist, den Index zu löschen und neu zu erstellen, wenn die Größe der Tabelle sich nicht verdoppelt. Wenn sich in Ihrer Tabelle mehrere Indizes befinden, ist es weitaus schwieriger, eine Faustregel anzugeben. Ich würde sagen, hier liegt der Steigerungsbereich bei etwa 10 %. Beispielsweise können beim Laden von weniger als 10 % des aktuellen Umfangs die Indizes erhalten bleiben. Hier sollten Sie experimentieren und Messungen vornehmen. Für Ladevorgänge in einer Tabelle, für die weitere Aktivitäten ausgeführt werden, müssen Sie eine Strategie entwickeln. Das Massenladen sperrt in der Regel den größten Teil oder die gesamte Zieltabelle. Sie sollten sicherstellen, dass dies unproblematisch ist, oder eine Partitionierung verwenden. Beim Laden in eine Betriebsdatenbank können Sie möglicherweise eine Partition für das Laden einrichten, sodass aktuelle Betriebsdaten aktiv bleiben. Wenn das Laden nur langsam stattfindet, sollten Sie überprüfen, welche andere Aktivität für diese Tabelle oder Partition ausgeführt wird, und anschließend sicherstellen, dass keine weiteren Konflikte vorhanden sind. Ausgezeichnete Hinweise zum Steigern der Leistung beim Massenladen finden Sie auf MSDN im SQLCAT-Artikel The Data Loading Performance Guide (in englischer Sprache). Desweiteren sollten Sie beim Neuladen TRUNCATE und nicht DELETE verwenden, um Daten zu löschen, damit der Löschvorgang nicht transaktional ist. Die bereits beschriebenen Netzwerkprobleme treten bei Zielen, die eine Netzwerkverbindung verwenden, ebenfalls auf. Versuchen Sie die Paketgröße zu erhöhen und aktivieren Sie Großrahmen für Ihr Netzwerk, um beim Laden eine optimale Leistung zu erzielen.
15:02	Damit sind wir am Ende des zweiten Teils dieser Reihe zur Leistung. Vielen Dank an Thomas für die hilfreichen Informationen, die als Grundlage dieser Videoreihe dienen, und an Clara und Douglas für ihre Hilfe beim Erstellen der Reihe. Weitere Informationen zu diesen Themen finden Sie in den Top 10 SQL Server Integration Services Best Practices (in englischer Sprache). Vielen Dank, dass Sie sich dieses Video angesehen haben. Sehen Sie sich auch die anderen drei Teile der Videoreihe SSIS: Designing and Tuning for Performance an. Wir hoffen, dass diese Informationen wertvoll für Sie waren, und freuen uns auf Ihr Feedback. Auf der Videostartseite finden Sie in der oberen rechten Ecke einen Link für Bewertung und Feedback.

Freigeben über

Optimieren des SSIS-Paketdatenflusses im Unternehmen (SQL Server-Video)

Videozusammenfassung

Danksagungen zum Video

Videotranskript

Siehe auch

Andere Ressourcen

Hilfe und Informationen

Zusätzliche Ressourcen