Durch eine Frage im Microsoft Technet Forum habe ich bemerkt, dass sehr häuft die Vermutung geäußert wird, dass beim Löschen eines Clustered Index die Relation selbst vollständig neu aufgebaut wird. Diese Vermutung ist falsch und der nachfolgende Artikel zeigt, welche Operationen Microsoft SQL Server beim Löschen eines Clustered Index intern durchführt.

Problemstellung

In einer Relation mit mehr als 500 Millionen Datensätzen sollte ein Clustered Index gelöscht werden. Diese Operation wurde als sehr zeit- und ressourcenintensiv beschrieben. Aus dem Zeitverzug leitete man ab, dass Microsoft SQL Server beim Löschen eines Clustered Index die Relation neu aufbaut. Um das genaue – interne – Verfahren zu analysieren, dient das folgende Testszenario:

-- Creation of a relation for demonstration
IF OBJECT_ID('dbo.tbl_demo', 'U') IS NOT NULL
    DROP TABLE dbo.tbl_demo;
    GO

CREATE TABLE dbo.tbl_demo
(
    Id      int          NOT NULL    IDENTITY (1, 1),
    col1    char(189)    NOT NULL    DEFAULT ('just stuff'),
    col2    char(200)    NOT NULL    DEFAULT ('more stuff')
);

-- Filling the table with a few records
SET NOCOUNT ON
GO

INSERT INTO dbo.tbl_demo DEFAULT VALUES
GO 100000

-- create two indexes for demonstration
CREATE UNIQUE CLUSTERED INDEX tbl_demo_Id ON dbo.tbl_demo (Id);
CREATE INDEX tbl_demo_Col1 ON dbo.tbl_demo(Col1);
GO

Der obige Code erzeugt eine Relation mit dem Namen [dbo].[tbl_demo] und fügt 100.000 Datensätze ein. Anschließend werden sowohl ein clustered Index [tbl_demo_id] als auch ein non clustered Index [tbl_demo_col1] angelegt. Nachdem die 100.000 Datensätze eingetragen wurden, ergeben sich für die physikalischen Strukturen beider Indexe folgende Bilder:

Die interne Struktur des Clustered Index stellt sich wie folgt dar (Auszug aus den ersten Datenseiten):

Ebenfalls Aufschluss über den allokierten Datenspeicher geben die nachfolgenden Abfragen, die mit Hilfe von sys.fn_PhysLocFormatter Informationen über die Position eines Datensatze geben:

Die obere Ergebnismenge zeigt die Datensätze 1-7 des Clustered Index, die auf Datenseite 20.256 beginnen. Die Daten des non clustered Index [tbl_demo_col1] beginnen auf der Datenseite 25.632. Nun wird der Clustered Index gelöscht und aus der Relation wird automatisch ein HEAP. An diesem Punkt setzt die eigentliche Untersuchung an. Die Vermutung einiger Foristen war, dass Ursache für die lange Ausführungszeit der Neuaufbau der Relation selbst sowie möglicher vorhandener non clustered Indexe ist. Um die Transaktion später gezielt aus dem Transaktionsprotokoll zu filtern, wird eine benannte Transaktion für das Löschen des Clustered Index verwendet! Anschließend wird erneut die Information über den allokierten Datenspeicher in Verbindung mit den vorhandenen Daten ausgegeben. Ist die Annahme der Foristen korrekt, muss sich für jeden Datensatz im Clustered Index die allokierte Datenseite geändert haben, da ein REBUILD immer Daten aus den bereits allokierten Datenseiten in neue Datenseiten verschiebt!

Das Ergebnis für die – nun zu einem Heap gewandelten – Tabelle ist überraschend. Die allokierten Datenseiten haben sich NICHT verändert. Die Datensätze sind auch weiterhin auf den zuvor allokierten Datenseiten gespeichert; die Datenseiten des non clustered Index haben sich jedoch geändert – dieser Index wurde also neu aufgebaut! Das Transaktionsprotokoll zeigt auch deutlich, warum sich an den Datenzuordnungen für den Clustered Index nichts geändert hat:

Die Abbildung zeigt einen verkürzten Auszug des Inhalts des Transaktionsprotokolls für die einzelnen Operationen, die während des Löschens des Clustered Index protokolliert wurden. Insgesamt sind es weit über 150 Einzeloperationen, von denen einige für die technische Begründung wichtig sind. Man kann deutlich erkennen, dass Microsoft SQL Server nicht die Daten selbst verändert sondern die Datenseiten für die Speicherung der Metadaten eines Objekts manipuliert:

Die Funktionsweise jedes einzelnen Datenseiten-Typs würde den Rahmen dieses Artikels sprengen. Wer mehr über die Struktur einer Datenbank / Datenseite / Record erfahren möchte, dem sei z. B. der folgende Artikel von Paul S. Randal empfohlen:

“Inside the Storage Engine: GAM, SGAM, PFS and other allocation maps”. Alternativ zum World Wide Web sei das Buch “Microsoft SQL Server 2012 Internals” von Kalen Delany ans Herz gelegt – ein Buch, dass in keiner gut sortierten Bibliothek eines SQL Server Experten fehlen darf. Dort finden sich im Kapitel “Databases Under the Hood” sehr detaillierte Informationen zu den einzelnen Seitentypen!

Aus dem Transaktionsprotokoll wird ersichtlich, dass ausschließlich Datenseiten zur Speicherung von Metadaten geändert werden. Die Daten selbst werden nicht geändert. Da nur die Strukturen der Datenseiten untereinander geändert wurden (ein HEAP hat z. B. keinen Intermediate Level (B-Tree) sondern ist eine vollkommen flache Struktur), werden keine Bewegungen der Daten selbst durchgeführt – sie verbleiben auf den ursprünglichen Datenseiten.

Als Beispiel für die Manipulation von Metadaten schauen wir etwas genauer auf die Zeile 11 des Transaktionsprotokolls. Dort insbesondere auf die [Lock Information]. Man kann erkennen, dass eine exklusive Sperre in Datenbank 19 (das ist meine Beispieldatenbank) – File 1 – Page 178 – Slot 5 gesetzt wurde, um einen Datensatz zu modifizieren. Schaut man sich noch einmal die Abbildung der “internen” Struktur (siehe oben) an, stellt man fest, dass die Datenseite 178
als IAM_PAGE allokiert wurde. Eine IAM-Datenseite speichert Informationen über Extents, die von einem Index allokiert werden. Ein Blick in die Datenseite selbst zeigt, dass einzelne Datenseiten nicht allokiert sind:

Insgesamt sind folgende Bereiche “frei”, die mit den zuvor durch den B-Tree und Root-Level des Clustered Index allokierten Seiten abgeglichen wird:

Bei allen in der Tabelle genannten Datenseiten handelt es sich um die Root- und B-Tree-Datenseiten des Indexes. Es sind also tatsächlich keine Daten selbst gelöscht/verschoben worden sondern es wurden die Strukturen, die einen Index ausmachen, vollständig aufgehoben. Damit entspricht die Datenstruktur einer flachen Struktur mit einer Datenebene – ein HEAP!

Warum wurden die Datenseiten des non clustered Index neu organisiert?

Tatsächlich stellt sich – auf den ersten Blick – die Frage, warum ausgerechnet die Daten des non clustered Index neu organisiert wurden; dieser Index wurde doch durch die Anweisung, den Clustered Index zu löschen, nicht unmittelbar nicht betroffen. Das ein non clustered Index bei der Reorganisation einer Relation (HEAP / Clustered Index) direkt betroffen ist, hängt mit den eng verbundenen Strukturen zwischen Relation (Clustered Index / HEAP) und einem non clustered Index zusammen.

Jeder non clustered Index führt neben den eigenen Indexattributen IMMER den eindeutigen Schlüssel der Relation selbst mit. Bei einem Clustered Index ist dies immer der Clustered Key. Ist der Clustered Index nicht eindeutig, führt Microsoft SQL Server für die Eindeutigkeit des Datensatzes zusätzlich einen UNIQUIFIER mit. Dieser UNIQUIFIER in Verbindung mit dem Clustered Key sorgt für die Eindeutigkeit eines Datensatzes.

Besitzt ein Clustered Index einen Clustered Key als Referenzwert, kann ein HEAP nicht von solchen Strukturen profitieren. Ein HEAP definiert sich dadurch, dass es keine eindeutigen Kriterien für einen Datensatz gibt. In einem solchen Fall legt Microsoft SQL Server für die “interne” Eindeutigkeit einen eigenen “Schlüssel” – den sogenannten RID (RowLocator ID).

Nun versteht sich, warum der non clustered Index neu aufgebaut werden musste. Bevor der Clustered Index gelöscht wurde, wurde der Clustered Key ([Id]) der Relation als Pointer für den Zugriff auf die Daten verwendet wie die nachfolgende Abbildung zeigt:

Zu jedem Eintrag im non clustered Index wird der zugehörige Clustered Key als Pointer zu den eigentlichen Daten gespeichert. Durch dieses Verfahren kann Microsoft SQL Server alle erforderlichen Daten der Relation, die sich nicht auf den Indexseiten des non clustered Index befinden, unmittelbar mittels KeyLookup aus dem LEAF-Level des Clustered Index beziehen.

Der identische non clustered Index OHNE einen Clustered Index (HEAP) hat die folgende “Struktur”:

Man kann deutlich erkennen, dass nun neue Datenseiten allokiert wurden und die Länge eines Datensatzes verändert ist. Waren es mit dem Clustered Key (int) nur 4 Bytes, die pro Datensatz im Index gespeichert wurden, müssen nun 8 Bytes für den RID gespeichert werden.

Fazit

Das Löschen eines Clustered Index führt nicht dazu, dass die Relation selbst neu aufgebaut wird. Da beim Löschen eines Clustered Index ausschließlich die Strukturen geändert werden, ist eine Neuorganisation der Relation selbst nicht notwendig. Beim Löschen eines Clustered Index müssen aber ALLE non clustered Indexe vollständig neu aufgebaut werden, da kein Clustered Key mehr vorhanden ist, der eine Referenz / Pointer zu den Datenseiten selbst bildet. Statt eines Clustered Keys verwendet Microsoft SQL Server für einen HEAP eine eigene Methode für die Eindeutigkeit eines Datensatzes – den RowLocator (RID). Jeder non clustered Index benötigt diese neue “Referenz” und muss deswegen neu aufgebaut werden.

Herzlichen Dank fürs Lesen!