Die Verwendung von Indexen in Datenbanksystemen stellt einen wichtigen Schritt dar, um eine Datenbank performant zu gestalten. Die Wahl eines geeigneten Schlüsselattributs für den Clustered Index stellt bereits die Weichen für die Performance und die Größe aller weiteren – non-clustered – Indexe. Neben einem geeigneten Datentypen spielt die Eindeutigkeit der Werte bei der Definition eines Clustered Index eine entscheidende Rolle, die über die Performance entscheiden kann. Der nachfolgende Artikel beschreibt im Detail, wie Microsoft SQL Server sicherstellt, dass Werte in einem Clustered Index eindeutig identifiziert werden können, obwohl der Index nicht “UNIQUE” ist.

Testumgebung

Für die Beispiele in diesem Artikel wird eine Tabelle mit Informationen zu Kunden und deren Kostenstellenzuordnungen verwendet. Die Tabelle hat folgenden Aufbau:

CREATE TABLE [dbo].[Companies]
(
    [Id]         int             NOT NULL IDENTITY(1,1),
    [Name]       nvarchar(128)   NOT NULL,
    [TaxNo]      varchar(24)     NOT NULL,
    [CostCenter] char(7)         NOT NULL,
    [UpdateBy]   varchar(20)     NOT NULL
);
GO

Die Auswahl eines geeigneten Attributs für einen „clustered Index” muss sorgfältig geplant sein, da diese Entscheidung Auswirkungen auf das gesamte Layout der Datenstruktur hat. Der Clustered Index kann – wie ein non clustered Index auch – wahlweise als eindeutiger Index oder als Index mit redundanten Schlüsselwerten definiert werden.

Kardinalität der Schlüsselattribute

Die Kardinalität eines Attributs bestimmt, ob der Schlüssel eines Index als UNIQUE definiert werden kann. Für das erste Beispiel soll ein Clustered Index auf dem Attribut [Name] angelegt werden. Mit der folgenden Abfrage wird aus den Beispieldaten ermittelt, wie häufig der Firmenname in der Beispieltabelle [dbo].[Companies] verwendet wird:

SELECT TOP 10
       [Name]            AS CompanyName,
       COUNT_BIG(Id)     AS Kardinalität
FROM   [dbo].[Companies]
GROUP BY
       [Name]
ORDER BY
       COUNT_BIG(*) DESC;

Das Ergebnis der Abfrage zeigt, dass Firmennamen in der Beispieldatenbank redundant vorkommen.

Auf Grund der Redundanzen im Firmennamen kann für das Attribut [Name] kein eindeutiger Clustered Index angewendet werden.

 

Ein Clustered Index ist – wie bereits oben erwähnt – nicht dadurch in seiner Anwendung begrenzt, dass er nicht eindeutig sein kann. Vielmehr reguliert ein Clustered Index die logische Sortierung von Datensätzen in einer Tabelle; respektive er repräsentiert die Tabelle selbst.

Clustered Index mit redundanten Schlüsselattributen

Um zu zeigen, wie Microsoft SQL Server redundante Schlüsselattribute in einem Index verwaltet, wird im ersten Beispiel das Attribut [Name] mit einem Clustered Index versehen. Auf Grund der redundanten Daten darf der Index nicht eindeutig sein.

CREATE CLUSTERED INDEX [cix_companies_name] ON [dbo].[Companies] ([Name]);

Obwohl der erstellte Clustered Index nicht eindeutig ist, muss Microsoft SQL Server sicherstellen, dass der Datensatz “eindeutig identifizierbar” in der Tabelle ist. Diese “Eindeutigkeit” ist um so wichtiger, als dass diese Eindeutigkeit als Referenz in jedem non-clustered Index der Tabelle gespeichert werden muss. Es stellt sich also die Frage, wie Microsoft SQL Server die Datensätze verwaltet, um die Eindeutigkeit eines Datensatzes zu gewährleisten. Hierzu muss man tiefer in die Database Engine von Microsoft SQL Server eindringen.

Microsoft SQL Server verwaltet Datensätze in Datenseiten. Eine Datenseite hat eine feste Größe von 8.192 Bytes und kann bis zu 8.060 Bytes vollständig für die Speicherung von Datensätzen verwenden. Um einen Blick auf eine Datenseite zu werfen, muss der – nicht dokumentierte – Befehl DBCC PAGE verwendet werden. Zuvor wird mit Hilfe der Funktion [sys].[fn_PhysLocCracker] ermittelt, auf welchen Datenseiten die Datensätze der Tabelle [dbo].[Companies] gespeichert wurden:

SELECT P.[file_id],
       P.[page_id],
       P.[slot_id],
       C.name
FROM   [dbo].[Companies] AS C
       CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS P;

Die Abbildung zeigt die ersten Datensätze der Beispieltabelle. Die ersten drei Spalten der Ergebnismenge verweisen auf die jeweilige Position jedes einzelnen Datensatzes der Tabelle. Der erste Datensatz befindet sich in Datei 1 der Datenbank auf Datenseite 146 in Slot 0. Der Wert des Indexschlüssels kommt in den Beispieldaten nur einmal vor. Das nächste Unternehmen wird auf der gleichen Datenseite gespeichert, kommt aber mehrmals in den Beispieldaten vor.

Mit Hilfe des nachfolgenden Befehls kann der Inhalt und die Struktur der Datenseite 146 im Management Studio von Microsoft SQL Server ausgegeben werden:

DBCC TRACEON (3604);
DBCC PAGE ('demo_db', 1, 146, 3) WITH TABLERESULTS;

Um detaillierte Informationen zu einer Datenseite zu erhalten, muss das Traceflag 3604 aktiviert werden. Durch die Aktivierung wird die Ausgabe von DBCC PAGE nicht in das Fehlerprotokoll von Microsoft SQL geleitet sondern clientseitig ausgegeben. Die Option TABLERESULTS wurde für die bessere Darstellung des nachfolgenden Ergebnisses gewählt!

Die Abbildung zeigt die Informationen des ersten Datensatzes, der auf der Seite gespeichert wurde. Man erkennt, dass Microsoft SQL Server neben den gespeicherten Daten eine weitere Spalte VOR den eigentlichen Daten verwaltet. Hierbei handelt es sich um das Systemattribut [UNIQUIFIER]. Hierbei handelt es sich um ein internes – ausschließlich für die Verwaltung der Eindeutigkeit genutztes – Attribut, dass nicht nach außen angezeigt wird.

Ein UNIQUIFIER wird von Microsoft SQL Server dann für einen Index verwendet, wenn der Index nicht als eindeutig definiert wurde. Mit Hilfe eines UNIQUIFIER gelingt Microsoft SQL Server die “interne” Eindeutigkeit eines Datensatzes. Ein genauer Blick auf die Abbildung zeigt, dass Microsoft SQL Server trotz “zusätzlicher” Informationen sehr sparsam mit dem zur Verfügung gestellten Platz einer Datenseite umgeht. Der jeweils ERSTE Datensatz eines Schlüsselattributs bekommt zwar den Wert 0 für den UNIQUIFIER zugeteilt; dieser Wert wird aber nicht physikalisch auf der Datenseite gespeichert. Die Speicherung eines UNIQUIFIER-Wertes benötigt den Datentypen INT. Somit müssen weitere 4 Bytes zum eigentlichen Datensatz hinzu addiert werden, die ebenfalls gespeichert werden müssen.

Werden Datensätze mit redundanten Indexschlüsseln gespeichert, wird ab dem zweiten Datensatz mit gleichem Indexschlüssel der UNIQUIFIER physikalisch auf der Datenseite gespeichert.

Die Abbildung zeigt zwei Kundendatensätze, deren Indexschlüssel ([Name]) redundant in der Tabelle vorkommt. Der erste Datensatz erhält den – internen – UNIQUIFIER-Wert 0 während der zweite Eintrag den Wert 1 erhält. Ist der erste Eintrag nur eine berechnete Anzeige muss für die Speicherung des zweiten UNIQUIFIER-Werts physikalisch Speicher auf der Datenseite reserviert werden. Der Datensatz ist insgesamt um 4 Bytes gewachsen! Diese zusätzlichen 4 Bytes für den UNIQUIFIER werden zwischen den Informationen über die Struktur der variablen Spalten einer Tabelle und den variablen Daten eines Datensatzes zusätzlich gespeichert. Obwohl es sich bei dem Datentypen INT um einen festen Datentypen handelt, wird er – in diesem Fall – wie ein variabler Datentyp behandelt.

Die Abbildung zeigt – rot markiert – das Offset für die Werte jeder einzelnen Spalte mit variabler Datenlänge (jeweils 2 Bytes). So beginnt der Name des Unternehmens bei Offset 28, die Steuernummer bei Offset 72, usw.). Die Abbildung zeigt keinen Eintrag für den UNIQUIFIER, da es sich bei dem Datensatz um den ersten Eintrag mit gleichem Wert im Indexschlüssel handelt.

Die zweite Abbildung zeigt den zweiten Datensatz mit identischem Schlüsselattribut. In diesem Fall muss Microsoft SQL Server den Wert für den UNIQUIFIER physikalisch speichern. Obwohl es sich um einen Datentypen mit fester Datenlänge handelt, wird er im Bereich der variablen Daten gespeichert. Durch die zusätzlichen 4 Bytes VOR den eigentlichen Daten des Datensatzes verschieben sich die Offsets. Der Name des Unternehmens beginnt nun bei Offset 32, die Steuernummer verschiebt sich ebenfalls um 4 Bytes und beginnt nun bei Offset 76 (4C)! Insgesamt wächst der Datensatz um 4 Bytes (siehe Record Size im Header).

Clustered Index mit eindeutigen Schlüsselattributen

Wenn die Schlüsselattribute eines Clustered Index eindeutig sind, benötigt Microsoft SQL Server keine zusätzlichen Informationen, um die – interne – Eindeutigkeit eines Datensatzes zu gewährleisten. In diesem Fall kann der Index selbst die Datenintegrität gewährleisten. Neben dem fehlenden Verwaltungsaufwand ändert sich auch die Länge/Größe eines Datensatzes nicht mehr.

Die in Beispiel 1 verwendete Tabelle wird im zweiten Beispiel mit einem eindeutigen Clustered Index auf dem Feld [Id] erstellt.

CREATE UNIQUE CLUSTERED INDEX [cix_companies_id] ON [dbo].[Companies](Id);

Nach der Erstellung des Index zeigt ein erneuter Blick auf die Datenseiten des Clustered Index, dass die vormals mit UNIQUIFIER gespeicherten Datensätze nun ohne diesen Zusatz auskommen.

Die Abbildung zeigt den ersten Beispieldatensatz mit der [Id] = 2 (blaue Kennzeichnung). Auffällig ist bei der Wahl eines eindeutigen Index, dass die Anzahl der Spalten von vormals 6 auf 5 Spalten reduziert wurde, die in der Tabelle selbst definiert wurden! Die grüne Markierung zeigt die Offsets der Spalten mit variablen Datentypen. Vergleicht man die Struktur des Datensatzes mit dem zweiten – identischen – Eintrag, erkennt man, dass (bis auf den Wert für [Id]) keine Änderungen in der Struktur des Datensatzes vorgenommen wurde.

Zusammenfassung

Nicht nur die Wahl der geeigneten Datentypen für einen Clustered Index entscheidet über mögliche Performanceeinbußen. Auch über die Eindeutigkeit eines Clustered Index sollte man sich bei der Erstellung Gedanken machen. Ist ein Clustered Index nicht eindeutig, muss Microsoft SQL Server selbst für die Eindeutigkeit sorgen und verwaltet diese Information mit Hilfe eines – internen – UNIQUIFIER-Attributs. Ein UNIQUIFIER belegt 4 Bytes zusätzlich im Datensatz und wird – trotz fester Datenlänge – wie ein variabler Datentyp behandelt.

Übrigens gilt die Verwendung eines UNIQUIFIER-Attributs ausschließlich für einen Clustered Index. Für non-clustered Indexe ist dieser Verwaltungsaufwand nicht erforderlich, da die Eindeutigkeit entweder durch den Clustered Key (+[UNIQUIFIER] oder die RID (Row Locator ID) eines Heap gewährleistet wird.

Herzlichen Dank fürs Lesen!

Referenzen

SQL Server Clustered Index	http://msdn.microsoft.com/de-de/library/ms190639.aspx
Gruppierte Indexstrukturen	http://msdn.microsoft.com/de-de/library/ms177443.aspx
Kardinalität (Datenbanken)	http://de.wikipedia.org/wiki/Kardinalit%C3%A4t_(Datenbanken)
DBCC IND	http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-IND.aspx
DBCC PAGE	http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-PAGE.aspx
TRACEFLAG 3604	http://blogs.msdn.com/b/askjay/archive/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements.aspx

Beispielskript

Für das obige Beispiel kann das Beispielskript aus dem Bereich [Downloads] heruntergeladen werden!