In einem recht interessanten Thread in den msdn-Foren von Microsoft ging es primär darum, die Gründe zu finden, warum ein UPDATE so lange dauert. Unter anderem wurde von – einem von mir sehr geschätzten Kollegen – Kalman Toth zu diesem Punkt erwähnt, dass eine UPDATE-Anweisung möglicherweise eine DELETE-Anweisung mit einer anschließenden INSERT-Anweisung sei. Das dies nicht der Fall ist, soll der nachfolgende Artikel detailliert belegen. Auf Grund der Komplexität dieses Themas werde ich diesem Thema zwei Artikel widmen. Der aktuelle Artikel beschäftigt sich mit dem UPDATE-Verhalten in einem HEAP.

Microsoft SQL Server versucht nach Möglichkeit immer, einen effizienten Weg zu finden, um Ressourcen möglichst schnell wieder freigeben zu können. Jede DML-Anweisung (INSERT, UPDATE und DELETE) benötigt exklusive Sperren auf die Ressourcen, die von der Anweisung betroffen sind. Somit würde meines Erachtens ein UPDATE in Verbindung mit einem DELETE und einem anschließenden INSERT Ressourcen nur unnötig lang binden. Weiß Microsoft SQL Server, dass eine Aktualisierung nur eine einmalige Bindung der Ressourcen bedeutet, sollte also ein UPDATE ein eigener Transaktionsprozess sein.

Beispiel 1: UPDATE in einem HEAP (feste Datensatzlänge)

Ein HEAP ist eine Relation ohne feste Ordnung da ein HEAP nicht über ein Schlüsselattribut verfügt, das die physikalische Ordnung der Daten beeinflusst. Für die nachfolgenden Beispiele reicht eine einfache Struktur aus:

CREATE TABLE dbo.tbl_demo
(
    Id    char(2)   NOT NULL,
    col1  char(200) NOT NULL
);
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 Relation mit den Daten des Alphabets gefüllt ist, können die Tests beginnen. Wichtig ist bei den nachfolgenden Untersuchungen, dass es sich um feste Datensatzlängen handelt. Jeder Datensatz hat eine feste Länge von 204 Bytes (ich lasse jetzt mal die “internen” Verwaltungsbytes außer Betracht!). Zunächst gilt es, einen Blick auf die physikalische Position jedes einzelnen Datensatzes zu werfen.

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

Die Informationen zu den einzelnen Datensätzen befinden sich in der Testdatenbank in Datei 1, Datenseite 2466 und – und das ist für die weiteren Untersuchungen relevant!) in fortlaufenden Slot(s). Alle 26 Datensätze passen auf eine Datenseite. Da die Daten sequentiell eingefügt wurden, stimmen die Slots mit den Offsets der Daten überein.

DBCC TRACEON(3604);
DBCC PAGE ('demo_db', 1, 2466, 2) WITH TABLERESULTS;

Die obige Abbildung zeigt die Offsets für jeden einzelnen Slot. Der Datensatz mit der [Id] = ‘A’ belegt Slot 0 und beginnt bei Offset 96, [Id] = ‘B’ beginnt bei Offset 319, usw..

Zunächst wird überprüft, wie sich ein DELETE mit einem anschließenden INSERT auf die Datenspeicherung auswirkt. Für das Beispiel wird der Datensatz mit der [ID] = ‘J’ zunächst gelöscht und anschließend mit einem anderen Wert für [col1] wieder eingetragen. Um nachzuvollziehen, welche Schritte Microsoft SQL Server durchführt, wird das komplette Beispiel in einer in sich geschlossene Transaktion gekapselt.

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

Ein Blick in das Transaktionsprotokoll zeigt, welche Aktionen von Microsoft SQL Server für die obigen DML-Befehle durchzuführen waren (Die nachfolgende Abfrage wird auch in den weiteren Beispielen für die Anzeige des Transaktionsprotokolls verwendet und der besseren Übersichtlichkeit wegen nicht wiederholt!):

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];

DBCC TRACEON(3604);
DBCC PAGE ('demo_db', 1, 2466, 2) WITH TABLERESULTS;

Mit dem nächsten Beispiel wird nun der Datensatz mit der [Id] = ‘B’ aktualisiert und anschließend das Transaktionsprotokoll untersucht.

BEGIN TRANSACTION updaterecord
    UPDATE dbo.tbl_demo
    SET    col1 = 'Das ist ein neuer Text'
    WHERE  Id = 'B'
COMMIT TRANSACTION

Man kann sehr deutlich erkennen, dass ein UPDATE nicht dazu führt, dass ein separates DELETE und ein anschließendes INSERT durchgeführt wird. Neben der expliziten Operation [LOP_MODIFY_COLUMNS] ist vor allen Dingen interessant, dass ausschließlich Slot 1 betroffen ist. Ein Blick auf die entsprechende Datenseite zeigt, dass weder ein anderer Slot gewählt wurde, noch dass sich die Position des Datensatzes verändert hat.

Als erste Erkenntnis kann man für einen HEAP mit fester Datensatzlänge festhalten, dass ein UPDATE eine eigene in sich geschlossene Transaktion ist, die nicht durch implizites DELETE mit einem anschließendes INSERT definiert ist.

Beispiel 2: Update in einem HEAP (variable Datensatzlänge)

Wie verhält sich Microsoft SQL Server in einem HEAP mit Attributen variabler Datensatzlängen. Um die nachfolgenden Ergebnisse zu interpretieren, bedarf es etwas Hintergrundwissen zur Verwaltung von Daten variabler Länge in einem HEAP. Werden, wie im nachfolgenden Beispiel, Daten hintereinander geschrieben, so werden die Slots sequentiell belegt; Datensatz A belegt Slot 0, Datensatz B belegt Slot 1, usw.. Der entscheidende Punkt bei der Speicherung der Daten ist, dass die Daten UNMITTELBAR hintereinander gespeichert werden. Hat z. B. Datensatz A eine Länge von 60 Bytes, dann beginnt Datensatz B bei Position 61 (Verwaltungsdaten wie Slot, etc. nicht berücksichtigt!). Was passiert aber, wenn nun Datensatz A plötzlich mehr Daten speichern soll, als beim ersten Speichervorgang?

Sollte der neue Datensatz nicht mehr auf die Datenseite selbst passen, wird er auf einer neuen Datenseite gespeichert. In diesem Fall spricht man von “Forwarded Records”. Ein Forwarded Record bedeutet, dass an der ursprünglichen Position eine Adresse hinterlegt wird, an der sich der “neue” Datensatz befindet (in etwa mit einem Nachsendeantrag zu vergleichen).

Sollte der Datensatz in seiner neuen Gesamtlänge noch auf die ursprüngliche Datenseite passen, wird für den Slot, in dem sich der Datensatz befindet, das Offset verändert, da der komplette Datensatz an eine neue – freie – Position verschoben werden muss, an der dieser Datensatz zusammenhängend abgelegt werden kann. Beide Szenarien werden mit der nachfolgenden Datenstruktur und den identischen Daten wie in Beispiel 1 überprüft:

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

Zunächst wird der Datensatz mit der [Id] = ‘B’ aktualisiert, indem der Wert des Attributs [col1] über den bisherigen Wert hinaus verlängert wird. Ein erster Blick auf die aktuellen Slots zeigt, dass der Datensatz mit der [Id] = ‘B’ in Slot 1 an der Position 144 beginnt:

DBCC TRACEON(3604);
DBCC PAGE ('demo_db', 1, 2466, 2) WITH TABLERESULTS;

Nun wird das Update für [Id] = ‘B’ ausgeführt, indem der Wert in [col1] zu 100 ‘A’s umgeschrieben wird.

BEGIN TRANSACTION updaterecord
    UPDATE dbo.tbl_demo
    SET    col1 = REPLICATE('A', 100)
    WHERE  Id = 'B'
COMMIT TRANSACTION

Schaut man sich das dazugehörige Transaktionsprotokoll an, wird man etwas überrascht sein; es führt lediglich aus, dass für den Datensatz in Slot 1 eine Modifikation durchgeführt wurde.

Tatsächlich ist aber deutlich mehr passiert, als es das Transaktionsprotokoll zeigt. Ein Blick auf die Datenseite zeigt, dass der Datensatz vollständig verschoben worden ist. Das kein Eintrag dazu im Transaktionsprotokoll vorhanden ist, ist logisch, da weder neue Datenseiten erzeugt werden mussten noch Datensätze in andere Slots verschoben werden mussten.

Da der Datensatz nicht vollständig in die vorhandene Position gepasst hat, musste der Datensatz – auf der Datenseite – an einer anderen Position gespeichert werden. Dieser Vorgang wird im Transaktionsprotokoll nicht aufgezeichnet.

Was passiert, wenn ein Datensatz nicht mehr vollständig auf eine Datenseite passt, soll das abschließende Beispiel zeigen, in dem im Datensatz mit der [Id] = ‘F’ der Eintrag für [col1] mit 7.000 ‘F’s gefüllt werden soll:

BEGIN TRANSACTION updaterecord
    UPDATE dbo.tbl_demo
    SET    col1 = REPLICATE('F', 7000)
    WHERE  Id = 'F'
COMMIT TRANSACTION

Ein erster auf das Transaktionsprotokoll zeigt bereits, dass deutlich mehr Aktionen von Microsoft SQL Server durchgeführt wurden, um den Datensatz zu speichern. Für die weiteren Erklärungen wurde zusätzlich die [Page Id] hinzugefügt!

Die Operation [LOP_COPY_VERSION_INFO] kann im Kontext vernachlässigt werden, da für die Datenbank in meinem Fall “Snapshot Isolation Level” aktiviert wurde. Entscheidend sind die beiden ersten – rot markierten – Transaktionsschritte. Hier passiert zunächst Folgendes:

Zeile 3: Der Datensatz [Id] = ‘F’ wird auf Seite “9af” (Dez: 2468) kopiert
Zeile 4: In Datensatz [Id] = ‘F’ wird [col1] zu 8000 ‘F’s

Anschließend wird der Nachsendeantrag eingetragen.
Zeile 5: Eintrag auf Seite “9a2” (Dez: 2466) wird modifiziert

Schaut man sich den Speicherort der einzelnen Datensätze genauer an, wird man aber überrascht sein; Datensatz [Id] = ‘F’ hat sich offensichtlich nicht von der Stelle bewegt.

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

Das scheint nun sehr widersprüchlich; besagt doch das Transaktionsprotokoll, dass eine neue Datenseite angelegt worden ist. Ein Blick auf die physikalischen Indexstatistiken besagen Gleiches; sie zeigen aber noch einen weiteren – im Zusammenhang mit HEAPS elementaren – Hinweis.

SELECT index_id,
       index_type_desc,
       fragment_count,
       page_count,
       record_count,
       forwarded_record_count
FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_demo'), DEFAULT, DEFAULT, 'DETAILED');

Die Indexstatistiken zeigen 27 Datensätze (seltsam, das Alphabet hat 26 Buchstaben) und einen “forwarded record”. Folgt man der Definition eines “Forwarded records”, ergibt das obige Ergebnis Sinn. Auf der – ursprünglichen – Datenseite (2466) wird der Datensatz nicht gelöscht, vielmehr wird der dort vorhandene Datensatz so geändert, dass nicht mehr die Daten selbst, sondern eine “Nachsendeadresse” hinterlegt ist. Somit ist der Datensatz in Slot 5 nicht gelöscht – er erfüllt nur eine andere Aufgabe – wie die nächste Abbildung deutlich belegt:

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

Fazit

Für HEAPS kann klar belegt werden, dass bei einer Modifikation eines Datensatzes KEIN separates DELETE verbunden mit einem anschließenden INSERT stattfindet; ehrlich gesagt hätte mich das auch sehr gewundert. Wie bereits oben beschrieben, versucht Microsoft SQL Server Operationen so ressourcenschonend wie möglich durchzuführen. Separate DELETE mit anschließenden INSERT würden diesem Prinzip zugegen laufen.

Jedoch haben die obigen Beispiele für HEAPS auch klar gezeigt, dass die Arbeit mit Attributen mit variabler Zeichenlänge unter besonderen Bedingungen mehr Ressourcen beanspruchen können, als eingeplant. Sofern ein Datensatz noch auf die gleiche Datenseite passt, müssen die Informationen tatsächlich von einer belegten Ressource zu einer anderen Ressource “verschoben” werden; passt der Datensatz überhaupt nicht mehr auf die Datenseite, wird der Datensatz komplett auf eine neue Datenseite verschoben und ein Pointer für den Datensatz wird an der ursprünglichen Adresse gespeichert – ähnlich einem Nachsendeantrag bei der Post.

Im nächsten Artikel werde die Operationen in Verbindung mit einem Clustered Index beleuchten – hier verhält es sich etwas anders, da ein Clustered Index immer eine physikalische Neuordnung erzwingt. Aber auch diese Ergebnisse werden den interessierten Leser sicherlich überraschen. Mehr zu diesem Thema gibt es nach meinem Urlaub ab dem 22.07.2013.

Herzlichen Dank fürs Lesen!