Mich erreichte heute die Email eines Bekannten, in der ich gebeten wurde, einen von ihm erstellten Index zu überprüfen. Die Analyse der Ausführungspläne hat gezeigt, dass SORT-Operationen bis zu 40% der Kosten des Ausführungsplans ausgemacht haben. Teure SORT-Operationen können einen eigentlich idealen Ausführungsplan schnell zunichte machen. Mit einer geeigneten Indexierung kann man solche Engpässe jedoch vermeiden. Der nachfolgende Artikel zeigt exemplarisch, wie durch geeignete Indexierung eine SORT-Operation vollständig aus dem Ausführungsplan eliminiert werden kann.

Testumgebung

Für die Tests wird eine Relation [dbo].[Participants] verwendet, die ca. 150.000 Datensätze enthält und die folgende Struktur besitzt:

 1: CREATE TABLE dbo.Participants

 2: (

 3:     Participant_Id  int IDENTITY(1,1) NOT NULL,

 4:     Event_Id        int         NOT NULL,

 5:     Event_Name      char(64)    NOT NULL,

 6:     Event_Date      datetime    NOT NULL,

 7:     Opening         char(10)    NULL,

 8:     FirstName       char(80)    NULL,

 9:     LastName        char(80)    NULL,

 11:    Confirmed       bit         NOT NULL,

 13:    NoShow          bit         NOT NULL

 14: );

 15: GO

 16:  

 17: CREATE UNIQUE CLUSTERED INDEX ix_Participants_Id ON dbo.Participants (Participant_Id);

 18: GO

Die Relation besitzt einen Clustered Index mit [Participant_Id] als Clustered Key. Als Optimierungsgrundlage dient die folgende Abfrage:

 1: SELECT  Event_Name,

 2:         Event_Date,

 3:         LastName,

 4:         FirstName,

 5:         Opening,

 6:         Invited,

 7:         Confirmed,

 8:         Cancelled,

 9:         NoShow

 10: FROM    dbo.Participants

 11: ORDER BY

 12:         Event_Date,

 13:         Event_Name,

 14:         LastName,

 15:         FirstName;

Führt man die Abfrage aus, ergibt sich basierend auf der vorhandenen Struktur der folgende Ausführungsplan:

Parallelisierung ist für die Datenmenge in Ordnung und auch der Hinweis im SORT-Operator kann für die Problemstellung vernachlässigt werden. Auf Grund der hohen Datenmenge wird als Hinweis ausgegeben, dass die Sortierung in TEMPDB verlagert wurde. Entscheidend für die Problemanalyse ist, dass der SORT-Operator 36% der gesamten Ausführungszeit für sich beansprucht.

Verhinderung von SORT-Operationen

Verhindern kann man teure SORT-Operationen, indem man Microsoft SQL Server einen Index zur Verfügung stellt, der alle Attribute, die für ein ORDER BY verwendet werden, in einem Index bindet. Hierbei ist auf jeden Fall darauf zu achten, dass die Attribute in der Reihenfolge im Index vorhanden sind, die auch die Sortierung der Ausgabe beeinflussen. Gleichwohl ist auch darauf zu achten, dass alle Ausgabeattribute ebenfalls Bestandteil des Index sind, indem sie mit INCLUDE zum Index hinzu gefügt werden. Damit werden teure KeyLookups verhindert! Für eine Optimierung des obigen Verhaltens wird ein neuer Index mit der folgenden Struktur angelegt:

 1: CREATE INDEX ix_Participants_OrderedList ON dbo.Participants

 2: (

 3:     Event_Date,

 4:     Event_Name,

 5:     LastName,

 6:     FirstName

 7: ) INCLUDE (Opening, Invited, Confirmed, Cancelled, NoShow);

Der Index enthält als Indexattribute die Attribute, nach denen die Daten mit ORDER BY ausgegeben werden sollen. Sie werden in der gleichen Reihenfolge im Index verwendet, wie es die Reihenfolge für die SORT-Operation später benötigt. Gleichwohl wurden die anderen Attribute, die durch die Abfrage ausgegeben werden sollen mit INCLUDE dem Index hinzugefügt. Daraus ergibt sich das folgende Bild für eine Indexseite des erstellten Index.

Der rot umfasste Bereich markiert die Indexattribute; der blau markierte Bereich repräsentiert den Clustered Key des Clustered Index . Der grün markierte Bereich (Ausschnitt) zeigt die Attribute, die durch INCLUDE zusätzlich zu den eigentlichen Indexattributen gespeichert werden.

Führt man die Abfrage erneut aus, zeigt der Ausführungsplan ein vollständig anderes Verhalten, da nun alle Informationen aus dem Index gelesen werden können. Eine explizite SORT-Operation ist nicht mehr notwendig, da die Daten bereits sortiert im Index vorhanden sind – ein “ordinärer” Index-Scan verbleibt als einzige Operation.

Ein direkter Vergleich zwischen dem Clustered Index Scan und dem – nun ohne SORT-Operator – Scan des neu erstellten Index zeigt, dass eine deutliche Performancesteigerung eingetreten ist:

 1: -- SELECT und ORDER BY mit Clustered Index

 2: SELECT  Event_Name,

 3:         Event_Date,

 4:         LastName,

 5:         FirstName,

 6:         Opening,

 7:         Invited,

 8:         Confirmed,

 9:         Cancelled,

 10:        NoShow

 11: FROM    dbo.Participants WITH (INDEX (ix_Participants_Id))

 12: ORDER BY

 13:         Event_Date,

 14:         Event_Name,

 15:         LastName,

 16:         FirstName;

 17:  

 18: -- SELECT und ORDER BY mit neuem Index

 19: SELECT  Event_Name,

 20:         Event_Date,

 21:         LastName,

 22:         FirstName,

 23:         Opening,

 24:         Invited,

 25:         Confirmed,

 26:         Cancelled,

 27:         NoShow

 28: FROM    dbo.Participants

 29: ORDER BY

 30:         Event_Date,

 31:         Event_Name,

 32:         LastName,

 33:         FirstName;

Auch die I/O Statistiken belegen, dass SORT eine teure Operation ist, die die Ausführung einer Abfrage schnell negativ beeinflussen kann.

Muss bei Verwendung des optimierten Index die Datenmenge nur einmal vollständig durchlaufen werden, waren bei Verwendung des Clustered Index insgesamt 9 Scans erforderlich, die natürlich mehr I/O erzeugen.

Fazit

In manchen Situationen kann es Sinn machen, explizit für ORDER BY Operationen einen Index anzulegen. Diese Optimierungsmöglichkeit sollte man jedoch mit Bedacht wählen. Wechseln zum Beispiel die ORDER BY Attribute ständig oder die Reihenfolge der Attribute wird immer wieder geändert, kann der Index nicht mehr “einfach” von oben nach unten gelesen werden sondern es müssen trotz Indexierung erneut SORT-Operationen durchgeführt werden. Bevor man den Schritt zu einer Indexierung wagt, sollte man sorgfältig die Ausführungspläne der Datenbank analysieren. Stellt sich heraus, dass – wie in diesem Fall – eine Abfrage sehr häufig verwendet wird, sollte der Schritt zu einer entsprechenden Index in Betracht gezogen werden und der zusätzlich benötigte Speicher in der Datenbank als auch der höhere Verwaltungsaufwand (ist ein weitere Index) bei DML-Operationen in Kauf genommen werden!

Herzlichen Dank fürs Lesen!