SQL-Abfragen optimieren - Was Entwickler über Performance wissen müssen

2 Interne Verarbeitung von Abfragen

Um besser einzuschätzen zu können, an welchen Stellen eine Abfrageoptimierung erfolgversprechend ist, hilft es zu wissen, wie SQL-Abfragen im Detail intern verarbeitet werden.

2.1 Ablauf der Abfrageverarbeitung

Bevor eine Abfrage ausgeführt wird, erstellt SQL Server einen Ausführungsplan, in dem festgelegt wird, wie auf die beteiligten Tabellen zugegriffen wird. Genau genommen geschieht dies in drei Schritten, bei denen verschiedene Komponenten beteiligt sind.

1. Der so genannte Parser prüft die Syntax der SQL-Anweisung und erstellt einen groben Ausführungsbaum.
2. Der Algebrizer erledigt ähnliche Aufgaben wie der Parser, geht hierbei jedoch einen Schritt weiter, da nun auch die vorhandenen Datenstrukturen der von der Abfrage verwendeten Datenbankobjekte berücksichtigt werden. So werden einerseits die Namen von Tabellen und Spalten geprüft, aber auch die Korrektheit der dazugehörigen Datentypen. Als Ergebnis des Algebrizers wird der logische Ausführungsbaum weiter optimiert und als Syntaxbaum im Plan-Cache gespeichert (der Plan-Cache wird weiter hinten in diesem Kapitel ausführlicher behandelt).
3. Der Optimizer (dt.: Optimierer) schließlich erstellt den eigentlichen Ausführungsplan, der die Art und Reihenfolge des Zugriffs auf die beteiligten Tabellen regelt. Auch dieser Ausführungsplan wird im Plan-Cache gespeichert.

Dieses Vorgehen greift bei der Verarbeitung von Ad-hoc-Abfragen, parametrisierten Abfragen und sogar gespeicherten Prozeduren. Im Allgemeinen ist die oben dargestellte Dreiteilung nicht so relevant. Der wesentliche Punkt ist die Tatsache, dass der Optimierer einen Ausführungsplan erstellt, in dem festgelegt wird, wie die Abfrage intern ausgeführt wird.

Dabei werden die einzelnen Klauseln einer SQL-Abfrage in folgender Reihenfolge abgehandelt:

1. FROM bestimmt, welche Tabellen gelesen und wie sie miteinander verknüpft werden
2. WHERE filtert die Tabellen zeilenweise aufgrund von Bedingungen
3. GROUP BY fasst Zeilen des Zwischenergebnisses zusammen
4. HAVING filtert die Zeilen des Zwischenergebnisses anhand von Bedingungen
5. SELECT wählt die als Ergebnis zurückzugebenden Spalten aus
6. ORDER BY sortiert die Ergebniszeilen
7. TOP/OFFSET reduziert die Ergebnismenge auf eine bestimmte Anzahl von Zeilen (bei Verwendung von OFFSET ab einer bestimmten Position)

Auffällig ist dabei, dass dies nicht ganz der Reihenfolge entspricht, in der die einzelnen Klauseln in einer SQL-Abfrage angegeben werden. So werden die einzelnen Spalten, deren Daten gelesen werden, bei der Abfrage zwar gleich am Anfang angegeben, bei der Ausführung aber erst im vorletzten Schritt berücksichtigt.

Hinweis: Etwas logischer ist dies in der Abfragesyntax der Abfragesprache LINQ gestaltet. Hier werden die Klauseln in der Reihenfolge FROM, WHERE, ORDER BY, SELECT aufgeführt, was schon eher der Auswertungsreihenfolge entspricht und insbesondere verbesserte Möglichkeiten für die Eingabeunterstützung mittels IntelliSense im Code-Editor bietet. Ähnlich verhält es sich bei den in XQuery verwendeten FLWOR-Ausdrücken. Diese werden – entsprechend der Abkürzung – in der Reihenfolge FOR, LET, WHERE, ORDER BY, RETURN ausgewertet. Wenn man sich hier die letzten drei Bestandteile anschaut, wird zuerst die Menge der Zeilen mit WHERE gefiltert, dann mit ORDER BY sortiert und schließlich werden mit RETURN die zurückzugebenden Werte definiert.

2.2 Ausführungspläne

Im SQL Server Management Studio kann man sich den Ausführungsplan zu einer Abfrage anzeigen lassen. Dabei wird zwischen dem geschätzten und realen Ausführungsplan unterschieden.

Der geschätzte Ausführungsplan wird angezeigt, ohne dass die Abfrage wirklich ausgeführt wird. Dazu wählen Sie im Management Studio den Menüpunkt Abfrage |Geschätzten Ausführungsplan anzeigen oder das dazu gehörende Symbol in der Symbolleiste. Bis SQL Server 2008 R2 gab es zusätzlich noch eine entsprechende Tastenkombination STRG+L.

Alternativ kann der tatsächliche Ausführungsplan angezeigt werden, sobald die Abfrage ausgeführt wird. Auch hierzu gibt es einen Menüpunkt (Abfrage | Tatsächlichen Ausführungsplan einschließen), ein Symbol in der Symbolleiste sowie bis SQL Server 2008 R2 auch eine Tastenkombination (STRG+M). Durch jede dieser drei Varianten wird die Option umgeschaltet, also aktiviert bzw. deaktiviert. Wenn Sie die Anzeige des tatsächlichen Ausführungsplans aktiviert haben und anschließend die Abfrage ausführen, erscheint im Ergebnisbereich neben den Registerkarten Ergebnisse und Meldungen noch eine weitere, die mit Ausführungsplan betitelt ist und den tatsächlichen Ausführungsplan beinhaltet.

So wird für die folgende Abfrage …

… beispielsweise der in der Abbildung 2.1 dargestellte Ausführungsplan erstellt.

Doch wodurch unterscheiden sich der geschätzte und der tatsächliche Ausführungsplan?

Der geschätzte Ausführungsplan ist der Plan, der vom Abfrageoptimierer erstellt wird. Während die Abfrage ausgeführt wird, kann es jedoch vorkommen, dass beim Lesen von Tabellen oder Indizes festgestellt wird, dass der geschätzte Ausführungsplan auf veralteten Statistiken beruhte. Das kann dann zur Folge haben, dass der tatsächliche Ausführungsplan noch während der Ausführung angepasst wird und sich der tatsächliche Ausführungsplan damit vom geschätzten Plan unterscheidet.

Abbildung 2.1: Die grafische Darstellung eines Ausführungsplans

Verwenden Sie also zur Bewertung einer Abfrage nach Möglichkeit immer den tatsächlichen Ausführungsplan. Bei Abfragen mit extrem langer Laufzeit können Sie notfalls auf den geschätzten Ausführungsplan ausweichen.

Unabhängig davon, ob Sie den geschätzten oder tatsächlichen Ausführungsplan anzeigen lassen, ist aus diesem zu erkennen, in welcher Reihenfolge auf welche Tabellen zugegriffen wird, ob und welche Indizes verwendet werden, wie die verschiedenen Zwischenergebnisse miteinander verknüpft werden und vieles mehr.

Die im grafischen Ausführungsplan dargestellten Symbole stellen einzelne Operationen dar, von denen es eine relativ große Menge gibt. Eine vollständige Auflistung aller möglichen Operationen mit den dazu gehörenden Symbolen finden Sie im Internet unter http://msdn.microsoft.com/de-de/library/ms175913.aspx

Etwas gewöhnungsbedürftig ist sicherlich die Reihenfolge, in der die grafischen Ausführungspläne zu lesen sind. Da sie in einer Art Baumstruktur dargestellt werden, stellt die Wurzel des Baums (links oben) das Endergebnis dar, während die ersten Schritte, die ausgeführt werden, ganz rechts in den Blättern der Baumstruktur stehen. Hier sind normalerweise die eigentlichen Tabellen- oder Indexzugriffe zu finden, die auf verschiedene Arten stattfinden können1:

• Table Scan: sequenzielles Lesen der gesamten Tabelle
• Clustered Index Scan: sequenzielles Lesen des gruppierten Index
• Clustered Index Seek: gezielter Zugriff auf einzelne Zeilen des gruppierten Index
• Index Scan: sequenzielles Lesen des nicht gruppierten Index
• Index Seek: gezielter Zugriff auf einzelne Zeilen des nicht gruppierten Index

Verfolgt man die Verbindungslinien nun schrittweise in Richtung Wurzel, so lässt sich nachvollziehen, wie die im ersten Schritt gelesenen Daten weiter verarbeitet werden. So folgt beispielsweise nach einem Zugriff auf einen nicht gruppierten Index (per Index Scan oder Index Seek) meist ein Row Lookup, mit dem die eigentlichen Zeilendaten aus der Tabelle gelesen werden.

Hinweis: Damit der Abfrageoptimierer entscheiden kann, ob ein Indexzugriff sinnvoll, oder es doch effektiver ist, direkt über einen Table Scan die gesamte Tabelle zu lesen, sind zwei Informationen entscheidend. Die erste ist die Anzahl der Zeilen in der Tabelle, die zweite die Selektivität des Index. Sowohl bei geringer Zeilenanzahl als auch bei geringer Selektivität des Index (also wenn ohnehin fast alle Zeilen der Tabelle gelesen werden müssen), ist es effektiver, einen Table Scan auszuführen als den Umweg über den Index zu gehen. Damit dies möglich ist, wird die Anzahl der Zeilen in den verschiedenen Tabellen in Statistiken mit protokolliert. Diese müssen allerdings von Zeit zu Zeit aktualisiert werden, da sie sonst irreführende Werte beinhalten, die zu nicht optimalen Ausführungsplänen führen können.

In dem in Abbildung 2.1 dargestellten Beispiel wird der komplette Index...