- silicon.de - http://www.silicon.de -

Mehr Performance im Data Warehousing durch kluge Partitionierung des SQL Server

Im Rahmen unserer Projekte für Data Warehousing [1] 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.

Carsten Cohrs, der Autor dieses Gastbeitrags, ist  Senior Consultant, bei der QUNIS GmbH (Bild: QUNIS) [2]
Carsten Cohrs, der Autor dieses Gastbeitrags, ist
Senior Consultant, bei der QUNIS GmbH (Bild: QUNIS)

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.

Microsoft SQL Server (Bild: Microsoft) [3]

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.

Ausgewähltes Whitepaper

Nutzen und Vorteile der Integration von ECM- und ERP-Software

Ein ECM-System kann besonders dort eine wichtige Ergänzung zu einer bereits bestehenden ERP-Lösung darstellen, wo geschäftsrelevante Dokumente separat abgelegt und mit ERP-Datensätzen verknüpft werden sollen, um Geschäftsprozesse vollständig digital abbilden zu können. Dieses Whitepaper beschreibt die Vorteile an einem konkreten Beispiel.

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:

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, der Autor dieses Gastbeitrags, ist  Senior Consultant, bei der QUNIS GmbH (Bild: QUNIS) [2]

Carsten Cohrs ist Senior Consultant, bei der QUNIS GmbH [4]. 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 [5], wo die Mitarbeiter regelmäßig über ihre Erfahrungen aus der Praxis berichten.