Im vorherigen Artikel “UPDATE in HEAP = DELETE mit nachfolgendem INSERT?“ habe ich auf Grund eines interessanten Threads in den MSDN-Foren belegt, dass ein UPDATE-Befehl nicht mit einem DELETE und anschließendem INSERT zu vergleichen ist. Beide Verfahren unterscheiden sich massiv in ihrem Ausführungs- und Ressourcenverhalten. Der aktuelle Artikel beleuchtet das gleiche Verfahren für einen Clustered Index, der in seiner Struktur anders aufgebaut ist als ein HEAP. 

Sofern eine Relation einen Clustered Index besitzt, repräsentiert dieser Clustered Index die Relation, die – anders als ein Heap – die logische Sortierung der Daten nach dem Clustered Key erzwingt. Es gilt nun, zu untersuchen, wie sich ein Clustered Index bei einem UPDATE verhält. Auch hier soll wieder die Beispielrelation verwendet werden, wie aus dem vorherigen Artikel – lediglich durch einen Clustered Index repräsentiert.

Beispiel 1: Update in einem Clustered Index (feste Satzlänge)

CREATE TABLE dbo.tbl_demo
(
    Id    char(2)   NOT NULL,
    col1  char(200) NOT NULL,

    CONSTRAINT pk_tbl_demo_Id PRIMARY KEY CLUSTERED (Id)
);
GO

-- Eintragen von Datensätzen
SET NOCOUNT ON GO

DECLARE @i int = 65;
WHILE @i <= 90
BEGIN
    INSERT INTO dbo.tbl_demo (Id, col1)
    SELECT CHAR(@i), 'Das ist Buchstabe: ' + CHAR(@i);

    SET @i += 1
END

Nachdem die Datensätze eingetragen sind, wird wieder die physikalische Ablage überprüft. Den Aufbau der Abfrage wird im zuvor erwähnten Artikel “UPDATE IN HEAP…” beschrieben.

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS Location, * FROM dbo.tbl_demo;

In der Abbildung sind bereits zwei Datensätze markiert, die für die Beispiele verwendet werden. Zum einen ist es der Datensatz [Id] = ‘B’, der sich in Slot 1 befindet und zum anderen der Datensatz [Id] = ‘J’, der sich in Slot 9 befindet. Da die Anzahl der Datensätze in Verbindung mit der Datensatzlänge sehr gering ist, passen ALLE Datensätze auf eine Datenseite!

Datensatz löschen und anschließend wieder eintragen (DELETE –> INSERT)

Wie im vorherigen Artikel wird zunächst ein separates DELETE mit einem anschließenden INSERT mit dem Datensatz [Id] = ‘J’ durchgeführt um ein UPDATE gemäß der Fragestellung des Artikels zu simulieren. Unmittelbar im Anschluss wird erneut die Position des Datensatzes überprüft, um festzustellen, welche Auswirkungen die Transaktion auf den neuen Datensatz und seinen Speicherplatz hat.

BEGIN TRANSACTION DeleteInsert
    DELETE dbo.tbl_demo WHERE Id = 'J';
    INSERT INTO dbo.tbl_demo (Id, col1)
    VALUES ('J', 'Das ist nur ein Test');
COMMIT TRANSACTION

Zunächst die Prüfung der Position des Datensatzes nach dem Löschen und dem unmittelbaren Einfügen mit einem neuen Wert für [col1]!

Interessantes Ergebnis! Die Position hat sich nicht verändert. Im Gegensatz zu einem HEAP scheint also bei einem Clustered Index ein vollständig anderer Mechanismus zu wirken. Das bestätigt auch das Transaktionsprotokoll für die ausgeführte Aktion:

SELECT [Transaction ID],
       [Current LSN],
       Operation,
       Context,
       AllocUnitName,
       [Slot ID],
       [Lock Information]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction ID] IN
       (
          SELECT DISTINCT [Transaction ID]
          FROM sys.fn_dblog(NULL, NULL)
          WHERE [Transaction Name] = 'DeleteInsert'
       )
ORDER BY
        [Transaction ID],
        [Current LSN];

Wird ein Datensatz aus einer Relation gelöscht, so wird er nicht unmittelbar gelöscht, um möglichst schnell die gesperrten Ressourcen wieder frei zu geben. Statt dessen wird der Datensatz auf der Datenseite “gekennzeichnet”. Wer mehr Informationen zu “Ghosted Records” erhalten möchte, dem sei der Artikel “Inside the storage engine: Ghost Cleanup in depth” von Paul S. Randal empfohlen!

Da ein Datensatz mit einem identischen Clustered Key erneut in die Relation eingetragen wird, wird das Kennzeichen wieder entfernt und der neue Datensatz an der alten Position eingetragen – das ist ohne Probleme möglich, da jeder Datensatz in der Relation eine feste Satzlänge besitzt.

Schauen wir die obige Transaktion noch einmal in “Zeitlupe” an und werfen zwischen jeder Aktion einen Blick auf die Datenseite und die Veränderung der Informationen (Hinweis: Die Datenseite 2466 kann auf Ihren Systemen natürlich abweichen!).

1. DBCC TRACEON (3604);
2. BEGIN TRANSACTION DeleteInsert
      -- Vor dem Löschvorgang
3.    DBCC PAGE ('db_demo', 1, 2466, 2) WITH TABLERESULTS;
4.    DELETE dbo.tbl_demo WHERE Id = 'J';

      -- Nach dem Löschvorgang
5.    DBCC PAGE ('db_demo', 1, 2466, 2) WITH TABLERESULTS;
6.    DBCC PAGE ('db_demo', 1, 2466, 3) WITH TABLERESULTS;
7.    INSERT INTO dbo.tbl_demo (Id, col1)
8.    VALUES ('J', 'Das ist nur ein Test');   

      -- Nach dem Einfügen des neuen Datensatzes
9.    DBCC PAGE ('db_demo', 1, 2466, 2) WITH TABLERESULTS;
10.   DBCC PAGE ('db_demo', 1, 2466, 3) WITH TABLERESULTS;
COMMIT TRANSACTION

Los geht es zunächst mit der Datenseite, bevor der Datensatz gelöscht wird. Da für den Einstieg die Daten selbst irrelevant sind, genügt ein HEX-Dump der Datenseite (der am Ende die Offsets der einzelnen Slot(s) anzeigt (Zeile 3).

Der Datensatz, der gelöscht – und anschließend wieder eingefügt – wird, belegt den Slot 9 ([Id] = ‘J’) bei Offset 1977. Im nächsten Schritt wird der Datensatz [Id] = ‘J’ gelöscht und ein erneuter Blick auf die Belegung des Slot(s) (Zeile 5) zeigt eine erstaunliche Besonderheit:

Die Datenseite hat sich überhaupt nicht verändert! Obwohl der Datensatz gelöscht ist, wird der Platz, der durch den Slot belegt wurde, nicht als “freigegeben” gekennzeichnet. Aufschluss über das seltsame Verhalten gibt ein detaillierter Blick auf die Datenseite selbst (Zeile 6):

Der Pageheader zeigt an, dass ein es einen “GHOST_DATA_RECORD” auf der Datenseite gibt. Schaut man sich den betroffenen Slot 9 an, sieht man, dass der Datensatz noch vorhanden ist; der Inhalt wurde nicht gelöscht, jedoch handelt es sich nicht mehr um einen “PRIMARY_RECORD” – der Datensatz ist “zum Löschen” freigegeben!

Aus Optimierungsgründen löscht Microsoft SQL Server bei einem DELETE den Datenspeicher nicht sofort, weil es sich dabei um ressourcenintensive Operationen handelt. Für Microsoft SQL Server ist es deutlich effizienter, den betroffenen Datensatz als “ghosted” zu kennzeichnen, als den Inhalt während des Löschvorgangs zu überschreiben. Ein Hintergrundprozess übernimmt das Löschen der Daten “aus dem Verborgenen”. Wer tiefer in die Arbeitsweise von Microsoft SQL Server beim Löschen von “ghosted records” einsteigen möchte, dem sei der Artikel “Inside the storage engine: Ghost cleanup in depth” von Paul S. Randal empfohlen.

Im nächsten Schritt wird der Datensatz wieder eingefügt. Interessant ist erneut das Offset, um zu sehen, ob der bestehende Datensatz “überschrieben” wurde.

Man kann bei der Untersuchung der Datenseite sofort erkennen, dass der bereits allokierte Speicherplatz nicht mehr verwendet wird, sondern Microsoft SQL Server das Offset für Slot 9 an das freie Ende der Datenseite verschiebt und den Datensatz am Ende der “existenten” Daten anfügt. Hier hätte ich zwar erwartet, dass der bereits allokierte Speicher auf Grund der festen Satzlänge wiederverwendet wird; jedoch aus Sicht der Verwendung / Sperrung von Ressourcen auch hier wieder “logisch”. Es ist vermutlich für Microsoft SQL Server deutlich schneller, freien Platz zu allokieren als bestehende Allokationen wieder frei zu geben um sie anschließend zu überschreiben.

Es bleibt festzuhalten, dass der Slot – entgegen der Verhaltensweise in einem HEAP – beibehalten wird. Auch dieses Verhalten erschließt sich aus der Intention des Clustered Index. Die Daten werden logisch – gesteuert durch den Clustered Key - einsortiert – die Daten werden also von Slot 0 – Slot x gelesen. Dass dabei der Offset der Datensätze verschoben wird, kann vernachlässigt werden. Das Slot 9 “wiederbelebt” wurde, kann durch eine detaillierte Sicht auf die Datenseite belegt werden – der Datensatz wird wieder als “PRIMARY_RECORD” gekennzeichnet.

Aktualisierung eines Datensatzes (UPDATE)

Nachdem geklärt wurde, wie ein DELETE mit einem anschließenden INSERT aus Sicht des Transaktionsprotokolls aussieht, wird im nächsten Beispiel ein Update eines bestehenden Datensatzes durchgeführt. Dazu wird die Tabelle noch einmal komplett neu aufgebaut und anschließend wird der Datensatz [Id] = ‘B’ aktualisiert:

1. DBCC TRACEON (3604);
2. DBCC PAGE (2, 5, 14, 2) WITH TABLERESULTS;
3. BEGIN TRANSACTION updaterecord
4.     UPDATE dbo.tbl_demo
5.     SET    col1 = 'Das ist ein neuer Text'
6.     WHERE  Id = 'B';
7. COMMIT TRANSACTION
8. DBCC PAGE (2, 5, 14, 2) WITH TABLERESULTS;

Zunächst wird eine Ausgabe der Position des Datensatzes auf der Datenseite ausgegeben (Zeile 2). Um später die Transaktion selbst zu untersuchen, wird die Aktualisierung des Datensatzes in eine benannte Transaktion eingeschlossen (Zeile 3).

Die obige Abbildung zeigt die Position des Datensatzes [Id] = ‘B’ in Slot 1 an der Position 305 VOR der Aktualisierung. Nach der Aktualisierung des Datensatzes wird die Seite erneut ausgelesen (Zeile 8) und das Ergebnis sieht wie folgt aus:

Weder der Slot noch die Position des Datensatzes hat sich verändert! Das Verhalten eines Datensatzes in einem Clustered Index bei einer Aktualisierung ist weicht insofern von einem DELETE –> INSERT ab, als das der bereits allokierte Speicher wiederverwendet wird. Jedoch werden keine zwei Transaktionsschritte durchgeführt sondern der allokierte Datenbereich wird innerhalb eines Transaktionsschritts überschrieben, wie die nachfolgende Abbildung zeigt:

SELECT Operation,
       Context,
       AllocUnitName,
       [Slot ID],
       [RowLog Contents 0],
       [RowLog Contents 1]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction ID] IN
       (
          SELECT DISTINCT [Transaction ID]
          FROM sys.fn_dblog(NULL, NULL)
          WHERE [Transaction Name] = 'UpdateRecord'
       )
ORDER BY
        [Transaction ID],
        [Current LSN];

Das Transaktionslog zeigt, dass ein LOP_MODIFY_ROW auf dem Clustered Index in Slot 1 durchgeführt wurde. Zusätzlich habe ich noch die beiden Attribute [RowLog Contents 0] und [RowLog Contents 1] hinzugefügt, weil noch eine ECHTE Besonderheit bei einer Aktualisierung zu erwähnen ist (Leider kann ich die letztgenannten Attribute in diesem Artikel nicht im Detail beschreiben; das würde den Rahmen dieses Artikels vollständig sprengen. Ich versichere aber, dass ich speziell zu fn_dblog in Verbindung zu DML einen eigenen – SEHR AUSFÜHRLICHEN – Artikel schreiben werde!).

Das Attribut [RowLog Contents 0] beinhaltet den vorherigen Wert während [RowLog Contents 1] den neuen Wert beinhaltet. Bei den Eintragungen handelt es sich um Binärwerte, die aber mit einem einfachen Trick ausgelesen werden können (Das nachfolgende Verfahren geht aber nur bei Aktualisierungen in EINEM Attribut!)

Als Beispiel soll der Inhalt von [RowLog Contents 0] verwendet werden. Der nachfolgende Code speichert jeden binären Wert explizit in einer Zeile einer Tabellenvariablen

DECLARE @t TABLE
(
    Position    int        NOT NULL    IDENTITY (1, 1),
    Charakter   char(1)    NULL,
    BinaryValue AS CONVERT(varbinary(2), Charakter)
);

DECLARE @Content varbinary(max) = 0x4275636873746162653A20204220

DECLARE @i int = 1
WHILE @i <= DATALENGTH(@content)
BEGIN
    INSERT INTO @t
    SELECT CAST(SUBSTRING(@content, @i, 1) AS char(1))
    SET    @i += 1
END

SELECT * FROM @t;
GO

Das Ergebnis des obigen Codes mag etwas überraschen, wenn man sich den Befehl für die Aktualisierung noch einmal anschaut. Obwohl der Ausdruck “Das ist Buchstabe: B” ersetzt werden soll, wird effektiv nur folgender Eintrag “ersetzt”:

Ein erneuter Blick in das Transaktionsprotokoll zeigt, dass eine Änderung erst ab Offset 14 beginnt. Der Eintrag “Das ist “ wird durch die Aktualisierung nicht geändert; ein Indiz für die Effektivität von Microsoft SQL Server bei der Verwendung von Ressourcen.

Beispiel 2: Update in einem Clustered Index (variable Satzlänge)

Wie verhält sich ein Clustered Index bei Datensätzen mit variabler Datenlänge. Um dieses Verhalten zu untersuchen, wird die Relation [dbo].[tbl_demo] mit einer neuen Struktur aufgebaut und erneut mit 26 Datensätzen gefüllt:

CREATE TABLE dbo.tbl_demo
(
    Id    char(2)   NOT NULL,
    col1  varchar(8000) NOT NULL,

    CONSTRAINT pk_tbl_demo_Id PRIMARY KEY CLUSTERED (Id)
);
GO

-- Eintragen von Datensätzen
SET NOCOUNT ON

DECLARE @i int = 65;
WHILE @i <= 90
BEGIN
    INSERT INTO dbo.tbl_demo (Id, col1)
    SELECT CHAR(@i), 'Das ist Buchstabe: ' + CHAR(@i);

    SET @i += 1
END

Zunächst wird der Datensatz [Id] = ‘B’ aktualisiert mit einem Eintrag, der die gleiche Länge besitzt, wie der ursprüngliche Eintrag. Anschließend wird der Datensatz [Id]  = ‘D’ aktualisiert und mit einem Eintrag versehen, der 200 Zeichen umfasst. Zu guter Letzt wird der Datensatz [Id] = ‘F’ aktualisiert. Dabei wird der Eintrag in [Col1] auf 7.700 Zeichen erweitert.

Auch hier wird erneut eine benannte Transaktion verwendet, um nur die Einträge dieser Transaktion im Anschluss zu filtern.

SELECT Operation,
       Context,
       AllocUnitName,
       [Slot ID],
       [Offset in Row],
       [RowLog Contents 0],
       [RowLog Contents 1]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction ID] IN
       (
          SELECT DISTINCT [Transaction ID]
          FROM sys.fn_dblog(NULL, NULL)
          WHERE [Transaction Name] = 'UpdateRecord'
       )
ORDER BY
       [Transaction ID],
       [Current LSN];

Die Aktualisierung der drei Datensätze ist erfolgreich durchgelaufen. Interessant ist hierbei die Aktualisierung von [Id] = ‘F’! Ursprünglich befindet sich dieser Datensatz in Slot 5 auf der Datenseite; das Transaktionsprotokoll jedoch verwendet einen Slot 0 für die Modifikationen. Aufschluss über dieses Verhalten gibt ein Blick auf die Datenseiten vor und nach der Aktualisierung.

Die obige Abbildung zeigt, dass sich der Datensatz [Id] = ‘F’ nicht mehr auf der Datenseite 3:13:5 befindet sondern auf 4:13:0 verschoben wurde. Auch die nachfolgenden Datensätze [Id] = ‘G’, ‘'H’, ... wurden auf andere Datenseiten verschoben. Die beiden anderen modifizierten Datensätze sind auf der ursprünglichen Datenseite verblieben. Was genau passiert ist, lässt sich mit einem einzigen Begriff beschreiben. “Page Split”. Was ein Page Split ist, und was technisch im Hintergrund passiert, habe ich in dem Artikel “Fillfactor – Vor- und Nachteile” beschrieben. Da das letzte Update die Daten nicht mehr vollständig auf der aktuellen Datenseite speichern konnte, mussten die Daten auf zwei Datenseiten aufgeteilt werden. Ein Blick auf die Belegung der Slot(s) vor und nach der Aktualisierung zeigen die Auswirkungen:

Die obige Abbildung zeigt die Belegung der Slot(s) vor der Aktualisierung der Datensätze. Slot 01 beinhaltet [Id] = ‘B’; Slot 03 [Id] = ‘D’ und Slot 05 beinhaltet [Id] = ‘'F’. Die nächste Abbildung zeigt die gleiche Datenseite nach der Aktualisierung.

Die Daten für Slot 01 konnten im gleichen Datenbereich gespeichert werden, da sich die Gesamtlänge des Dateninhalts nicht verändert hat. Die Daten aus Slot 03 (200 Zeichen) konnten nicht mehr im ursprünglichen Datenbereich abgespeichert werden. Microsoft SQL Server verschiebt dann nicht bestehende Daten sondern – sofern möglich – speichert den neuen Eintrag für den Slot in einem freien und durchgängigen Bereich auf der Datenseite. Passt jedoch der neue Eintrag – wie im Beispiel von Slot 05 – nicht mehr vollständig in den noch freien Bereich der Datenseite, muss Microsoft SQL Server in einem Clustered Index einen Page Split durchführen und der Inhalt der Datenseite wird auf zwei (oder mehrere) Datenseiten verteilt.

Fazit

Für Clustered Indizes trifft die Aussage zu, dass ein UPDATE kein separates DELETE mit einem anschließenden INSERT impliziert. Vielmehr ist erkennbar, dass Microsoft SQL Server bei einem Update so effektiv ist, dass es erkennt, wenn ein Eintrag nur partial geändert werden soll; das trifft übrigens auch für einen HEAP zu!

Die Beispiele haben gezeigt, dass die Verwendung von (n)varchar(x) eher nachteilig sein kann, wenn es um Datenaktualisierungen geht. Wird ein Attribut aktualisiert, dessen neuer Wert eine längere Zeichenkette beinhaltet als der vorherige Wert, müssen neue Datenbereiche auf der Datenseite allokiert werden; nur bei fester Zeichenlänge kann der vormals allokierte Speicher wiederverwendet werden.

Anders als in einem HEAP gibt es in einem Clustered Index keine “Forwarded records”; dennoch führen statt dessen “Page Splits” zu erhöhtem I/O bei Lesevorgängen. Passt ein Datensatz nicht mehr auf eine Datenseite, muss der betroffenen Datensatz und ALLE nachfolgenden Datensätze auf neue Datenseiten verschoben werden. Bei diesen Aktionen handelt es sich um sehr ressourcenintensive Transaktionen, die vollständig protokolliert werden.

Dennoch; es bleibt allgemein festzuhalten, dass ein UPDATE kein DELETE verbunden mit einem nachfolgenden INSERT beschreibt und Microsoft SQL Server eher sparsam mit den gegebenen Ressourcen umgeht.

Hinweis

Bezüglich fn_dblog() werde ich einen eigenen – sehr ausführlichen – Artikel schreiben. Insbesondere das “Lesen” von Daten aus dem Transaktionsprotokoll ist “eine Wissenschaft für sich”. Hierbei werde ich ausführlich alle DML-Aktionen aus Sicht des Transaktionsprotokolls beschreiben.

Herzlichen Dank fürs Lesen!