Sonntag, 14. April 2013

Clustered Index vs. NonClustered Index

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!

Dateninformationen

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

Datenvolumen 2000 2012

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
           )

Indexinformationen

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'

ExecutionPlan - #1

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;

ExecutionPlan - #2

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:

Indexpages - Leaflevel - NonUnique

Indexpages - Leaflevel - Unique

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.

Kommentare :

  1. Dieser Kommentar wurde vom Autor entfernt.

    AntwortenLöschen
  2. Hallo Uwe,

    vielen Dank für Deine hilfreichen und immer kurzweiligen Blogs.
    Zum Thema:
    Wir haben gerade einen akuten Fall von 2 Tabellen als 1:N-Beziehung: einmal "Jobs" (Parent) und "JobsItems" (Childs) - verknüpft über eine JobID.
    JobID ist ein uniqueidentifier, aber darum geht es mir nicht, sondern um die ChildTabelle "JobsItems".
    Diese enthält ein Datumswert "LastActivity". Und weil im Dashboard der Anwednung im sogenannten JobMonitor viele Bereichsabfragen auf LastActivity stattfinden (BETWEEN), hat sich der Entwickler gedacht: nehme ich mal als ClusteredIndex den LastActivity, der sei für Bereichsuchen sehr perfomant. Stimmt, solange keine Updates auf dem Feld stattfinden.
    Du kannst Dir sicherlich vorstellen, was das für einen Impact gab, wenn das LastActivity - von dessen ClusteredIndex natürlich noch der eine oder andere NonClusterdIndex abhängig war (BookmarkLookups)- aktualiasiert wurde. Und diese Aktualisierung findet eigentlich ständig statt, wenn ein Client seine aktuellen Loginfos zum MiddleTier-Server schickt, der die Daten auf dem SQL Server aktualisiert.

    Das Fatale: der Impact wurde mangels Testcases während der Qualitätskontrolle nie provoziert.
    Mal abgesehen davon, dass "LastActivty" nicht unique ist.
    Durch Umorganisieren der Indexe - LastActivity ist kein ClusteredIndex mehr - sind die Bereichsuchen jetzt ca. 15% langsamer und verbraten ca. 10% mehr LogicalReads, der Impact konnte allerdings stark gemindert werden.
    Auf der Column "LastAvtivity" soll es jetzt noch einmal 2 NonClustered-Indexe geben mit einmal 1 und enimal 2 included Columns (2 INTEGER und 1 UNIQUINDETIFIER), um die LocicalReads drastisch zu reduzieren.

    Jetzt meine Frage:
    Die Column "LastAvtivity" ist ja jetzt nicht mehr der ClusteredIndex, was den Impact provoziert hat.
    Wie ist deine Erfahrung beim Update von Spalten, auf denen ein NonClusterdIndex liegt?
    Wenn ich 2 NonClusteredIndex auf der Spalte "LastActivity" anlege, um beim Lesen die LogicalReads drastisch zu reduzieren, müssen diese (und deren Statistiken) beim Update von "LastAvtivity" neu gebaut werden.
    Der ClusterdIndex ist jetzt übrigens JobID, die wird nicht aktualisiert.

    Das Abfragen über Bereichssuchen findet ungefähr 3x häufiger statt als das Aktualisieren (Write)
    Jetzt gilt es abzuwägen. Was sind Deine Erfahrungen diesbezüglich dieser Thematik?

    Liebe Grüße aus Soest
    Robert

    AntwortenLöschen
  3. Teuer war das Update auf den ClusteredIndex natürlich vor allem, weil es mehrere abhängige NonClusteredIndex gab, die ständig aktualisiert werden mußten. Ähnliches gilt für referenziete FKs.

    AntwortenLöschen
  4. Lieber Robert,

    erst einmal herzlichen Dank für Deinen Kommentar. Es freut mich sehr, dass Dir meine Blogartikel gefallen; das freut umso mehr als dass man durch solche Kommentare ein wenig Anerkennung erhält :). Bitte entschuldige, dass ich erst jetzt die Zeit finde, zu Deinem Kommentar Stellung zu nehmen - aber es ist momentan schlicht und einfach zu viel zu tun.

    Das von Dir skizzierte Szenario leidet unter einigen Schwächen von denen Du bereits eine beschrieben hast. Sofern das Attribut [LastActivity] immer wieder geändert wird, ist es als Clustered Index eher ungeeignet. Ein anderes Problem jedoch scheint mir, dass wohl auf dem Fremdschlüssel-Attribut kein Index liegt.

    Sollten also JOINS zwischen [JOBS] und [JOBSITEMS] durchgeführt werden, würden sie wohl mit einem NESTED LOOP oder aber mit einem HASH JOIN (bei großer Datenmenge) durchgeführt werden.

    Grundsätzlich ist aus meiner Erfahrung bei einer GUID vom Einsatz eines Clustered Index abzusehen! Das ist jedoch sehr stark davon abhängig, wie die Workloads in Deiner Anwendung aufgebaut sind. Hast Du ein sehr schreiblastiges System (OLTP) ist eine GUID sehr gut um sogenannte Hotspots zu vermeiden, die zwangsläufig bei fortlaufenden Clustered Keys auftreten. Dieser Vorteil wiederum wird zunichte gemacht, da bei "random" Clustered Keys sehr häufig Page Splits vorkommen, um Datensätze in bereits vollen Datenseiten zu speichern - ein Teufelskreis :)

    Dieses Thema ist zu komplex um es als "Kommentar" zu posten - ich werde über die Weihnachtstage Dein Beispiel aber aufgreifen und die Vor- und Nachteile der GUID als Clustered Key beschreiben.

    Alternativ wäre es schön, wenn Du die Zeit finden könntest, die SQL Konferenz in Darmstadt zu besuchen. Ich würde mich sehr gerne dort noch etwas intensiver mit Dir über dieses Thema unterhalten. Weitere Informationen zur SQL Konferenz findest Du unter:

    http://www.sqlkonferenz.de

    Alternativ wäre auch ein Treffen in Siegburg möglich, wenn die PASS zum nächsten SQL Saturday in Deutschland ruft :)

    http://www.sqlsaturday.com

    Beste Grüße aus dem Rhein-Main-Gebiet, Uwe

    AntwortenLöschen