SQL Server Analysis Services (kurz: SSAS) ist eine multidimensionale Datenbank, die hervorragende Möglichkeiten bietet, Business-Intelligence-Systeme bis auf Enterprise-Ebene zu entwickeln. SSAS vereint Einfachheit in der Entwicklung mit außergewöhnlicher Flexibilität und Performance. Kleine und triviale Systeme werden in den meisten Fällen sehr schnell funktionieren, bei größeren und komplexeren Systemen kann es zu Performance-Problemen kommen. Wie kann man nun solche Probleme und deren Ursachen identifizieren? Glücklicherweise bietet Microsoft einige Performanceanalyse-Tools; das wichtigste Tool ist SQL Server Profiler (kurz: Profiler).
Mit diesem Blogbeitrag möchten wir zeigen, wie man mit den in der Praxis typischen SSAS-Performanceproblemen für MOLAP (multidimensionales OLAP) umgeht. MOLAP ist der Standard-Speichermodus für Measuregroups in BI-Reportingsystemen ohne Planungsfunktionalität. MOLAP bietet normalerweise eine sehr gute Performance, verglichen mit ROLAP (relationales OLAP) ist es die klar bevorzugte Option für Measuregroups, in denen keine Rückschreibe-Option vorhanden bzw. notwendig ist.
Die Besonderheiten der Performanceanalyse mit SQL Server Profiler für die MOLAP- und ROLAP-Architekturen, die typisch für Planungssysteme sind, werden das Thema von einem späteren Blogbeitrag.
Szenario
Als Beispiel dient unsere Chair-Demodatenbank. Uns interessieren die folgenden Fragen:
- Wie arbeitet man am effizientesten mit dem Profiler? Tipps, Best Practices.
- Was passiert in Analysis Services, wenn ein DeltaMaster-Report aktualisiert wird? Welche Abfragen werden ausgeführt? Welche Ergebnisse (Antwortzeiten) werden geliefert?
Vorbereitung
Starten wir DeltaMaster und erstellen eine neue Analysesitzung für den Chair-Cube mit den Standard-Berichten für Kunden, Produkte und Branchen. Um die Anzahl der Abfragen zu minimieren, die DeltaMaster generiert, erstellen wir ein neues Cockpit „Monatssicht“ mit lediglich sechs Kennzahlen auf den Zeilen.
Abbildung 1: Monatssicht
Jetzt möchten wir analysieren, was in Analysis Services passiert, wenn wir diesen Bericht aktualisieren. Dafür starten wir SQL Server Profiler und verbinden uns mit dem SSAS-Server. Der Profiler kann entweder aus SQL Server Management Studio (SSMS Menü „Extras“) oder aus dem Windows-Startmenüordner für SQL Server aufgerufen werden.
Nachdem wir uns mit dem Server verbunden haben, erscheint das Dialogfenster für die Ablaufverfolgungseigenschaften, in dem wir die folgenden Informationen eingeben können:
- Ablaufverfolgungsname: Oft wird hier der Standardname eingegeben, es ist jedoch ratsam, der Ablaufverfolgung einen eindeutigeren Namen (z. B. „Chair MDX-Abfrageüberblick“) zu geben, denn dadurch wird es später einfacher zwischen mehreren Ablaufverfolgungen über die Fenster im Profiler zu navigieren.
- Vorlage: Über die Verwendung der Vorlagen im Profiler werden wir später noch ein paar Details erörtern. Jetzt wählen wir die Standard-Vorlage.
Abbildung 2: Ablaufverfolgungseigenschaften – Allgemein
Zunächst werden wir die Ablaufverfolgung nicht ausführen, sondern wählen die Ereignisse aus, die wir analysieren möchten. Dafür wechseln wir in das Ereignisauswahl-Register und stellen Folgendes ein:
- Checkboxen bei „Alle Ereignisse anzeigen“ und „Alle Spalten anzeigen“ markieren. Oft ist es sinnvoll, erst alle möglichen Ereignisse anzuschauen und zu überlegen, was in die Ablaufverfolgung am besten aufgenommen werden sollte. Gut ist auch, wenn zunächst alle möglichen Spalten ausgewählt werden.
- Entfernen wir die Haken für alle Ereignisse.
- Für unsere erste Ablaufverfolgung, die uns einen groben Überblick verschaffen soll, wählen wir nur die folgenden Ereignisse:
- Query Begin, Query End
- Audit Login, Audit Logout
Abbildung 3: Ablaufverfolgungseigenschaften – Ereignisauswahl
Da auf einem Server mehrere Datenbanken vorhanden sein können, die gleichzeitig abgefragt werden können und uns in unserem Szenario nur unsere eigenen Abfragen interessieren, ist es ratsam, die entsprechenden Ereignisfilter zu setzen.
Abbildung 4: Ablaufverfolgungseigenschaften – Ereignisfilter
Es ist möglich, mehrere Filterwerte einzugeben – die Umsetzung ist nicht trivial, deshalb möchte ich zeigen, wie es funktioniert. Man setzt den Cursor auf den letzten Filterwert, klickt noch Mal, um in den Editiermodus für den Wert zu wechseln, und dann drückt man die Eingabetaste und gibt den neuen Wert ein.
Um einen Filterwert zu entfernen, geht man in den Editiermodus für den gewünschten Filterwert, dann drückt man die Backspace-Taste und klickt in einen Bereich außerhalb des Editierfeldes.
Wir können für die Ereignisse noch die Spaltenreihenfolge anpassen – dafür wählt man den Button „Spalten organisieren“.
Hinweis: Die Ablaufverfolgungseigenschaften können nur für eine angehaltene Ablaufverfolgung geändert werden.
Wir haben jetzt alle Vorbereitungen getroffen und es kann endlich losgehen – wir können die Ablaufverfolgung starten.
Ablaufverfolgungsanalyse
Aktualisieren wir den Monatssicht-Bericht in DeltaMaster mit F9 und schauen die Ablaufverfolgung an. Bevor wir die Abfragenanalyse durchführen, ist es sinnvoll, die Ablaufverfolgung anzuhalten.
Abbildung 5: Ablaufverfolgung – Ereignisse
Wir können in der Ablaufverfolgung sehen, dass selbst unser einfacher Report 28 (!) MDX-Abfragen generiert. Für komplexe Berichte, besonders in denen ROLAP-Partitionen abgefragt werden, kann es zu langen Berichtausführungszeiten kommen. Grund dafür ist, dass eine MDX-Abfrage für eine ROLAP-Partition die Daten nicht aus Analysis Services, sondern aus SQL Server holt. Jede einzelne SQL-Abfrage für große Tabellen kann mehrere Sekunden dauern, da typische OLAP-Abfragen GROUP BY- und WHERE-Klauseln für mehrere Felder beinhalten.
Als erste Kandidaten für weitere Analysen werden die Abfragen genommen, deren Ausführung zu lange dauert. In unserem Beispiel gibt es solche Abfragen nicht.
Wie die erkannten Problemabfragen selbst mit dem Profiler genauer angesehen und analysiert werden können, zeigen wir in einem folgenden Blog-Artikel.
Speichern von Ablaufverfolgung
Um eine Ablaufverfolgung zu speichern, bestehen die folgenden Möglichkeiten:
- Datei -> Speichern unter -> Ablaufverfolgungsdatei. Die Ergebnisse werden in einer .trc-Datei (internes Format vom Profiler) gespeichert und können später mit dem Profiler erneut geöffnet werden.
- Datei -> Speichern unter -> Ablaufverfolgungstabelle. Die Ergebnisse werden in einer SQL Server-Tabelle gespeichert und können später mit dem Profiler geöffnet werden.
- Datei -> Speichern unter -> XML-Ablaufverfolgungsdatei.
- Datei -> Exportieren -> SQL Server Analysis Services-Ereignisse extrahieren -> MDX-Ereignisse extrahieren oder „Alle Abfragen extrahieren“.
Die Möglichkeit, die Abfragen aus einer Ablaufverfolgung zu extrahieren und sie als Datei zu speichern, ist gut, um sich einen Überblick über alle Abfragen zu verschaffen und später die Abfragen in Ruhe zu analysieren.
Vorlagen
Da wir für unsere Ablaufverfolgung viele Einstellungen angepasst haben, ist es sinnvoll unsere Einstellungen als Vorlage zu speichern, damit wir nächstes Mal, wenn wir den Profiler starten, diese Einstellungen als Vorlage verwenden können. Um die aktuellen Einstellungen als Vorlage zu speichern, muss die Ablaufverfolgung angehalten sein. Anschließend wählen wir den Menüpunkt Datei -> Speichern unter -> Ablaufverfolgungsvorlage. Hier geben wir den Namen „Chair MDX-Abfragenüberblick“ ein und speichern diese Vorlage.
Wenn wir jetzt eine neue Ablaufverfolgung erstellen, können wir unsere gespeicherte Vorlage nehmen. Dies spart viel Zeit und Mühe.
Abbildung 6: Ablaufverfolgungseigenschaften – eine Vorlage verwenden
Fazit und Ausblick
Das Thema Performanceanalyse ist sehr umfangreich und sehr wichtig für BI-Systeme. SQL Server Profiler ist nur eines der Tools, die für die Performanceanalyse hilfreich sein können. In diesem Artikel haben wir mit diesem interessanten Thema angefangen und uns die Basics von SQL Server Profiler angesehen. Weitere Themen wie die fortgeschrittene Abfragenanalyse und die Performanceanalyse für Planungssysteme, wo MOLAP mit ROLAP zusammen verwendet werden, schauen wir uns in einem weiteren Blogbeitrag an.
