Freitag, 30. August 2013

Optimierung von ORDER BY durch richtige Indexierung

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,
  10:     Invited         bit         NOT NULL,
  11:     Confirmed       bit         NOT NULL,
  12:     Cancelled       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:


Execution_Plan_01


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.


DBCC_PAGE_01


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.


Execution_Plan_02


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;

Execution_Plan_03


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


IO_Messung_01


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!

Kommentare :

  1. mmmmhhhh Ich kenne die Email ;-) Danke nochmal...
    Habe es dann auch so gelöst.....

    mfg
    Alex

    P.S
    Wie immer, die Beiträge einfach genial!

    AntwortenLöschen
  2. Hallo Andreas,

    den Ball musste ich aufnehmen :)
    Nachdem ich Deine Mail gelesen habe, habe ich mir noch mal ein paar Gedanken gemacht und anschließend das Verfahren geblogged.

    Bis zu den nächsten FDBT

    AntwortenLöschen