Archiv für die Kategorie SQL Server

Partitionierung des MS SQL Servers für das Data Warehouse – Teil 2

Erstellt am: Freitag, 16. Dezember 2016 von Monika Düsterhöft

Nach einem ersten Überblick und Erläuterungen zum Partition Switching im ersten Teil des Blog-Beitrags, geht es nun darum, was benötigt wird, um eine Partitionierung vorzunehmen. Unter dem Gesichtspunkt, die von Natur aus komplexen DWH-Verarbeitungsroutinen nicht noch unnötig zu verkomplizieren, bietet sich hierfür ein praktikables, schlankes und modular gehaltenes Umsetzungskonzept an, das aus nachfolgend aufgeführten Komponenten besteht.

Administrativen Aufwand bei der Partitionierung kleinhalten

Von zentraler Bedeutung für den Betrieb ist, 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, dass sämtliche partitionierte Tabellen dasselbe Partitionsschema und dieselbe Partitionsfunktion nutzen (oder falls doch nötig: möglichst wenig davon). Das Ganze sollte sogar soweit gehen, dass die OLAP Measure Groups auf die gleiche Weise partitioniert sind, wie die relationalen DWH-Tabellen.

Bausteine eines Partitionierungskonzepts

Folgende modulare Bausteine sollte Ihr Partitionierungskonzept etwa beinhalten:

  • 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 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

Bleibt noch auszuführen, welche Vorteile die multidimensionale Partitionierung u.a. bietet: Sie erlaubt es, 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.

Weitere Informationen: Im Rahmen der QUNIS Beratung für Data Warehousing  geben wir unseren Kunden auch Tipps zur Performance-Optimierung ihres Microsoft SQL Servers. Dabei spielt die richtige Partitionierung eine wichtige Rolle.

Partitionierung des MS SQL Servers für das Data Warehouse – Teil 1

Erstellt am: Freitag, 9. Dezember 2016 von Monika Düsterhöft

Im Rahmen der QUNIS Beratung für Data Warehousing  geben wir unseren Kunden auch Tipps zur Performance-Optimierung ihres Microsoft SQL Servers. 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 allerdings ausschließlich in der Enterprise Edition (gilt für relationale Partitionierung) verfügbar ist.

Was ist also nötig für einen Einsatz der Partitionierung auf relationalen Fakten und OLAP-Measure Groups? Lesen Sie weiter im zweiten Teil des Beitrags!