Während ich den vorherigen Artikel “Clustered Index vs. Non Clustered Index” geschrieben habe, habe ich ein paar interessante Beobachtungen gemacht, die es wert sind, etwas genauer unter die Lupe genommen zu werden. Vielmals höre ich aus Bemerkungen in Vorträgen oder Unterhaltungen mit Kollegen, wie wenig Beachtung bei der Indexierung der Vergleich des I/O bei der Umsetzung differenzierter Indexstrategien findet. Wenn ich mit einem Auftrag betraut werde, eine Abfrage zu optimieren, führe ich zunächst die Abfrage im Original aus und lege die daraus resultierenden I/O-Werte und den Ausführungsplan als "Baseline” fest. Anschließend beginne ich mit der Optimierung. Der nachfolgende Artikel soll deutlich machen, dass nicht immer nur der Ausführungsplan im Mittelpunkt stehen sollte sondern – und gerade – das I/O der Gratmesser für eine optimierte Indexstrategie ist.

Ausgangssituation

Gegeben ist zunächst ein HEAP ohne weitere Indexe mit der folgenden Struktur:

CREATE TABLE dbo.tbl_Members_Date
(
    SId         int         NOT NULL,
    FirstName   char(80)    NOT NULL,
    LastName    char(80)    NOT NULL,
    MemberSince date        NOT NULL
)

Diese Tabelle hat insgesamt 40.417 Datensätze mit einer festen Größe von 167 Bytes / Datensatz. Anschließend wurde die folgende Abfrage mit unterschiedlichen Indexstrategien auf diese Relation ausgeführt:

SELECT  YEAR(MemberSince)     AS MemberYear,
        COUNT_BIG(SId)        AS Members
FROM    dbo.tbl_Members_Date
GROUP BY
        YEAR(MemberSince)
ORDER BY
        COUNT_BIG(SId) DESC

Ausführung der Abfrage als HEAP

Die Relation besitzt aktuell noch keinen Index (HEAP) und die physikalische Gegebenheit der Relation (kann mittels sys.dm_db_index_physical_stats ermittelt werden) sieht wie folgt aus:

Die obige Abfrage ergab den folgenden Ausführungsplan und I/O:

Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0,...
tbl_Members_Date-Tabelle. Scananzahl 1, logische Lesevorgänge 883, ...

Wie aus dem Ausführungsplan deutlich erkennbar ist, handelt es sich um einen “Table Scan”.  Betrachtet man die vorherige Auswertung der physikalischen Struktur, kann man deutlich erkennen, dass jede Datenseite des Heaps gelesen werden musste (versteht sich von selbst – es gibt ja auch keine Einschränkungen).

Ausführung der Abfrage mit einem CLUSTERED INDEX auf [MemberSince]

Im nächsten Versuch wird ein Clustered Index erstellt, dessen Key das Attribut [MemberSince] ist. Anschließend wird die Abfrage erneut ausgeführt.

CREATE CLUSTERED INDEX cix_tbl_MemberSince_MemberSince ON dbo.tbl_Members_Date (MemberSince);

Das Ergebnis ist – zunächst – verblüffend. Statt besser zu werden, generiert diese Abfrage ein höheres IO!

Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, ...
tbl_Members_Date-Tabelle. Scananzahl 1, logische Lesevorgänge 903, ...

Es mussten 20 Pages mehr gelesen werden, um die gleiche Auswertung zu erhalten. Der Grund für dieses seltsame Verhalten ist in den unterschiedlichen Strukturen von HEAPS und CLUSTERED INDEXES zu finden. Ein HEAP besitzt eine sehr flache Struktur – nämlich einen Root-Level und anschließend unmittelbar die Leaf-Levels für die Speicherung der Daten. Ein Clustered Index hingegen hat eine deutlich komplexere Struktur, da bei zunehmendem Datenvolumen die Clustered Keys in B-Trees gespeichert werden. Die nachfolgende Abbildung zeigt den Unterschied sehr deutlich.

Hat der Heap – wie oben abgebildet – nur eine Ebene (Leaf-Level) muss der Clustered Index bereits 6 Seiten mehr für die Root-Page und eine Ebene (B-Tree) bereit halten. Die eigentlichen Daten wiederum werden erst im Level 0 (Leaf-Level) gespeichert. Hierbei werden 896 Seiten für diese Speicherung benötigt. Insgesamt sind es also 896 + 5 + 1 = 902 (+1 Page IAM) Seiten. Warum sind im Leaf-Level mehr Pages vorhanden als bei einem HEAP? Es werden doch identische Daten gespeichert!Die Erklärung hierfür ist relativ schnell gefunden, wenn man sich die Datenstrukturen der Leaf-Level einmal etwas genauer anschaut. Die nachfolgenden Abbildungen zeigen eine Page aus dem Leaf-Level eines HEAP und des CLUSTERED INDEX.

Data Page in HEAP

Data Page in CLUSTERED INDEX

Vollkommen transparent fügt SQL Server JEDEM Index, der nicht UNIQUE ist ein internes Attribut [UNIQUIFIER] hinzu, um einen Datensatz eindeutig zu kennzeichnen. Dieser [UNIQUIFIER] hat eine unangenehme Eigenschaft… – er benötigt Platz. Grundsätzlich wird der UNIQUIFIER immer dann mit einem Wert besetzt, wenn das Schlüsselattribut eines Index redundant ist. In einem HEAP werden solche [UNIQUIFIER] nicht verwendet! (Danke an Andreas Wolter, der mich auf diesen Fehler hingewiesen hat).

Bei dem anschließend gesetzten Clustered Index auf das Attribut [MemberSince] zeigt die obige Data Page bereits einen 17. ten redundanten Eintrag. Der UNIQUIFIER beginnt intern immer bei 0. hierbei sein ein wichtiger Hinweis darauf gestattet, dass ein UNIQUIFIER mit dem Wert 0 eine physikalische Länge von 0 hat – es wird kein INT für diesen Wert gespeichert. Hingegen ist jedoch zu erkennen, dass der UNIQUIFIER immer 4 Bytes belegt, wenn der Wert größer 0 ist. Das erklärt den unterschiedlichen Platzbedarf des CLUSTERED INDEX im Verhältnis zum HEAP.

Ausführung der Abfrage mit einem dedizierten Index auf das Attribut [SId]

Der dritte Test verwendet eine Abfragestrategie, bei der ein Clustered Index auf das Attribut [SId] angewendet wird. Da dieses Attribut eindeutig ist, wird ein UNIQUE Index verwendet. Des Weiteren wird ein Non Clustered Index auf das Attribut [MemberSince] gelegt. Durch diese Strategie wird für den Clustered Index die geforderte Eindeutigkeit gewährleistet, die die Speicherung eines UNIQUIFIERS vermeidet. Da jedoch MemberSince für die Abfrage benötigt wird, wird anschließend ein zweiter Non Clustered Index für dieses Attribut verwendet. Weitere Attribute müssen weder diesem Index als Schlüsselattribut hinzugefügt werden noch muss die SId (wird ja ebenfalls im der Abfrage benötigt) separat hinzugefügt werden, da ja der Clustered Key immer in jedem Non Clustered Index enthalten sein muss.

Nachdem die bestehenden Indexe vollständig gelöscht worden sind, können die zwei nachfolgenden Index angelegt werden:

CREATE UNIQUE CLUSTERED INDEX cix_tbl_MemberSince_SId ON dbo.tbl_Members_Date (SId);
CREATE INDEX ix_tbl_MemberSince_MemberSince ON dbo.tbl_Members_Date (MemberSince);

Ein Blick auf die Indexstatistiken zeigt, dass der Clustered Index nicht wesentlich weniger Volumen besitzt als der zuvor auf dem Attribut [MemberSince] verwendete Clustered Index.

Der Unterschied kommt zu Stande, da – wie bereits oben erwähnt – für die Datensätze kein UNIQUEIFIER mehr mit gespeichert werden muss. Ca. 10 Seiten für die obige Struktur mag erahnen, welches Datenvolumen eingespart werden kann, wenn es um wirklich große Datenmengen geht. Keine Behauptung ohne Beweise – ein Blick auf eine Datenseite zeigt den Unterschied deutlich.  Man kann auf der Abbildung deutlich erkennen, dass ein UNIQUIFIER auf der Datenseite fehlt; er wird ja auch nicht mehr benötigt, da per Definition für den Clustered Index definiert wurde, dass er eindeutig ist.

Interessant ist aber ein Blick auf den zweiten – für die Abfrage entscheidenden – Index, der ausschließlich das Attribut [MemberSince] abdeckt. Dieser Index ist – im Verhältnis zu den zuvor untersuchten Indexen – sehr klein.

Wow – dieser Index verwendet insgesamt nur 66 Pages. Das ist dem Umstand geschuldet, dass das Attribut des Index (MemberSince) lediglich 3 Bytes belegt. Hinzurechnen muss man noch den Clustered Key (4 Bytes)  + Overhead für die Speicherung der Daten (Slot Array, …). Dadurch, dass nun ca. 620 Informationen ([MemberSince], [SId]) auf eine Datenseite passen, können die 40.000 Datensätze bequem auf 65 Indexseiten gespeichert werden. Und genau das macht sich extrem bemerkbar in der Abfrage.

Das I/O ist erwartungsgemäß gemäß der Größe des Index recht klein:

Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, …
tbl_Members_Date-Tabelle. Scananzahl 1, logische Lesevorgänge 67, …

Insgesamt konnte so durch geschickte Indexierung die Performance  um über 90% gesteigert werden!

Verwendung einer INDEXED View

Die ganze oben aufgeführten Indexvarianten sind ein sehr guter Ansatz, das I/O signifikant zu verbessern. Die bester aller Varianten – unter der Voraussetzung der obigen Anforderungen – ist jedoch ungeschlagen die Verwendung einer INDEXED View. Mehr zu den Anforderungen an eine Indexed View finden Sie im Artikel “Verwendung von “Indexed views” zur Verbesserung der Performance”. Von daher hier nur kurz die Erstellung, Indexstruktur und Auswertung…

CREATE VIEW dbo.view_Member_Statistics
WITH SCHEMABINDING
AS
    SELECT  YEAR(MemberSince)      AS MemberYear,
            COUNT_BIG(*)           AS Members
    FROM    dbo.tbl_Members_Date
    GROUP BY
            YEAR(MemberSince) GO

CREATE UNIQUE CLUSTERED INDEX cix_view_Member_Statistics ON dbo.view_Member_Statistics (MemberYear);

Die Indexstatistiken sind beeindruckend.

Entsprechend gestaltet sich dann auch Ausführungsplan und I/O-Messung für die Ausführung der View:

view_Member_Statistics-Tabelle. Scananzahl 1, logische Lesevorgänge 2, …

Beeindruckend! Von ursprünglich 903 –> 883 –> 67 –> 2 I/O’s. Man kann sich vorstellen, wie glücklich ein Kunden sein wird, wenn er solche Zahlen präsentiert bekommt.

Fazit

Indexoptimierung muss immer von mehreren Seiten betrachtet werden. Es reicht nicht aus, den Ausführungsplan im Blick zu haben und zu versuchen, z. B. einen Index Scan durch einen Index Seek zu ersetzen oder einen Nested Loop durch einen Hash Join, … Indexoptimierung muss meines Erachtens zunächst das I/O im Auge behalten. Erst, wenn die Schwachstellen beim I/O eliminiert sind, sollte man sich um den Ausführungsplan kümmern. Diese Strategie ist meines Erachtens sogar elementar, da bei Reduktion des I/O Änderungen in der Ausführung einer Abfrage nicht auszuschließen sind.

Herzlichen Dank fürs Lesen