Heute habe ich mit einem sehr geschätzten Freund und Kollegen (Bernd Jungbluth) eine interessante Diskussion im Rahmen meines Vortrags zu Indexstrategien auf der SNEK II in Nürnberg geführt. Die Aufgaben-/Fragestellung war recht simpel. Es ging darum, ob ein Clustered Index auf einem Datumsattribut performanter sei als ein Clustered Index auf einem INT-Attribut und einem zusätzlichen Index auf dem besagten Datumsattribut. Allgemeine Nachteile eines Clustered Index auf einem Datumsattribut (Fragmentierung / Größe) sollen als Pro / Contra Argumente hier nicht beleuchtet werden.

Um einen direkten Vergleich zwischen den beiden Varianten durchführen zu können, wurden zwei Relationen mit identischer Struktur aber unterschiedlichen Indexstrategien erstellt. Die erste Relation besitzt einen Clustered Key auf dem INT-Datentypen und einen zusätzlichen nonclustered Index auf dem Attribut mit dem Datentypen [date].

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

    CONSTRAINT pk_tbl_Members_SId_SId PRIMARY KEY CLUSTERED (SId)   
)
GO

CREATE INDEX ix_tbl_Members_SId_MemberSince ON dbo.tbl_Members_SId (MemberSince) INCLUDE (FirstName, LastName);
GO

Um einen “fairen” Vergleich zu haben, ist zu beachten, dass der nonclustered Index auf dem Datumsattribut alle weiteren Attribute mittels INCLUDE ebenfalls auf den Indexseiten speichert. Die zweite Relation besitzt einen Clustered Index, der das Datumsattribut als Clustered Key besitzt. Da der Clustered Index immer die Relation selbst ist, wird kein zusätzlicher Index für diese Relation benötigt.

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

     CONSTRAINT pk_tbl_Members_Date_MemberSince PRIMARY KEY NONCLUSTERED (SId)
)
GO

CREATE CLUSTERED INDEX ix_tbl_Members_Date_MemberSince ON dbo.tbl_Members_Date (MemberSince)

Beide Relationen haben insgesamt ~41.000 Datensätze. In beiden Relationen befinden sich 1:1 identische Datensätze!

Der Unterschied im Datenvolumen für die Indexe erklärt sich dadurch, dass in der Relation [dbo].[tbl_Members_SId] ein zusätzlicher Index auf dem Datumsattribut implementiert wurde, der alle anderen Attribute auf den Indexpages speichern muss. Auf Basis der obigen Datenstruktur wurden nun Tests durchgeführt, die über verschiedene Datumsbereiche in beiden Relationen getestet wurden. Bevor jedoch das Ergebnis näher beleuchtet wird, müssen noch ein paar wichtige Rahmenbedingungen (Indexanalysen) im Vorfeld bekannt gegeben werden, die die nachfolgenden Ergebnisse erklären können.

Für die Tests werden aus den Datensätzen zwei Jahre betrachtet; es sind Daten aus dem Jahre 2000 und dem Jahr 2012. Um die Anzahl der Datensätze zu ermitteln wurde über eine der beiden Relationen (Beide beinhalten die exakt gleichen Daten!) folgende Abfrage ausgeführt:

SELECT  YEAR(MemberSince)    AS Jahr,
        COUNT (1)            AS Mitglieder
FROM    dbo.tbl_Members_Date
WHERE   YEAR(MemberSince) IN (2000, 2012)
GROUP BY
        YEAR(MemberSince)
ORDER BY
        YEAR(MemberSince) DESC

Weiterhin wurden vor den Tests auch die für die Abfragen relevanten Indexe genauer unter die Lupe genommen. Bei den zu untersuchenden Indexen handelt es sich für die Relation [dbo].[tbl_Members_Date] um den clustered Index [ix_tbl_Members_Date_MemberSince] und für die Relation [dbo].[tbl_Members_SId] um den non clustered Index [ix_tbl_Members_SId_MemberSince].

SELECT  OBJECT_NAME(st.object_id),
        i.name,
        index_type_desc,
        index_level,
        page_count,
        record_count
FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_Members_Date', 'U'), 1, DEFAULT, 'DETAILED') st
        ON (
             i.object_id = st.object_id AND
             i.index_id = st.index_id
           )

 SELECT  OBJECT_NAME(st.object_id),
        i.name,
        index_type_desc,
        index_level,
        page_count,
        record_count
FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_Members_SId', 'U'), 2, DEFAULT, 'DETAILED') st
        ON (
                i.object_id = st.object_id AND
                i.index_id = st.index_id
           )

Die Auswertung der Indexe zeigt – wie bereits weiter oben die Abbildung der Indexvolumina vermutet – eine fast identische Aufteilung der Daten. Die Anzahl der Pages im Leaf-Level des Clustered Index ist nahezu identisch mit der Anzahl der Pages im Index-Level des non clustered Index der Relation [dbo].[tbl_Menbers_SId]. Dieser Wert war zu erwarten, da ja die Informationen [FirstName] und [LastName] in den non clustered Index mit aufgenommen wurden. Sie sind zwar kein Bestandteil der Indexattribute selbst, wurden aber mittels INCLUDE in die Indexseiten mit aufgenommen, um ein identisches Verhalten zu simulieren, wie es der clustered Index macht.

Es wurden verschiedene Basisabfragen mit jeweils unterschiedlichen Zeiträumen (siehe oben) durchgeführt und das Ergebnis war nicht überraschend – KEINE Strategie war in einer der Abfragevarianten der Sieger. Die identischen Abfragen mit beiden Relationen gingen jeweils 1:1 (sowohl im direkten Vergleich des Ausführungsplans als auch im IO) aus. Stellvertretend für beide Datumseingrenzungen sollen hier die Ergebnisse der Abfragen für das Jahr 2012 gezeigt werden.

SELECT *
FROM   dbo.tbl_Members_Date
WHERE  MemberSince >= '20120101' AND
       MemberSince < '20130101'

SELECT *
FROM   dbo.tbl_Members_SId
WHERE  MemberSince >= '20120101' AND
       MemberSince < '20130101'

SELECT *
FROM   dbo.tbl_Members_Date
WHERE  MemberSince >= '20120101' AND
       MemberSince < '20130101'
ORDER BY
       LastName,
       FirstName;

SELECT *
FROM   dbo.tbl_Members_SId
WHERE  MemberSince >= '20120101' AND
       MemberSince < '20130101'
ORDER BY
       LastName,
       FirstName;

Der direkte Vergleich im Rahmen des IO und der Ausführungsstrategie geht unentschieden aus. Dies sollte in der Konsequenz bedeuten, dass beide Konzepte gleich gut sind? Leider nein – es gibt einen eklatanten Nachteil der Variante, in der das Datum den Clustered Index bildet. Was unterscheidet den Clustered Index auf das Datumsattribut in [dbo].[tbl_Members_Date] vom Clustered Index der Relation [dbo].[tbl_Members_SId] – ER IST NICHT UNIQUE!

Genau diese Tatsache gereicht dem clustered Index – marginal – zum Nachteil. Da er nicht eindeutig ist, muss SQL Server für jeden Clustered Key eine “interne” Eindeutigkeit schaffen. Dies geschieht über den sogenannten [UNIQUIFIER]. Die nachfolgenden Abbildungen zeigen eine Page aus dem Leaf-Level des Clustered Index auf das Datumsattribut (kein Unique Key) und auf das Attribut [SId] der zweiten Relation:

Die erste Abbildung zeigt eine Page aus dem Leaf-Level des Clustered Indexes der Relation [dbo].[tbl_Members_Date]. Da das Datum keine Eindeutigkeit besitzt, muss SQL Server für jeden Datensatz eine Eindeutigkeit künstlich erzeugen. Hierzu wird im Index für jeden Eintrag ein [UNIQUIFIER] mitgeführt. Für die obige Abbildung bedeutet dies, das der Eintrag im Slot 0 bereits der 28. Eintrag (Der UNIQUIFIER beginnt bei 0) ist; der nächste Eintrag für den 01.01.2000 ist dann der 29. Eintrag, usw..

Für den Clustered Index der Relation [dbo].[tbl_Members_SId] gilt das nicht. Hier ist der Clustered Key ein eindeutiger Wert und somit muss SQL Server selbst nicht mehr für die Eindeutigkeit sorgen. Insgesamt ist somit die Verwaltung und der Speicherverbrauch des Indexes besser als für einen non unique clustered index.

Fazit

Ich wurde immer wieder gefragt, welche Strategie ich für einen Clustered Index verfolge. Meine Standardantwort darauf war immer: “It depends”. Das obige Beispiel zeigt sehr anschaulich, dass es keinen abfragerelevanten Vorteil gibt, wenn man die Wahl zwischen zwei Lösungen hat. Jedoch gilt für mich bei der Wahl des Clustered Index immer die Bestimmung der Relation selbst. Nehmen wir das obige Beispiel als Grundlage, dann gilt die Überlegung zunächst der Fragen:

• Wie häufig kommt es vor, dass Datumsangaben erfasst werden, die ZWISCHEN bestehenden Datumsangaben in der Relation liegen
• Welche Kardinalität (Selektivität) besitzen die Datumswerte
  Je niedriger die Kardinalität eines Clustered Keys um so höher der “Verwaltungsaufwand” des SQL Server
• Ist es wichtig, dass Daten schnell und fortlaufend eingetragen werden “müssen”
• Hat die betroffene Relation noch weitere Indexe
  Jeder non clustered Index muss IMMER den Clustered Key (Referenz zur Datenseite) in den Indexpages mitführen

Die obigen Fragen beeinflussen meine Entscheidung nach der richtigen Wahl signifikant. Für das obige Beispiel würde ich immer die Wahl des Clustered Keys auf dem Attribut [SId] befürworten. Die Vorteile liegen eindeutig auf dieser Entscheidung: Ein Clustered Key mit einer geringen Datengröße und einer i. d. R. fortlaufenden Nummer (IDENTITY)

• Eindeutigkeit des Clustered Keys (Sehr hohe Kardinalität)
• Keine Speicherung von Clustered Key UND Uniquifier in den weiteren non clustered indexes und damit deutlich weniger IO bei der Verwendung

Es kann keine pauschale Antwort nach der Frage des richtigen Clustered Index geben – sie ist immer von vielen Faktoren abhängig, die nicht nur und ausschließlich von den Daten selbst gesteuert sind.

Vielen Dank für’s Lesen.
Das Timing ist perfekt, in wenigen Minuten fährt mein Zug in Frankfurt HBF ein.
Die SNEK II hat mir persönlich sehr gut gefallen – konnte ich doch endlich über “mein” Lieblingsthema INDEXING referieren…

Vielleicht bis zum nächsten Jahr auf der SNEK III, auf der ein “Deep Dive “ fortgesetzt wird.