2. Grundlagen

2.1 Optimierungsmodell

Dieses Kapitel basiert im Wesentlichen auf: [Kali05a], [Pant07], [Pant10], [Schm07] und [Schm09].

Für eine erfolgreiche Performance-Optimierung ist ein systematisches Vorgehen unabdingbar. Nach Schmeling hat sich in der Praxis das in Abbildung 1 gezeigte Modell zur PerformanceOptimierung als geeignet erwiesen, wobei die einzelnen Optimierungsphasen jeweils auf die Vorhergehenden aufbauen.

Abbildung 1: Optimierungsmodell (Quelle: Eigene Darstellung in Anlehnung an [Schm09])

Eine Performance-Optimierung erscheint oftmals erst im laufenden Produktivsystem erforderlich, da das Gesamtsystem plötzlich spürbar langsamer geworden ist. Dies ist jedoch der falsche Ansatz. Eine Optimierung sollte bereits auf dem speziell für eine Anwendung ausgelegten Datenbankdesign aufsetzen, um die Zugriffe auf die Datenbank zu minimieren. Der Fokus liegt dabei vor allem auf einer effizienten Datentypenwahl, ebenso wie dem sinnvollen Einsatz von Normalisierung und Denormalisierung in Bezug auf die anwendungsspezifische Nutzung.

Die zweite Phase, die Optimierung des eigentlichen SQL Codes, bietet in der Regel das größte Potential, um spürbaren Performancegewinn zu erzielen. Genaueste Kenntnisse über die Funktionsweise des SQL Servers, sowie die interne Verarbeitung eines SQL Befehls, sind dabei notwendig.

Im Anschluss an die SQL Code Optimierung empfiehlt sich eine Indexoptimierung, um die Datenbankstruktur durch entsprechende Indizes zu ergänzen und überflüssige zu eliminieren. Eine einmalige Erstellung reicht nicht aus, weshalb die vorhanden Indizes von Zeit zu Zeit reorganisiert oder komplett neu erstellt werden sollten.

Im vierten Optimierungsschritt gilt es, Blockierungen durch gesperrte Ressourcen aufzuspüren und zu minimieren. Gesperrte Ressourcen entstehen, indem mehrere Transaktionen versuchen gleichzeitig auf bestimmte Ressourcen zuzugreifen.

Für viele Systemadministratoren ist die Hardwareaufrüstung der erste Schritt um Performanceprobleme zu beheben. Dadurch werden jedoch meistens die eigentlichen Problemursachen nicht beseitigt, sondern nur eine kurzzeitige Performancesteigerung erreicht. Dementsprechend sollte eine Hardwareaufrüstung das letzte Mittel sein, um eine Performancesteigerung zu erreichen.

2.2 Interne Verarbeitung von SQL-Anweisungen

Dieses Kapitel basiert im Wesentlichen auf: [MaUn03], [Micr11], [Pant10], [Schm09], [Warn07] und[Wood07].

Die SQL Code Optimierung erfordert genaueste Kenntnisse des SQL Servers und der Verarbeitung von SQL-Anweisungen. Um die Funktionsweise des SQL Servers, insbesondere die Reihenfolge der Verarbeitung von SQL-Anweisungen zu verstehen, wird zunächst folgendes Beispiel betrachtet:

Beispiel 1: Einfache SQL Abfrage - Reihenfolge der Verarbeitung von SQL-Befehlen

Die obige Abfrage liefert eine Auflistung aller Unterkategorien, sowie die Anzahl der in ihr enthaltenen Produkte zurück. Dabei werden nur Kategorien beachtet, welche mindestens 75 Produkte und den String „kamera“ im Kategorienamen enthalten.

Der SQL Server verarbeitet die einzelnen Klauseln der Abfrage in einer exakt festgelegten, logischen Reihenfolge, welche Abbildung 2 verdeutlicht.

Abbildung 2: Reihenfolge der Verarbeitung von SQL-Befehlen

Über FROM werden zunächst alle benötigten Tabellen gelesen und anschließend über JOIN und ON miteinander verknüpft. Im nächsten Verarbeitungsschritt werden die Ergebnisse durch WHERE-Bedingungen gefiltert und somit in der Zeilenanzahl eingeschränkt. Das entstandene Zwischenergebnis wird dann über GROUP BY zeilenweise zusammengefasst. WITH CUBE bzw. WITH ROLLUP ergänzen die Zeilen mit Zwischensummen anhand der GROUP BY- Aggregationen. Die HAVING-Klausel filtert das Zwischenergebnis und reduziert folglich die Zeilenanzahl weiter. Erst im achten logischen Verarbeitungsschritt werden die benötigten Spalten über SELECT ausgewählt und das Zwischenergebnis in der Spaltenanzahl dezimiert. Eine anschließende Zeilenfilterung erfolgt dann über DISTINCT. Das Ergebnis kann nun noch mit Hilfe der ORDER BY-Klausel sortiert und abschließend über TOP weiter in der Zeilenanzahl eingeschränkt werden. Letztendlich wird nun das Gesamtergebnis zurückgegeben.

Jeder Verarbeitungsschritt erzeugt gewissermaßen eine temporäre Tabelle, welche an den nächsten Bearb ei tungs schritt weitergereicht wird. Diese temporäre Tabelle wird hierbei in jedem Schritt durch Hinzufügen und Löschen von Zeilen und Spalten verändert.

Die logische Schlussfolgerung daraus ist: Je früher die Quelldaten gefiltert werden, desto weniger Daten müssen an den nächsten Bearbeitungsschritt weitergegeben und verarbeitet werden.

Folgende Beispiele sollen den Performanceunterschied zwischen einer Abfrage über die HAVING-Klausel (Beispiel 2) und über die WHERE-Klausel (Beispiel 3) verdeutlichen. Beide Abfragen geben alle Produkte aus, die im Produktnamen den String „kamera‘‘ enthalten und im Onlineshop mindestens 5.000 mal verkauft wurden.

Beispiel 2: Vergleich von HAVING und WHERE – Abfrage mit HAVING-Klausel

Die Abfrage mit Hilfe der HAVING-Klausel benötigte insgesamt 2.656 Millisekunden.

Beispiel 3: Vergleich von HAVING und WHERE – Abfrage mit WHERE-Klausel

Eine entsprechend modifizierte Abfrage über eine WHERE-Bedingung benötigte 1.627 Millisekunden. Die Umstellung von HAVING auf WHERE bewirkte demnach eine Geschwindigkeitssteigerung von über 60%.

Es ist offensichtlich, dass eine Filterung über die WHERE-Klausel effektiver ist, da die Quelldaten bereits vor der Gruppierung selektiert werden und die darauffolgenden

Bearbeitungsschritte somit weniger Daten verarbeiten müssen. Bei HAVING werden die Zeilen mit GROUP BY zusammengefasst und erst im Anschluss erfolgt die eigentliche Filterung der Zeilen. Eine Verwendung der HAVING-Klausel macht also nur Sinn, wenn diese Aggregatfunktionen (z.B. COUNT(), SUM(), AVG(), MIN(), MAX() ) enthält - welche im WHERE-Teil nicht erlaubt sind. Ein weiterer Performancevorteil der WHERE-Klausel besteht in der Möglichkeit der Verwendung von Indizes für die angegebenen Bedingungen, die für HAVING nicht benutzt werden können.

Bevor jedoch eine SQL-Anweisung überhaupt ausgeführt wird, erstellt der SQL-Server einen Abfrageausführungsplan (engl. Execution Plan). In diesem wird unter anderem festgelegt, in welcher Reihenfolge auf die einzelnen Tabellen zugegriffen wird und welche Methoden verwendet werden, um die benötigten Daten aus den Tabellen zu filtern.

Der Abfrageausführungsplan wird hierbei in drei Bearbeitungsschritten von den drei nachfolgend beschriebenen Modulen erzeugt.

Der Parser

Im ersten Schritt scannt der Parser die SQL-Anweisung und zerlegt die einzelnen Anweisungen in logische Einheiten. Dabei wird eine einfache, formale Prüfung der SQL- Syntax durchgeführt. Letztendlich erstellt der Parser eine Abfragestruktur (auch Ausführungsbaum, Sequenzstruktur oder Syntaxbaum genannt), welche die logischen Schritte beschreibt, die für die Umwandlung der Quelldaten benötigt werden.

Der Algebrizer

Die vom Parser erzeugte Abfragestruktur wird nun vom Algebrizer weiterverarbeitet. Dabei führt er eine genauere Syntaxprüfung durch, die nicht nur die Richtigkeit der beteiligten Tabellen und Spalten, sondern auch die Datentypen überprüft. Der Algebrizer optimiert die Abfragestruktur weiter, entfernt dabei redundante Operationen und hilft bei der Bestimmung von Unterabfragen und Aggregatfunktionen. Als Ergebnis erzeugt der Algebrizer einen optimierten Syntaxbaum, welcher im Plancache gespeichert wird. Der Plancache ermöglicht die spätere Wiederverwendung von Ausführungsplänen, um das erneute Erstellen eines bereits erzeugten Ausführungsplans zu verhindern.

Der Abfrageoptimierer

Der Abfrageoptimierer erstellt die endgültige Abfragestruktur - den Ausführungsplan. Der Optimierer analysiert verschiedene Möglichkeiten des Zugriffs auf die Quelldaten, die letztlich alle zum korrekten Ergebnis führen. Dabei legt er unter anderem die Reihenfolge der Zugriffe auf die einzelnen Tabellen, die Verwendung von...