Optimierung von Abfragen in MS SQL Server DWH

WEITER BLICKEN. MEHR ERKENNEN. BESSER ENTSCHEIDEN.
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen
SOLISYON GMBH
CHRISTIAN WOLF, BENJAMIN WEISSMAN
VERSION 1.0
OPTIMIERUNG VON ABFRAGEN IN MS SQL
SERVER DWH-UMGEBUNGEN
1
AUSGANGSSITUATION ....................................................................................................................2
2
LÖSUNGSANSATZ ..........................................................................................................................3
3
BEISPIEL ......................................................................................................................................4
3.1
URSPRUNGSABFRAGE – OHNE CLR ..........................................................................................4
3.2
ABFRAGE MIT CLR ................................................................................................................4
4
WEITERGEHENDE OPTIMIERUNG .....................................................................................................6
AUTOREN ....................................................................................................................................7
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
5
1|
1
Ausgangssituation
In Datawarehouses stellt sich regelmäßig die Herausforderung, dass die Schlüsselelemente von
Stammdaten aufgefüllt oder manipuliert werden müssen. Dies erfolgt, um beispielsweise leere Schlüssel zu vermeiden oder auch Schlüssel, welche sich aus mehreren Feldern zusammensetzen zu verbinden.
Hierzu werden häufig sogenannte Benutzerdefinierte Funktionen (UDF) genutzt.
Diese erlauben auch nachträglich einen flexiblen Eingriff in die Strukturen. Ist beispielsweise anfangs
ein Aufbau von mandantenabhängigen Artikelnummern in Form von Mandant + _ + Artikel gewünscht
welcher zu einem späteren Zeitpunkt „gedreht“, also in Artikel + _ + Mandant geändert werden soll,
so erlaubt eine solche UDF dies durch eine einzige zentrale Änderung.
In den evidanza-Standardwarehouses wird dies beispielsweise über die Funktion dbo.SetDummy() gelöst.
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
Die Nutzung von benutzerdefinierten Funktionen hat jedoch einen entscheidenden Nachteil: Abfragen
können nicht mehr parallel verarbeitet werden, insbesondere bei großen Abfragen wirkt sich dies massiv negativ auf die Performance aus.
2|
2
Lösungsansatz
Der Lösungsansatz ist relativ einfach: Die nicht parallel lauffähigen Funktionen können in sogenannte
Common Language Runtime (CLR) Objekte ausgelagert werden. Hierbei wird der T-SQL Code in Programmcode überführt, welcher im Anschluss wiederum dem SQL Server verfügbar gemacht wird. Die
Nutzung der CLR-Objekte macht die Ausführung, sofern nicht weitere Code-Fragmente dem entgegenstehen, parallel lauffähig und erhöht somit merklich die Performance. Durch den transparenten Austausch der bisherigen benutzerdefinierten Funktionen ist auch diese Anpassung an nur einer zentralen
Stelle erforderlich und somit mit minimalem Aufwand zu bewerkstelligen.
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
Eine Alternative zum Einsatz von CLR Objekten wäre der entsprechende Umbau der Abfragen, so dass
diese keine benutzerdefinierten Funktionen mehr nutzen – dies geht jedoch sehr zu Lasten der Flexibilität und bedeutet einen massiv erhöhten Test- und Entwicklungsaufwand, weswegen wir diesen Ansatz hier nicht weiter verfolgen.
3|
3
Beispiel
Unsere Demo-Umgebung ist eine virtuelle Maschine mit Windows Server 2012, SQL Server 2012 BI
Edition, 4 virtuellen CPU-Kernen sowie 32 GB Arbeitsspeicher. Als Beispielabfrage dient die View
VFAKT_Accounting aus dem evidanza-AX 2012-Standardwarehouse, welche effektiv eine Summenund Saldenliste, angereichert um Buchungsdimensionen etc. bereitstellt.
Die Faktentabelle hat in unserem Beispiel etwa 3,4 Millionen Datensätze.
3.1 Ursprungsabfrage – ohne CLR
Da lediglich die Funktionen selbst im Hintergrund ausgetauscht werden spielt die exakte Abfrage für
dieses Fallbeispiel keine Rolle – sie ist für beide Fälle, also mit und ohne CLR, absolut identisch.
Wie dem Execution-Plan zu entnehmen ist, erfolgt diese Abfrage seriell. Folglich wird auch nur ein CPU
Kern genutzt.
Abbildung 1: Execution-Plan ohne CLR
3.2 Abfrage mit CLR
Einzig durch die Umstellung auf CLR basierte Funktionen wird die Abfrage parallel ausgeführt, das System nutzt die bestehenden Ressourcen und reduziert hierdurch die Laufzeit:
Abbildung 2: Execution-Plan mit CLR
Die Gesamtlaufzeit beträgt nun nur noch etwa 2 Minuten, wurde also um etwa 70% reduziert. Engpass
ist hier nun nur noch die Anzahl der CPUs – durch weitere Kerne könnte man somit die Abfragedauer
weiter reduzieren. Somit lässt sich das System nun klar skalieren.
Auf anderen Systemen mit anderen Daten und/oder Hardwareparametern konnten wir ähnliche Performancesteigerungen beobachten.
Der direkte Vergleich der Abfragen macht nochmals deutlich, dass sich der Vorteil immer stärker darstellt, je größer die Datenmengen werden:
4|
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
Die Gesamtlaufzeit beträgt hier ca. 7 Minuten.
Ausführungszeiten
45,0
38,13
40,0
Dauer in Minuten
35,0
30,0
25,0
20,0
15,0
12,12
10,0
5,0
6,60
2,4
2,13
1,4
0,0
2.500.000
3.400.000
9.000.000
Anzahl Datensätze (unterschiedliche Abfragen)
ohne CLR
mit CLR
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
Abbildung 3: Vergleich der Ausführungszeiten
5|
4
Weitergehende Optimierung
Je nach Systemumgebung kann es nun jedoch dennoch vorkommen, dass auch beim Einsatz von CLR
Komponenten die Performance Ihrer Abfragen weiterhin nicht dem erwarteten Ergebnis entspricht.
Prüfen Sie zunächst Ihre Indexe und Statistiken – ohne entsprechend Indizierung ist jede weitere Performance-Analyse nahezu sinnlos. Achten Sie in den entsprechenden Ausführungsplänen, insbesondere bei großen Tabellen, vor allem auf Table Scans.
Eventuell stehen aber auch sonstige Parameter, wie zum Beispiel MAXDOP, einer parallelen Abfrage
entgegen.
Wir empfehlen Ihnen somit dringend eine entsprechende Analyse Ihrer Ausführungspläne um herauszufinden, ob die erwartete Parallelisierung auch wirklich stattfindet. Sollte dies, trotz Verzicht auf benutzerdefinierte Funktionen etc., nicht erfolgen, so helfen häufig die folgenden Optionen:
4.1 Überprüfung der Servereinstellungen
Abbildung 4: SQL-Server-Einstellungen
Die Standardeinstellung des Kostenschwellenwerts für Parallelität von 5 ist eine gute Ausgangsbasis.
Durch eine Verringerung des Wertes, werden die Kosten von parallelen Abfragen gesenkt und der
Query-Optimizer erzeugt eher parallele Ausführungspläne.
Der Wert von 0 des Max. Grads an Parallelität bedeutet, dass es keine Einschränkungen der Anzahl
der genutzten CPUs gibt. Ein Wert größer als 0 limitiert die zu verwendenden CPU in einer parallelen
Abfrage auf die angegebene CPU-Zahl.
6|
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
In den SQL-Server-Einstellungen sollten die Werte der in Abbildung 4 markierten Optionen überprüft
werden.
4.2 Parallelen Ausführungsplan erzwingen
Sollte der SQL-Query-Optimizer auch bei angepassten Servereinstellungen und bei optimalen Indizes
keinen parallelen Ausführungsplan erzeugen, kann in bestimmten Situationen nachfolgende QueryOption helfen:
OPTION (RECOMPILE, QUERYTRACEON 8649)
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
Diese Abfrage-Option bewirkt, dass die Abfrage auf jeden Fall parallel ausgeführt wird. In einigen Einzelfällen kann die Ausführungszeit hierdurch deutlich verkürzt werden, obwohl der Query-Optimizer
den seriellen Execution-Plan besser bewertet.
7|
5
Autoren
Haben Sie noch Fragen? Haben Sie Interesse am konkreten Einsatz der von uns entwickelten CLR Funktionen bei Ihnen? Benötigen Sie Unterstützung bei einer ähnlichen Herausforderung?
Besuchen Sie uns unter www.solisyon.de oder wenden Sie sich gerne direkt an einen der Autoren!
Christian Wolf
Senior Consultant
Optimierung von Abfragen in MS SQL Server DWH-Umgebungen | 31.03.2015
[email protected]
+49 911 99 00 77 24
Benjamin Weissman
Geschäftsführer
[email protected]
+49 911 99 00 77 21
8|