Mehr Performance im Data Warehousing durch kluge Partitionierung des SQL Server

Im Rahmen unserer Projekte für Data Warehousing geben wir unseren Kunden (Mittelstand und Konzernen) auch Tipps zur Performance-Optimierung ihres Microsoft SQL Servers, auf dem die Lösungen häufig basieren. Dabei spielt die richtige Partitionierung eine wichtige Rolle.

Grundsätzlich ist eine Partitionierung sowohl relational wie multidimensional möglich. Während Letzteres häufiger Einsatz findet, ist gerade die relationale Partitionierung bei zeitintensiven Data-Warehouse-Verarbeitungsroutinen ein effektvolles Mittel zur Laufzeitverbesserung. Die relationale Partitionierung findet auf Tabellen Anwendung und bedeutet faktisch, dass die Tabelle physisch in einzelne Fragmente aufgeteilt wird. Es handelt sich aber nach wie vor logisch und abfragetechnisch um eine (1!) Tabelle.

Die einzelnen Fragmente sind die Partitionen. Diese können auf unterschiedlichen Dateigruppen liegen, was durch das Partitionsschema definiert ist. Wichtiger für das Verständnis ist jedoch, dass jeder Datensatz eindeutig zu einer bestimmten Partition zugeordnet werden kann. Das ist über die Partitionsfunktion einzustellen.

Dazu ist ein Feld der Tabelle festzulegen, dessen Inhalt dieses für jeden Datensatz bestimmt, beispielsweise ein Datum. So ließe sich in der Partitionsfunktion für Data-Warehouse-Fakten festlegen, dass historische Daten in Jahrespartitionen und aktuelle Daten in Monatspartitionen abgelegt werden.

Partition Swichting zur Performance-Steigerung

Um auf dieser Basis tatsächlich signifikante Performance-Verbesserungen in der Data-Warehouse-Verarbeitung erzielen zu können, ist ein weiterer Aspekt zu beachten. Die alleinige Partitionierung hat keinerlei Effekt, wenn SQL-Server die anzusprechenden Partitionen nicht auf Basis des entsprechenden SQL-Statements ableiten kann. Daher ist bei der Erstellung der SQL-Anweisungen besondere Acht geboten.

Um diese Gefahr auszuschalten, sollte alternativ zum Mittel des Partition Switching gegriffen werden. Hierbei werden einzelne Partitionen aus der Data-Warehouse-Tabelle in eine Arbeitstabelle für die Verarbeitung (löschen, einfügen, aktualisieren) herausgelöst (Switch out).

Bedenken Sie hierbei, dass keinerlei Daten physisch bewegt werden, sondern dass die einzelnen Datenseiten in den Metadaten als zugehörig zur Arbeitstabelle statt zur Data-Warerhouse-Tabelle deklariert werden. Dadurch ist ein solches Partition Switching auch eine Angelegenheit eines Bruchteils einer Sekunde, egal wie viele Datensätze betroffen sind. Nach der Verarbeitung erfolgt das Partition Switching in umgekehrter Richtung (Switch in).

Wann lohnt sich Partition Switching?

Ab wann lohnt sich der Aufwand der Partitionierung verbunden mit Partitionsschema, Partitionsfunktion, zusätzlicher Arbeitstabelle und Partition Switching? Zunächst muss ein gewisses Datenvolumen vorhanden sein, ab dem es sich überhaupt lohnt, über den Einsatz nachzudenken. Die Angaben hierzu schwanken zwischen 20 und 60 Millionen Datensätze pro Tabelle. In der Praxis beschränkt sich das folglich auf Faktendaten.

Sollten Sie also eine oder mehrere Faktentabellen in dieser Größenordnung und gleichzeitig Laufzeitprobleme in der Verarbeitung haben, lohnt sich der Einsatz der Partitionierung. Die ist allerdings ausschließlich in der Enterprise Edition (gilt für relationale Partitionierung) verfügbar.

Administrativen Aufwand bei der Partitionierung kleinhalten

Was benötigen wir nun, um eine Partitionierung vorzunehmen? Um die von Natur aus komplexen DWH-Verarbeitungsroutinen nicht noch unnötig zu verkomplizieren, sollten wir ein praktikables, schlankes und modular gehaltenes Umsetzungskonzept wählen. Von zentraler Bedeutung für den Betrieb ist dabei, dass keine regelmäßigen administrativen Eingriffe nötig sind, etwa um neue Partitionen anzulegen, sondern, dass automatisch ausgeführte Routinen dieses übernehmen.

Basis für dieses Konzept ist es daher, dass sämtliche partitionierte Tabellen dasselbe Partitionsschema und dieselbe Partitionsfunktion nutzen (oder falls doch nötig: möglichst wenig davon). Das Ganze sollte sogar so weit gehen, dass die OLAP Measure Groups auf die gleiche Weise partitioniert sind, wie die relationalen DWH-Tabellen. Nachfolgend seien die einzelnen Bausteine eines Partitionierungskonzepts kurz genannt:

• Eine Importsteuerungstabelle, die für sämtliche Faktendaten, die auf Basis eines Importdatums inkrementell importiert werden, Steuerungsmöglichkeiten für entsprechende Anwender erlaubt, wie einmaliger Import von Datum x, danach wieder y Tage rollierend oder dauerhaft ab Datum z usw.;
• Eine Handvoll Sichten, die Ihnen Infos zu Dimensionen / Measure Groups (beides basierend auf sog. Dynamic Management Views (DMV) auf Grundlage eines eingerichteten Verbindungsservers zu Analysis Services) und Partitionen / Partitionsgrenzen liefern – diese sind für den allgemeinen Überblick sinnvoll und finden in den nachfolgend aufgeführten Routinen Verwendung;
• Eine zentrale interne Routine (gespeicherte Prozedur), die auf Basis übergebener Parameter das Partition Switching für die relevanten Partitionen einer Faktentabelle vornimmt (in/out), sowie bei inkrementellen Importprozessen, die auf einem Importdatum basieren, die entsprechenden Fakten, die neu eingelesen werden, löscht (auf Basis der obigen Importsteuerungstabelle);
• Eine manuell aufzurufende Routine, die für neu erstellte Measure Groups basierend auf partitionierten Faktentabellen die Partitionierung – gültig nach aktuellem Stand – einrichtet (auf Basis von XMLA-Code via Verbindungsserver);
• Eine Routine für die nächtliche Datenverarbeitung, die sämtliche Dimensionen parallel verarbeitet (process update) und anschließend alle nicht partitionierten Measure Groups sowie alle relevanten Partitionen partitionierter Measure Groups parallel verarbeitet (auf Basis von XMLA-Code via Verbindungsserver sowie der Importsteuerungstabelle);
• Eine Routine, die auch mehrfach am Monatsanfang ablaufen kann und wiederholt nachsieht, ob es in der obersten (nach oben hin nicht begrenzten Partition) bereits Daten gibt. In diesem Fall werden alle nötigen Maßnahmen durchgeführt, die relationalen wie auch die multidimensionalen Partitionen anzulegen und zu verarbeiten.

Ein Tipp noch für die Praxis: Achten Sie penibel auf die Benennung der Dimensionen, Cubes und Measure Groups! Denn DMVs liefern grundsätzlich den Namen dieser Objekte, während über XMLA die internen IDs dieser Objekte angesprochen werden müssen. Umbenennungen der IDs sind hier aufwändig.

Vorteile der multidimensionalen Partitionierung

Zusammenfassend bietet also die multidimensionale Partitionierung einige Vorteile und erlaubt, den Caching-Mechanismus zu optimieren. Während eine unpartitionierte Measure Groups mit der nächtlichen Verarbeitung aus dem Arbeitsspeicher entfernt wird, bleiben bei partitionierten Measure Groups die nicht-verarbeiteten Partitionen im Arbeitsspeicher vorhanden. Weiterhin beschränkt sich die Verarbeitung auf relevante Partitionen, während historische Partitionen nicht immer wieder neu verarbeitet werden müssen.

Über den Autor

Carsten Cohrs ist Senior Consultant, bei der QUNIS GmbH. Das Unternehmen hat sich auch praxisorientierte Beratung und die nachhaltigen Betreuung von Projekten in den Bereichen Business Intelligence und Data Warehousing, Planung und Performance Management sowie Advanced Analytics und Big Data ausgerichtet. Dieser Beitrag bei silicon.de erschien zuerst im QUNIS-Blog, wo die Mitarbeiter regelmäßig über ihre Erfahrungen aus der Praxis berichten.