SQL Server

Optimieren der SQL Server-Abfrageleistung

Maciej Pilecki

Kurz zusammengefasst:

• Analysieren von Ausführungsplänen
• Optimieren von Abfragen
• Ermitteln optimierungsbedürftiger Abfragen

Beim Optimieren eines Datenbankservers müssen Sie die Leistung einzelner Abfragen verbessern. Dies ist ebenso wichtig wie die Optimierung anderer Aspekte Ihrer Serverinstallation, die einen Einfluss auf die Leistung haben, wie z. B. Hardware- und Softwarekonfiguration – oder vielleicht sogar noch wichtiger.

Selbst wenn Ihr Datenbankserver die leistungsfähigste Hardware nutzt, die verfügbar ist, kann er bereits durch wenige problematische Abfragen negativ beeinflusst werden. Schon eine schlechte Abfrage kann gravierende Leistungsprobleme auf Seiten der Datenbank nach sich ziehen.

Umgekehrt lässt sich die Gesamtleistung einer Datenbank stark verbessern, indem einige besonders aufwendige oder besonders oft ausgeführte Abfragen optimiert werden. In diesem Artikel werde ich einige Verfahren vorstellen, mit denen Sie die aufwendigsten Abfragen bzw. diejenigen mit der schlechtesten Leistung auf Ihrem Server ermitteln und optimieren können.

Analysieren von Ausführungsplänen

Wenn eine einzelne Abfrage optimiert werden soll, betrachtet man normalerweise zuerst ihren Ausführungsplan. Der Ausführungsplan beschreibt die Abfolge von physischen und logischen Vorgängen, die SQL ServerTM durchführt, um die Abfrage auszuführen und das gewünschte Resultset zu erstellen. Der Ausführungsplan wird von einer bestimmten Komponente des Datenbankmoduls, dem so genannten Abfrageoptimierer, während der Optimierungsphase der Abfrageverarbeitung erstellt. Dabei werden viele verschiedene Faktoren berücksichtigt, wie z. B. die in der Abfrage verwendeten Suchprädikate, die betroffenen Tabellen und ihre Verknüpfungsbedingungen, die Liste der zurückgegebenen Spalten und das Vorhandensein sinnvoller Indexe, die als effiziente Datenzugriffspfade dienen können.

Bei komplexen Abfragen kann die Zahl der möglichen Permutationen riesig sein, deshalb evaluiert der Abfrageoptimierer nicht alle Möglichkeiten, sondern versucht stattdessen einen Plan zu finden, der für eine gegebene Abfrage „gut genug“ ist. Denn nicht immer lässt sich ein perfekter Plan finden, und selbst wenn es möglich wäre, könnten die Kosten für die Bewertung aller Möglichkeiten zum Ermitteln dieses perfekten Plans leicht so hoch sein, dass sie den Wert aller Leistungsgewinne zunichte machen. Aus Sicht des DBAs ist es wichtig, den Prozess und seine Beschränkungen zu verstehen.

Es gibt eine Reihe von Möglichkeiten, einen Ausführungsplan für eine Abfrage erhalten:

• Management Studio bietet dazu die Funktionen „Display Actual Execution Plan“ (Tatsächlichen Ausführungsplan anzeigen) und „Display Estimated Execution Plan“ (Geschätzten Ausführungsplan anzeigen), mit denen der Plan grafisch dargestellt wird. Diese Features bieten die beste Lösung für eine direkte Untersuchung und sind bei weitem der am häufigsten gebrauchte Ansatz zum Anzeigen und Analysieren von Ausführungsplänen. (In diesem Artikel werde ich zum Illustrieren meiner Beispiele auf diese Weise erstellte grafische Pläne verwenden.)
• Verschiedene SET-Optionen, wie z. B. SHOWPLAN_XML und SHOWPLAN_ALL, geben den Ausführungsplan entweder als XML-Dokument zurück, das den Plan mit einem speziellen Schema beschreibt, oder als Rowset mit Textbeschreibung zu den einzelnen Vorgängen im Ausführungsplan.
• SQL Server Profiler-Ereignisklassen, wie z. B. „Showplan XML“, ermöglichen die Erfassung von Ausführungsplänen von Anweisungen, die während eines Ablaufverfolgung gesammelt wurden.

Auch wenn die XML-Darstellung des Ausführungsplans vielleicht für einen Menschen nicht die am leichtesten lesbare Form ist, ermöglicht diese Variante doch das Schreiben von Prozeduren und Dienstprogrammen, mit denen Ausführungspläne analysiert und nach Anzeichen für Leistungsprobleme und suboptimale Pläne durchsucht werden können. Eine XML-basierte Darstellung kann auch in einer Datei mit der Erweiterung „.sqlplan“ gespeichert und dann in Management Studio geöffnet werden, um eine grafische Darstellung zu erzeugen. Diese Dateien können auch zur späteren Analyse gespeichert werden und machen damit die Notwendigkeit einer erneuten Erstellung des Ausführungsplans bei jeder Analyse überflüssig. Dies ist besonders nützlich, wenn Sie Ihre Pläne vergleichen wollen, um Veränderungen im Laufe der Zeit zu beobachten.

Geschätzte Ausführungskosten

Zunächst müssen Sie bei Ausführungsplänen verstehen, wie sie erzeugt werden. SQL Server verwendet einen kostenbasierten Abfrageoptimierer, der versucht, den Ausführungsplan mit den niedrigsten geschätzten Kosten zu erzeugen. Die Schätzung basiert auf der Datenverteilungsstatistik, die für den Optimierer verfügbar ist, wenn die an der Abfrage beteiligten Tabellen ausgewertet werden. Wenn diese Statistiken fehlen oder veraltet sind, fehlen dem Abfrageoptimierer wichtige Informationen für die Abfrageoptimierung, und die Schätzungen werden wahrscheinlich ungenau sein. Dann kann es passieren, dass der Optimierer einen nicht optimalen Plan wählt, weil die Ausführungskosten der verschiedenen Pläne über- oder unterschätzt wurden.

Über die geschätzten Ausführungskosten gibt es einige falsche Annahmen. Insbesondere wird oft angenommen, dass die geschätzten Ausführungskosten ein guter Indikator dafür sind, wie lange die Ausführung einer Abfrage dauern wird, und dass sich anhand dieser Schätzung gute von schlechten Plänen unterscheiden lassen. Das ist jedoch nicht der Fall. Erstens ist nicht ausreichend dokumentiert, in welchen Einheiten die geschätzten Kosten ausgedrückt werden und ob sie in direkter Beziehung zur Ausführungszeit stehen. Zweitens geht es hier um eine Schätzung, die natürlich auch falsch sein kann. Daher können Pläne mit höheren geschätzten Kosten in punkto CPU, E/A und Ausführungszeit unter Umständen viel effizienter sein, trotz des höheren Schätzwerts. Dies kommt häufig bei Abfragen vor, die Tabellenvariable beinhalten. Da für Tabellenvariable keine Statistiken verfügbar sind, nimmt der Abfrageoptimierer für jede Tabellenvariable an, dass sie nur eine Zeile enthält, selbst wenn sie wesentlich umfangreicher ist. Der Abfrageoptimierer wählt den Plan also anhand einer ungenauen Schätzung aus. Deshalb sollten Sie sich beim Vergleichen der Ausführungspläne Ihrer Abfragen nicht allein auf die geschätzten Abfragekosten verlassen. Berücksichtigen Sie stattdessen die Ausgabe der Optionen STATISTICS I/O und STATISTICS TIME in der Analyse, um die tatsächlichen Ausführungskosten in punkto E/A und CPU-Zeit zu erkennen.

An dieser Stelle ist auch ein besonderer Typ von Ausführungsplan zu erwähnen, der parallele Plan. Wenn Sie Ihre Abfrage auf einem Server mit mehreren CPUs ausführen und die Abfrage für eine Parallelausführung geeignet ist, kann ein paralleler Plan ausgewählt werden. (Gewöhnlich zieht der Abfrageoptimierer einen parallelen Plan nur für eine Abfrage in Betracht, deren Kosten einen bestimmten konfigurierbaren Schwellenwert überschreiten.) Aufgrund des Aufwands für die Verwaltung mehrerer paralleler Ausführungsthreads (also die Verteilung der Arbeit auf mehrere Threads, die Synchronisierung und die Zusammenstellung der Ergebnisse) ist die Ausführung paralleler Pläne kostenintensiver, was sich auch in ihren geschätzten Kosten zeigt. Warum werden sie also gegenüber billigeren, nicht parallelen Plänen bevorzugt? Weil die Rechenleistung mehrerer CPUs genutzt wird, liefern parallele Pläne tendenziell schneller Ergebnisse als Standardpläne. Je nach Szenario und in Abhängigkeit von Variablen wie verfügbaren Ressourcen und Last durch andere, gleichzeitige Abfragen kann dies in einem Setup wünschenswert sein. Wenn das der Fall ist, sollten Sie steuern, welche Ihrer Abfragen parallele Pläne erstellen können und wie viele CPUs jede Abfrage nutzen kann. Dazu legen Sie auf Serverebene den maximalen Grad an Parallelität fest. Anschließend können Sie diese Option auf Ebene einzelner Abfragen bei Bedarf mit „OPTION (MAXDOP n)“ überschreiben.

Analysieren von Ausführungsplänen

Betrachten wir nun eine einfache Abfrage, ihren Ausführungsplan und einige Möglichkeiten zum Verbessern der Leistung. Angenommen, ich führe diese Abfrage mit Management Studio aus und habe die Option „Tatsächlichen Ausführungsplan einschließen“ in der „Adventure Works“-Beispieldatenbank in SQL Server 2005 aktiviert:

SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID

Als Ergebnis wird der in Abbildung 1 dargestellte Ausführungsplan angezeigt. Diese einfache Abfrage berechnet den Gesamtbetrag der Bestellungen, die von allen Kunden des fiktiven Unternehmens „Adventure Works“ aufgegeben wurden. Wenn Sie nun den Ausführungsplan betrachten, können Sie sehen, wie das Datenbankmodul die Abfrage verarbeitet und das Ergebnis erzeugt. Grafische Ausführungspläne werden von oben nach unten und von rechts nach links gelesen. Jedes Symbol steht für einen durchgeführten logischen oder physischen Vorgang, und die Pfeile zeigen den Datenfluss zwischen den Vorgängen. Die Dicke der Pfeile steht für die Anzahl an Zeilen, die von den Vorgängen übergeben werden: Je dicker der Pfeil, desto mehr Zeilen sind beteiligt. Wenn Sie den Mauszeiger auf eines der Operatorsymbole setzen, werden in einem gelben QuickInfo (wie in Abbildung 2) Details zum jeweiligen Vorgang angezeigt.

Abbildung 1** Beispielausführungsplan **(Klicken Sie zum Vergrößern auf das Bild)

Abbildung 2** Details zu einem Vorgang **(Klicken Sie zum Vergrößern auf das Bild)

Wenn Sie die einzelnen Operatoren betrachten, können Sie die Reihenfolge der ausgeführten Schritte analysieren:

1. Das Datenbankmodul führt auf der Tabelle „Sales.Customer“ einen „Clustered Index Scan“-Vorgang durch, bei dem ein gruppierter Index durchsucht wird, und gibt für alle Zeilen in dieser Tabelle die Spalte „CustomerID“ zurück.
2. Anschließend wird ein „Index Scan“-Vorgang durchgeführt, bei dem einer der nicht gruppierten Indexe in der Tabelle „Sales.SalesOrderHeader“ durchsucht wird. Dabei handelt es sich um einen Index auf der Spalte „CustomerID“, der aber implizit auch die Spalte „SalesOrderID“ umfasst (den Gruppierungsschlüssel der Tabelle). Im Anschluss an die Suche werden die Werte aus beiden Spalten zurückgegeben.
3. Die Ausgabe beider Suchen wird mit dem physischen Operator „Merge Join“ auf der Spalte „CustomerID“ verknüpft. (Dies ist eine von drei möglichen physischen Varianten zum Durchführen einer logischen „Join“-Operation. Sie ist schnell, erfordert aber, dass beide Eingaben nach einer verknüpften Spalte sortiert sind. In diesem Fall haben beide Suchvorgänge die Zeilen bereits nach „CustomerID“ sortiert zurückgegeben, sodass ein zusätzlicher Sortiervorgang nicht erforderlich ist.)
4. Anschließend durchsucht das Datenbankmodul den gruppierten Index auf der Tabelle „Sales.SalesOrderDetail“ und ruft die Werte von vier Spalten („SalesOrderID“, „OrderQty“, „UnitPrice“ und „UnitPriceDiscount“) aus allen Zeilen in dieser Tabelle ab. (Geschätzt wurde eine Rückgabe von 123.317 Zeilen, und diese Anzahl entspricht der tatsächlichen Rückgabe, wie Sie Abbildung 2 entnehmen können. Die Schätzung war also sehr präzise.)
5. Zeilen, die beim Durchsuchen des gruppierten Indexes erstellt wurden, werden an den ersten „Compute Scalar“-Operator übergeben, damit der Wert der berechneten Spalte „LineTotal“ für jede Zeile berechnet werden kann, basierend auf den in der Formel verwendeten Spalten „OrderQty“, „UnitPrice“ und „UnitPriceDiscount“.
6. Der zweite „Compute Scalar“-Operator wendet die Funktion „ISNULL“ auf das Ergebnis der vorherigen Berechnung an, wie durch die Formel der berechneten Spalte angegeben. Dies schließt die Berechnung der Spalte „LineTotal“ ab und gibt sie, zusammen mit der Spalte „SalesOrderID“, an den nächsten Operator zurück.
7. Die Ausgabe des „Merge Join“-Operators in Schritt 3 wird mithilfe des physischen Operators „Hash Match“ mit der Ausgabe des „Compute Scalar“-Operators aus Schritt 6 verknüpft.
8. Dann wird ein weiterer „Hash Match“-Operators angewendet, um die von „Merge Join“ zurückgegebenen Zeilen nach dem Wert der Spalte „CustomerID“ und dem berechneten SUM-Aggregat der Spalte „LineTotal“ zu gruppieren.
9. Der letzte Knoten, SELECT, ist kein physischer oder logischer Operator, sondern ein Platzhalter, der für die Gesamtergebnisse und -kosten der Abfrage steht.

Auf meinem Laptop hat dieser Ausführungsplan geschätzte Kosten von 3.31365 (wie in Abbildung 3 gezeigt). Wenn bei der Ausführung „STATISTICS I/O“ auf ON gesetzt ist, meldet die Abfrage die Durchführung von insgesamt 1.388 logischen Lesevorgängen in den drei beteiligten Tabellen. Die unter den Operatoren angezeigten Prozentwerte geben die Kosten des jeweiligen Operators im Verhältnis zu den geschätzten Gesamtkosten des ganzen Ausführungsplans an. In Abbildung 1 sehen Sie, dass der Großteil der Gesamtkosten des Ausführungsplans durch folgende drei Operatoren entsteht: den Operator „Clustered Index Scan“ auf der Tabelle „Sales.SalesOrderDetail“ und die zwei „Hash Match“-Operatoren. Bevor ich jedoch mit ihrer Optimierung beginne, möchte ich noch auf eine sehr einfache Änderung meiner Abfrage hinweisen, durch die ich zwei der Operatoren ganz beseitigen kann.

Abbildung 3** Geschätzte Gesamtausführungskosten der Abfrage **

Da ich von der Tabelle „Sales.Customer“ nur die Spalte „CustomerID“ zurückgebe und diese Spalte auch als Fremdschlüssel in der Tabelle „Sales.SalesOrderHeader“ enthalten ist, kann ich die Tabelle „Customer“ aus der Abfrage ganz entfernen, ohne die logische Bedeutung oder das Ergebnis dieser Abfrage zu verändern. Dazu verwende ich folgenden Code:

SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

Das Ergebnis ist ein anderer Ausführungsplan, der in Abbildung 4 dargestellt ist.

Abbildung 4** Ausführungsplan nach Entfernen der Tabelle „Customer“ aus der Abfrage **(Klicken Sie zum Vergrößern auf das Bild)

Zwei Vorgänge wurden dabei völlig beseitigt: der „Clustered Index Scan“-Vorgang auf der Tabelle „Customer“ und der „Merge Join“-Vorgang auf den Tabellen „Customer“ und „SalesOrderHeader“. Zusätzlich wurde auch der „Hash Match“-Vorgang durch einen viel effizienteren „Merge Join“-Vorgang ersetzt. Um jedoch den „Merge Join“-Operator auf die Tabellen „SalesOrderHeader“ und „SalesOrderDetail“ anwenden zu können, müssen die Zeilen beider Tabellen nach der Verknüpfungsspalte „SalesOrderID“ sortiert zurückgegeben werden. Dazu verwendet der Abfrageoptimierer einen „Clustered Index Scan“ auf der Tabelle „SalesOrderHeader“ anstelle des einfachen „Index Scan“-Vorgangs, der in Sachen E/A billiger gewesen wäre. Dies ist ein gutes Beispiel dafür, wie der Abfrageoptimierer in der Praxis funktioniert. Da die Kosteneinsparungen durch die Änderung der physischen Verknüpfung größer waren als die zusätzlichen E/A-Kosten durch den „Clustered Index Scan“-Vorgang, hat der Abfrageoptimierer die resultierende Kombination von Operatoren gewählt, da hier die geschätzte Gesamtsumme der Ausführungskosten am geringsten war. Auf meinem Laptop ist die Anzahl logischer Lesevorgänge zwar gestiegen (auf 1.941), die benötigte CPU-Zeit war jedoch kürzer, und die geschätzten Ausführungskosten dieser Abfrage waren um etwa 13 % geringer (2.89548).

Angenommen, ich möchte die Leistung dieser Abfrage weiter verbessern. Ich betrachte den „Clustered Index Scan“-Vorgang auf der Tabelle „SalesOrderHeader“, der nun der teuerste Operator in diesem Ausführungsplan ist. Da ich für die Abfrage nur zwei Spalten dieser Tabelle benötige, kann ich einen nicht gruppierten Index erstellen, der nur diese zwei Spalten enthält. So kann ich das Durchsuchen der ganzen Tabelle durch das Durchsuchen des viel kleineren, nicht gruppierten Indexes ersetzen. Die Indexdefinition könnte etwa so aussehen:

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

Beachten Sie, dass der erstellte Index eine berechnete Spalte umfasst. Dies ist je nach Definition der berechneten Spalte nicht immer möglich.

Nach Erstellen dieses Indexes und Ausführen der gleichen Abfrage erhalte ich den neuen Ausführungsplan wie in Abbildung 5 gezeigt.

Abbildung 5** Optimierter Ausführungsplan **(Klicken Sie zum Vergrößern auf das Bild)

Das Durchsuchen des gruppierten Indexes auf der Tabelle „SalesOrderDetail“ wurde durch das Durchsuchen eines nicht gruppierten Indexes mit bedeutend geringeren E/A-Kosten ersetzt. Außerdem habe ich einen der „Compute Scalar“-Operatoren eliminiert, da mein Index einen bereits berechneten Wert für die Spalte „LineTotal“ enthält. Die geschätzten Kosten für den Ausführungsplan betragen jetzt 2.28112, und die Abfrage führt bei der Ausführung 1.125 logische Lesevorgänge durch.

Der abdeckende Index

Übung: Kundenauftragsabfrage

Aufgabe: Hier eine Übung zum Abfragen der Kundenaufträge. Versuchen Sie, die Indexdefinition zu erstellen. Welche Spalten muss sie enthalten, um als abdeckender Index für diese Abfrage verwendet werden zu können? Kann sich die Reihenfolge der Spalten in der Indexdefinition auf die Leistung auswirken?

Lösung: Ihre Aufgabe war es, den bestmöglichen abdeckenden Index auf der Tabelle „Sales.SalesOrderHeader“ für die Beispielabfrage aus diesem Artikel zu erstellen. Dazu müssen Sie zunächst feststellen, dass diese Abfrage nur zwei Spalten der Tabelle verwendet: „CustomerID“ und „SalesOrderID“. Wenn Sie meinen Artikel sorgfältig gelesen haben, werden Sie bemerkt haben, dass im Fall der Tabelle „SalesOrderHeader“ bereits ein Index existiert, der diese Abfrage abdeckt, nämlich der Index auf der Spalte „CustomerID“. Er enthält bereits implizit die Spalte „SalesOrderID“, die als Gruppierungsschlüssel der Tabelle dient.

Ich habe ja auch erklärt, warum der Abfrageoptimierer diesen Index nicht verwendet. Natürlich könnten Sie den Abfrageoptimierer zwingen, diesen Index zu verwenden. Aber diese Lösung wäre weniger effizient als der bereits bestehende Plan, bei dem ein „Clustered Index Scan“-Vorgang und „Merge Join“-Operatoren zum Einsatz kommen. Sie würden den Abfrageoptimierer zwingen, zwischen dem Durchführen eines zusätzlichen Sortiervorgangs (um „Merge Join“ verwenden zu können) und dem Rückgriff auf einen weniger effizienten „Hash Join“-Operator zu wählen. In beiden Fallen sind die geschätzten Ausführungskosten höher als bei dem vorhandenen Plan (die Variante mit dem „Sort“-Vorgang wäre besonders ungünstig). Deshalb wird der Abfrageoptimierer sie nur verwenden, wenn er dazu gezwungen wird. Daher würde in diesem Fall nur ein nicht gruppierter Index auf „SalesOrderID“, „CustomerID“ eine bessere Leistung ermöglichen als der „Clustered Index Scan“-Vorgang. Wichtig ist jedoch, dass die Spalten in genau dieser Reihenfolge stehen müssen:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

Wenn Sie diesen Index erstellen, enthält der Ausführungsplan anstelle des „Clustered Index Scan“-Operators den „Index Scan“-Operator. Das ist ein bedeutender Unterschied. In diesem Fall ist der nicht gruppierte Index mit nur zwei Spalten wesentlich kleiner als die ganze Tabelle in Form eines gruppierten Indexes. Deshalb werden zum Lesen der notwendigen Daten weniger E/A-Operationen benötigt.

Dieses Beispiel zeigt auch, wie die Reihenfolge der Spalten im Index erhebliche Auswirkungen auf dessen Nutzen für den Abfrageoptimierer haben kann. Behalten Sie dies bei der Erstellung mehrspaltiger Indexe immer im Blick.

Der auf der Tabelle „SalesOrderDetail“ erstellte Index ist ein Beispiel für einen so genannten abdeckenden Index. Dabei handelt es sich um einen nicht gruppierten Index, der alle zum Durchführen der Abfrage benötigten Spalten enthält und ein Durchsuchen der ganzen Tabelle mit den Operatoren „Table Scan“ oder „Clustered Index Scan“ überflüssig macht. Der Index ist im Prinzip eine kleinere Kopie der Tabelle, die eine Teilmenge der Spalten in der Tabelle enthält. Nur die zum Durchführen der Abfragen benötigten Spalten sind im Index enthalten. Er enthält also nur das, was zum „Abdecken“ der Abfrage benötigt wird.

Das Erstellen abdeckender Indexe für die am häufigsten ausgeführten Abfragen ist eine der einfachsten und gebräuchlichsten Methoden zum Optimieren von Abfragen. Dies funktioniert besonders gut, wenn die Tabelle viele Spalten enthält, von denen jedoch nur wenige häufig in Abfragen verwendet werden. Durch das Erstellen eines oder mehrerer abdeckender Indexe können Sie die Leistung der betreffenden Abfragen deutlich verbessern, da auf eine wesentlich kleinere Datenmenge zugegriffen wird und weniger E/A-Operationen benötigt werden. Die Wartung zusätzlicher Indexe verursacht jedoch bei Datenänderungsvorgängen (INSERT, UPDATE und DELETE) versteckte Kosten. In Abhängigkeit von Ihrer Umgebung und dem Verhältnis zwischen SELECT-Abfragen und Datenänderungen sollten Sie daher genau abwägen, ob die Leistungsverbesserungen bei Abfragen diese zusätzlichen Kosten für die Indexwartung rechtfertigen.

Doch keine Angst vor der Erstellung mehrspaltiger Indexe anstelle von einspaltigen Indexen. Häufig sind sie viel nützlicher als einspaltige Indexe, und es ist viel wahrscheinlicher, dass der Abfrageoptimierer sie verwenden kann, um die Abfrage abzudecken. Die meisten abdeckenden Indexe sind mehrspaltige Indexe.

Bei meiner Beispielabfrage gibt es jedoch noch weitere Verbesserungsmöglichkeiten. Beispielsweise ließe sie sich weiter optimieren, indem für die Tabelle „SalesOrderHeader“ ein abdeckender Index erstellt wird. Dadurch kann anstelle des „Clustered Index Scan“-Vorgangs ein einfacher „Index Scan“-Vorgang auf einem nicht gruppierten Index durchgeführt werden. Das ist eine gute Übung für Sie. Versuchen Sie, die Indexdefinition zu erstellen. Welche Spalten muss sie enthalten, um als abdeckender Index für diese Abfrage verwendet werden zu können? Kann sich die Reihenfolge der Spalten in der Indexdefinition auf die Leistung auswirken? Die Lösung finden Sie in der Randleiste "Übung: Kundenauftragsabfrage".

Indizierte Sichten

Wenn die Leistung meiner Beispielabfrage sehr wichtig ist, kann ich noch einen Schritt weiter gehen und eine indizierte Sicht erstellen, in der die Ergebnisse der Abfrage physisch gespeichert werden. Für indizierte Sichten gelten bestimmte Voraussetzungen und Einschränkungen, aber wenn die Verwendung möglich ist, sind damit erhebliche Leistungssteigerungen machbar. Bedenken Sie, dass mit indizierten Sichten höhere Wartungskosten als bei Standardindexen verbunden sind. Daher sollten Sie diese mit Bedacht einsetzen. In diesem Fall sieht die Indexdefinition wie folgt aus:

CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID 

Beachten Sie die Option „WITH SCHEMABINDING“, die eine Voraussetzung für die Erstellung eines Indexes auf einer solchen Sicht ist, und die Funktion „COUNT_BIG(*)“, die notwendig ist, wenn die Indexdefinition eine Aggregatfunktion (in diesem Fall „SUM“) enthält. Nachdem ich die Sicht erstellt habe, kann ich einen Index dafür erstellen:

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID 
ON vTotalCustomerOrders(CustomerID)

Wenn ich diesen Index erstelle, wird das Ergebnis der Abfrage in der Definition der Sicht „materialisiert“ und physisch auf dem Datenträger im Index gespeichert. Beachten Sie, dass durch alle Datenänderungsvorgänge auf den Basistabellen automatisch die Werte in der Sicht gemäß dieser Definition aktualisiert werden.

Was passiert, wenn ich die Abfrage wiederhole, hängt davon ab, welche Edition von SQL Server ich verwende. Bei den Enterprise- und Developer-Editionen wird der Abfrageoptimierer automatisch diese Abfrage mit der Definition der indizierten Sicht abgleichen und die indizierte Sicht verwenden, statt die betreffenden Basistabellen abzufragen. Abbildung 6 zeigt den in diesem Fall erstellten Ausführungsplan Er besteht aus nur einem Vorgang, nämlich einem „Clustered Index Scan“-Vorgang des Indexes, den ich für die Sicht erstellt habe. Die geschätzten Ausführungskosten betragen nur 0.09023, und es finden nur 92 logische Lesevorgänge statt.

Abbildung 6** Ausführungsplan bei Verwendung einer indizierten Sicht **(Klicken Sie zum Vergrößern auf das Bild)

Sie können diese indizierte Sicht auch mit anderen Versionen von SQL Server erstellen und verwenden. Um jedoch dieselbe Wirkung zu erzielen, müssen Sie die Abfrage so ändern, dass mit NOEXPAND direkt auf die Sicht verwiesen wird:

SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)

Wie Sie sehen sind indizierte Sichten, richtig eingesetzt, ein sehr leistungsfähiges Feature. Sie sind besonders zum Optimieren von Abfragen geeignet, in denen Aggregationen über große Mengen von Daten erfolgen. Bei Verwendung mit der Enterprise Edition können viele Abfragen davon profitieren, ohne dass Änderungen am Code erforderlich sind.

Ermitteln optimierungsbedürftiger Abfragen

Wie stelle ich fest, bei welchen Abfragen sich eine Optimierung lohnt? Zunächst suche ich nach Abfragen, die häufig ausgeführt werden. Bei ihnen mögen zwar die Kosten für eine einzige Ausführung nicht sehr groß sein, aber die aggregierten Kosten für alle Ausführungen können wesentlich größer sein als bei einer großen, selten ausgeführten Abfrage. Das soll keinesfalls heißen, dass sich eine Optimierung großer Abfragen nicht lohnen kann. Aber ich bin der Ansicht, dass man sich zuerst auf die am häufigsten ausgeführten Abfragen konzentrieren sollte. Wie also lassen sich diese ermitteln?

Leider ist die zuverlässigste Methode etwas kompliziert und erfordert die Durchführung einer Ablaufverfolgung für alle auf dem Server ausgeführten Abfragen und das anschließende Gruppieren der Abfragen anhand ihrer Signaturen. (Dabei werden im Abfragetext die eigentlichen Parameterwerte durch Platzhalter ersetzt, damit der gleiche Abfragetyp auch dann ermittelt werden kann, wenn die Ausführung mit anderen Parameterwerten erfolgt.) Dieser Vorgang ist kompliziert, da es schwierig ist, Abfragesignaturen zu erzeugen. Itzik Ben-Gan beschreibt in seinem Buch „Inside Microsoft SQL Server 2005: T-SQL Querying“ eine Lösung mit benutzerdefinierten CLR-Funktionen und regulären Ausdrücken.

Es gibt jedoch eine wesentlich einfachere, wenn auch nicht ganz so zuverlässige Methode. Dabei nutzen Sie die Statistiken, die im Ausführungsplancache für alle Abfragen gespeichert werden, und fragen diese mittels dynamischer Verwaltungsansichten ab. Abbildung 7 enthält eine Beispielabfrage, die sowohl den Text als auch den Ausführungsplan der 20 Abfragen in Ihrem Cache mit der höchsten Gesamtzahl von logischen Lesevorgängen zeigt. Diese Abfrage ist sehr praktisch zum schnellen Ermitteln der Abfragen, die die meisten logischen Lesevorgänge erzeugen, doch ihre Möglichkeiten sind begrenzt. Das liegt daran, dass sie nur die Abfragen anzeigt, deren Pläne zur Zeit der Abfrage bereits zwischengespeichert sind. Was nicht zwischengespeichert ist, kann auf diese Weise auch nicht erfasst werden.

Figure 7 Ermitteln der 20 aufwendigsten Abfragen in Bezug auf Lese-E/A

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Sobald Sie die Abfragen mit den schlechten Leistungswerten ermittelt haben, betrachten Sie deren Abfragepläne und versuchen, die Leistung mithilfe der in diesem Artikel beschriebenen Indizierungsverfahren zu verbessern. Wenn es Ihnen gelingt, war die aufgewendete Zeit gut investiert.

Viel Spaß beim Optimieren!

Maciej Pilecki ist Associate Mentor bei Solid Quality Mentors, einer weltweit tätigen Organisation, die auf Schulung, Mentoring und Beratung spezialisiert ist. Er ist Microsoft Certified Trainer (MCT) und SQL Server-MVP (Most Valuable Professional) und bietet häufig Kurse an und hält oft Vorträge auf Konferenzen zu unterschiedlichen Aspekten von SQL Server und der Anwendungsentwicklung.

© 2008 Microsoft Corporation und CMP Media, LLC. Alle Rechte vorbehalten. Die nicht genehmigte teilweise oder vollständige Vervielfältigung ist nicht zulässig.