Wenn alle Datensätze aus einem Heap gelöscht werden, mag man meinen, dass Microsoft SQL Server nach dem Löschvorgang auch die allozierten Datenseiten wieder frei gibt. Das macht der Microsoft SQL Server jedoch nur, wenn bestimmte Voraussetzungen vorhanden sind wie der nachfolgende Artikel zeigt.

Was ist ein Heap

Unter einem HEAP versteht man eine Relation, die kein Clustered Index ist. Daten werden in einem HEAP nicht nach einer spezifizierten Ordnung gespeichert! Ebenso wenig verfügt ein Heap über ein definiertes Ordnungskriterium. Heaps werden in Datenbanken verwendet, um möglichst effizient Daten in Staging-Tabellen einzufügen um sie anschließend weiter zu verarbeiten (siehe “Neue Daten in einen Heap eintragen…”).

Testumgebung

Zunächst wird ein Heap erstellt, der mit 100 Datensätzen gefüllt wird. Anschließend wird die interne Struktur dieser Relation untersucht.

SET NOCOUNT ON;

GO

CREATE TABLE dbo.heap

(

    Id    int         NOT NULL IDENTITY (1, 1),

    c1    char(1000)  NOT NULL DEFAULT ('A')

);

GO

-- Insert 100 records and check the number of pages

INSERT INTO dbo.heap DEFAULT VALUES

GO 100

Die erste Frage ist natürlich, wie viele Datenseiten die Relation beim Befüllen alloziert hat. Das kann mit sys.dm_db_partition_stats herausgefunden werden:

-- Partition information

SELECT  index_id,

        in_row_data_page_count,

        in_row_used_page_count,

        in_row_reserved_page_count

FROM    sys.dm_db_partition_stats

WHERE   object_id = OBJECT_ID('dbo.heap');

Wie die Abbildung zeigt, belegt die Relation dbo.Heap 22 Datenseiten im Leaf. Das die Gesamtzahl der belegten Datenseiten höher ist, errechnet sich aus ALLEN von der Relation belegten Datenseiten. Ein Heap besitzt neben den reinen Datenseiten im Leaf die IAM-Seite (Index Allocation Map). Die Anzahl der reservierten Datenseiten lässt sich relativ leicht errechnen, wenn man weiß, dass Microsoft SQL Server beim Speichern von Daten in einer Tabelle die ersten 8 Datenseiten (Leaf) in einem Mixed Extent verwaltet. Alle weiteren Belegungen erfolgen ab der 9. Datenseite immer in einem Extent (entspricht 8 Datenseiten).

Nun werden Daten aus der Relation dbo.Heap gelöscht und erneut geprüft, wie sich die Datenstruktur verhält! Um das Transaktionsprotokoll besser analysieren zu können, wird die Operation in einer benannten Transaktion ausgeführt.

BEGIN TRANSACTION DeleteRecords

DELETE dbo.heap;

COMMIT TRANSACTION DeleteRecords

Nachdem alle Datensätze aus der Relation entfernt wurden, wird erneut überprüft, welche Datenseiten durch die Relation in der Datenbank alloziert sind. Nach dem allgemeinen Verständnis sollte nun nur noch die IAM-Seite vorhanden sein. Führt man die weiter oben beschriebene Prüfung in sys.dm_db_partition_stats erneut aus, wird man feststellen, dass immer noch alle Datenseiten von der Relation belegt sind. Ein Blick in das Transaktionsprotokoll zeigt, dass die Datenseiten NICHT freigegeben worden sind.

SELECT  [Current LSN],

        Operation,

        Context,

        AllocUnitName,

        [Page ID],

        [Slot ID],

        [Lock Information]

FROM    sys.fn_dblog(NULL, NULL) WHERE [Current LSN] LIKE

(

    SELECT  LEFT([Current LSN], LEN([Current LSN]) - 5) + '%'

    FROM    sys.fn_dblog(NULL, NULL)

    WHERE   [Transaction Name] = 'DeleteRecords'

)

ORDER BY

        [Current LSN] ASC;

Der Auszug aus dem Transaktionsprotokoll zeigt, dass Datensätze von den Datenseiten gelöscht werden [LOP_DELETE_ROWS] aber die Datenseiten nicht entfernt werden sondern lediglich auf der PFS protokolliert wird, wie viel Platz (in Prozent) auf den allozierten Datenseiten verfügbar ist (Siehe auch “Wie alloziert Microsoft SQL Server freien Speicher in einem HEAP?”

Warum werden die Datenseiten nicht wieder freigegeben?

Die ideale Erklärung hat der von mir sehr geschätzte Kollege Hugo Kornelis (Blog | Twitter | LinkedIn) dazu gegeben. Führt eine andere Session (SELECT-Session) einen Table Scan aus, während eine Delete-Operation (DELETE-Session) initiiert wird, hat die SELECT-Session bereits die IAM-Datenseite abgerufen. Auf der IAM-Datenseite werden Informationen zu den allozierten Datenseiten im Leaf gespeichert.

Die Abbildung zeigt die ersten 8 Datenseiten, die in einem Mixed Extent gespeichert sind. Die Datenseiten, Alle anderen Datenseiten werden in Extents (8 zusammengehörige Datenseiten) gespeichert.

In dem oben beschriebenen Szenario kann es nun zu zwei Problemsituationen kommen:

• Die SELECT-Session hat einen Shared Lock auf der IAM-Datenseite. In diesem Fall muss die DELETE-Session warten, bis der Shared Lock aufgehoben wird um anschließend einen Exclusive Lock zu setzen.
• Die SELECT-Session hat keine Sperre auf der IAM-Datenseite und die DELETE-Session löscht die Zuordnungen der Datenseiten. Nachdem die Datenseiten wieder an das System (Datenbank) zurück gegeben wurden, werden sie von einem anderen Objekt alloziert. Da die SELECT-Session die vorherige Zuordnung aus der IAM gelesen hat, wird sie die Datenseiten lesen wollen. Was dann passiert, dürfte jedem klar sein.

Aus diesem Grund werden die Datenseiten von Heaps, die nicht exklusiv gesperrt sind, nicht wieder freigegeben; eine gute Entscheidung, wie man sicherlich nachvollziehen kann. Der Beweis für diese Aussage wird mit dem nachfolgenden Skript geführt. Nachdem die Relation erneut mit 100 Datenseiten gefüllt wurde, besteht die folgende Situation / Struktur:

-- Partition information

SELECT  index_id,

        in_row_data_page_count,

        in_row_used_page_count,

        in_row_reserved_page_count

FROM    sys.dm_db_partition_stats

WHERE   object_id = OBJECT_ID('dbo.heap');

-- Page structure

SELECT  allocated_page_iam_page_id,

        allocated_page_page_id

FROM sys.dm_db_database_page_allocations

(

    db_id(),

    OBJECT_ID('dbo.heap', 'U'),

    NULL,

    NULL,

    'DETAILED'

)

ORDER BY

    is_iam_page DESC,

    allocated_page_page_id;

GO

Es werden erneut 23 Datenseiten alloziert. Seite 411 ist die IAM-Datenseite während die eigentlichen Daten der Relation auf 22 Datenseiten im LEAF-Bereich gespeichert werden. Die ersten 8 Datenseiten werden auf Mixed Extents gespeichert; alle weiteren Datensätze werden auf Exclusive Extents gespeichert.

BEGIN TRANSACTION DeleteRecords

DELETE dbo.heap WITH (TABLOCK);

COMMIT TRANSACTION DeleteRecords

GO

SELECT  [Current LSN],

        Operation,

        Context,

        AllocUnitName,

        [Page ID],

        [Slot ID],

        [Lock Information]

FROM    sys.fn_dblog(NULL, NULL) WHERE [Current LSN] LIKE

(

    SELECT LEFT([Current LSN], LEN([Current LSN]) - 5) + '%'

    FROM   sys.fn_dblog(NULL, NULL)

    WHERE  [Transaction Name] = 'DeleteRecords'

)

ORDER BY

        [Current LSN] ASC;

GO

Das Löschen der Daten in der Relation geschieht nun mit einer exklusiven Tabellensperre. Interessant ist nun, welche Änderungen sich aus dieser Tabellensperre im Transaktionsprotokoll ergeben – das zeigt die nächste Abbildung:

Es soll nicht jede Zeile dokumentiert werden aber auffällig sind besonders die Zeilen 11 – 16. Bei Löschen der Datensätze passiert nun Folgendes:

• Zunächst wird die PFS (Page Free Space) aktualisiert, da zuvor Datensätze von der Datenseite gelöscht wurden (Zeile 11).
• Die betroffene Datenseite (0x019A = 410) wird aktualisiert, nachdem alle Datensätze entfernt worden sind. (Zeile 12).
• Die Datenseite 410 ist nun leer und dieser Umstand wird von Microsoft SQL Server dazu verwendet, die IAM-Datenseite ebenfalls zu aktualisieren. Dort wird die Datenseite als “not allocated” gekennzeichnet. (Zeile 13)
• Die Datenseite 410 wurde auf einem Mixed Extent gespeichert. Da die Datenseite nun wieder an die Datenbank für weitere Aufgaben zurück gegeben wird, muss SGAM (Shared Global Allocation Map) ebenfalls aktualisiert werden. Das Bit für die Zuordnung der Datenseite (Verwaltung) in SGAM wird aktualisiert und die Datenseite ist offiziell nicht mehr aktiviert (Zeile 14)
• Auch die PFS benötigt diese Informationen, da die Datenseite nun wieder für die Datenbank zur Verfügung steht (Zeile 15)
• Zum Schluss wird die Anzahl der Datenseiten aktualisiert (HOBT steht für Heap Or B-Tree)

Wenn die Operation abgeschlossen ist, werden weitere Datenseiten gelöscht.

Zusammenfassung

Es ist immer wieder interessant, zu sehen, wie sehr sich Heaps von Clustered Indexen unterscheiden. Um die Datenseiten eines Heaps endgültig wieder als Ressource an die Datenbank zurück zu geben, muss die Tabelle exklusiv gesperrt sein. In einem Clustered Index werden die Datenseiten unmittelbar freigegeben, sobald keine Datensätze mehr auf der Seite gespeichert werden. Das wäre aber auf jeden Fall einen weiteren Artikel wert.

Herzlichen Dank fürs Lesen!