Schlagwort-Archive: Performance

Performanceprobleme bei SSAS-Dimensionsabfragen aus SSRS

Nachdem wir in einem Projekt unseren Test-Datenbestand (12 Mio Fakten) auf den Produktiv-Bestand (73 Mio Fakten) erweitert hatten, zeigten die Berichte (SSRS 2008 R2) massiv schlechte Antwortszeiten in bestimmten Berichten auf unseren Cube (SSAS 2008 R2) – jeweils nach der Cubeaufbereitung. Somit war klar, dass Ursache war, dass einige der im Bericht verwendeten Abfragen nicht im Cache waren, da dieser ja durch die inkrementelle Dimensionsaufbereitung und Aufbereitung einiger Cubepartitionen gelöscht wird.

Naheliegende Strategien waren:

  • Aufteilung in mehr Partitionen unter Angabe der Slice-Property
  • Verbesserte Aggregationen über Usage based Aggregation Design
  • Cache Warming – Strategien

All das brachte uns aber nicht wirklich weiter.

Letztendlich stellte sich aber heraus, dass die Ursache gar nicht in den (tlw. komplexen) Abfragen zur Ermittlung der Fakten zu suchen war, sondern in „Dimensions-Abfragen“. Damit meine ich folgendes: Unsere Berichte werden (aus einer eigenen Web-Applikation heraus) mit Parametern aufgerufen, die IDs sind (eigentlich die Member Unique Names, also z.B. Datum.Jahr.&[2010]). Dabei haben die Parameter (aus Performancegründen) keine Datasets, die alle verfügbaren Werte enthalten. Nun wollen wir aber in dem Bericht natürlich auch den übergebenen Wert im Klartext anzeigen, also in obigem Beispiel das Jahr 2010. Deswegen mussten also einfache Abfragen her, die aus dem Member Unique Name den Klartext (Member Caption) ermitteln.

In unserem Projekt hatten wir uns darauf geeinigt, wenn möglich den SSRS-Designer für SSAS-Abfragen zu verwenden, um eine gute Wartbarkeit (ohne tiefere MDX-Kenntnisse) zu erreichen.

Damit gibt es zwei einfache Arten, dieses Problem zu lösen:

Die erste Art, hat eine Spalte – das Jahr – und zusätzlich den (MultiSelect) Report Parameter als Parameter für die Abfrage:

Wizard für die langsame Abfrage
(Bild zum Vergößern anklicken!)

Als MDX ergibt sich:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Man kann auch die Abfrage etwas erweitern:

Man fügt ein berechnets Measure (in diesem Fall namens „UniqueName“) mit dem MDX

[Datum].[Jahr].CurrentMember.UniqueName

hinzu:

Wizard der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Damit ergibt sich folgendes MDX:

WITH MEMBER [Measures].[UniqueName] AS [Datum].[Jahr].CurrentMember.UniqueName SELECT NON EMPTY { [Measures].[UniqueName] } ON COLUMNS, NON EMPTY { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Anmerkung: Das dabei enstehende versteckte Dataset zum Befüllen der „verfügbaren Werte“ des Parameters löschen wir, da wir ja keine „verfügbaren Werte“ anzeigen wollen (unsere Dimension hatte zu viele Einträge).

Beide Abfragen scheinen dasselbe zu tun und mit der selben Performance. Dies ist aber ein Trugschluss!

Leeren wir zunächst (und vor Ausführung eines neuen Test-Statements) den SSAS-Cache mit

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine“>
<Object>
<DatabaseID>SimpleCube</DatabaseID>
<CubeID>Verkaeufe</CubeID>
</Object>
</ClearCache>

(siehe dazu auch: http://www.ssas-info.com/analysis-services-faq/27-mdx/133-mdx-how-do-i-clear-analysis-services-ssas-database-cache)

Dann betrachten wir die Darstellung im SQL Server Profiler (mit den Standard-Einstellungen für den SSAS):

Die erste Abfrage ergibt folgendes Bild:

SQL Profiler Ergebnis der langsamen Abfrage
(Bild zum Vergößern anklicken!)

Schauen wir uns die einzelnen Zeilen an:

  • Session Initialize – spricht für sich
  • Query Begin: Hier fängt die Abfrage an – im unteren Bereich sieht man das MDX und die Parameterwerte für die @Parameter im MDX
  • Progress Report Begin: Daten aus einer Partition werden gelesen (!), d.h. von der Festplatte in den Speicher übernommen.
  • Progress Report End: das dazugehörige Ende
  • Query SubCube: die gelesenen Daten werden verwendet, um die Abfrage zu beantworten (in diesem Fall Non-Cache)
  • Query End: Das Ende der Abfrage

Die zweite Abfrage ergibt ein anderes Bild:

SQL Profiler Ergebnis der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Hier sehen wir, dass kein Zugriff auf eine Partition erfolgt!

Der Zugriff auf die Partition ist natürlich schädlich, da er bei großen Datenmengen lang dauern kann, zumal er gar nicht benötigt wird, da wir ja nur Dimensions-Element-Bezeichnungen abfragen wollen.
Es ist nicht erklärbar, warum SSAS hier dennoch auf die Fakten zugreift.
Deswegen ist die zweite Abfrage auf jeden Fall vorzuziehen!

Noch ein interessante Anmerkung zum Schluss: Wenn man die erste Abfrage im SQL Server Management Studio ausführt, ist sie auch nicht langsam. Da im Management Studio keine parametrisierten Abfragen möglich sind, muss man dazu die Parameter durch die entsprechenden Strings ersetzen, und erhält somit folgendes MDX:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(„{ [Datum].[Jahr].&[2001],[Datum].[Jahr].&[2008] }“, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Führt man dieses MDX aus (nachdem man den Cache geleert hat), so erhält man folgendes Bild:

Ergebnis des Profilers der langsamen Abfrage ausgeführt im Management Studio
(Bild zum Vergößern anklicken!)

Man sieht hier also auch nur ein Query Begin und ein Query End, also auch keinen Zugriff auf die Cube-(bzw. Partitions-)Daten.

Dies hatte uns die Fehlersuche erschwert, da wir natürlich zunächst auf der Suche nach dem verantwortlichen Statement alle Statements im SQL Server Profiler mitgeschnitten und dann einzeln im Management Studio ausgeführt hatten, was – wie eben gesehen – das problematische Statement leider nicht offenbart.

Performance und GUIDs

In einem Kunden-Projekt sollte ich die Performance der SQL-Zugriffe einer .NET-Applikation verbessern.

Hier beschreibe ich die Ergebnisse, da sie sich auch auf andere Szenarien verallgemeinern lassen.

Die erste Veränderung war, keine GUIDs als clustered primary keys zu verwenden. Stattdessen setzen wir nun ints (mit Identity) ein. Dies führt zu einer immensen Beschleunigung bei den INSERTs in die Datenbank. Dies ist sehr gut nachvollziehbar, da nun die INSERTs immer am Ende der Tabelle statttfinden, so dass kein zeitaufwändiges Umorganisieren der Seiten innerhalb der Tabelle notwendig wird.

Leider ging es aber in meiner Aufgabe um die SELECT-Performance und nicht um die INSERT-Performance. Aber auch diese verbesserte sich durch die Verwendung der integer-Werte deutlich (Abfragezeit ungefähr halbiert). Als Test verwendete ich die Abfrage von 500 Datensätzen, die ich zu Beginn zufällig ausgewählt hatte. Vor jeder Abfrage wurde natürlich der Cache geleert 🙂 Die Steigerung lässt sich dadurch erklären, dass alle Indizes nun vom Platzbedarf viel kleiner wurden (1 int = 4 byte, 1 guid = 16 byte –> ca. 4x so viele Daten gehen auf eine Index-Page [natürlich abhängig von den weiteren Feldern des Index]). Außerdem ist ein Zähler besser verteilt als eine Guid.

Als nächste Verbesserung verwendete ich in m:n-Tabellen als clustered primary key nicht einen Zähler, sondern einen zusammengesetzten Schlüssel aus den beiden referenzierten Tabellen (+ ein weiteres Feld, um die Eindeutigkeit sicherzustellen). Dabei verwendete ich als erstes Feld das Feld der beiden, das in den meisten Abfragen bekannt ist. (Auf der umgekehrten Reihenfolge lag natürlich auch ein Index). Dadurch muss beim Standard-Zugriff nicht mehr über einen non-clustered Index zugegriffen werden, wodurch ein Zugriff eingespart wird. Dies brachte eine weitere Halbierung der Zugriffszeit.

ALs letztes gab es spezielle Szenarien, in denen nach Texten gesucht werden musste – ein Beispiel: Man möchte alle Aufträge ermitteln, die in einer Position einen bestimmten Positionsfreitext enthalten. Dann wird folgendes SQL-Statement abgesetzt:

SELECT * FROM Auftrag a INNER JOIN Auftragsposition pos on pos.AuftragID = a.AuftragID WHERE pos.Positionsfreitext like ‚Test%‘

Natürlich war auf der Auftragspositions-Tabelle ein Index auf Positionsfreitext. Ich erweiterte diesen Index um die AuftragID. Dadurch kann der Join direkt über den Index abgewickelt werden und ein Zugriff auf die Tabelle wird eingespart, was für diesen Spezialfall ebenfalls eine deutliche Performance-Steigerung einbrachte.