Sonntag, 6. April 2014

Löschen von Daten aus Heap gibt Datenseiten nicht frei

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');

sys.dm_db_partition_stats_01


Wie die obige 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;

fn_dblog_01




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.


DBCC_PAGE_IAM_01


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


ALL_SITUATION_BEFORE_TABLOCK


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:


fn_dblog_02


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!

Kommentare :

  1. Hallo Herr Ricken, vielen Dank für diesen und natürlich hauch Ihren anderen hochwertigen Beiträge.
    Sie verweisen in Ihrem Beitrag darauf, dass der Microsoft SQL Server nach einem Löschvorgang ohne Exclusive Lock allozierten Datenseiten nicht wieder frei gibt. Wie lassen sich diese Ressource dann nach mehren DELETE Operationen wieder an Datenbank zurück zu geben?

    AntwortenLöschen
  2. Hallo Herr Gerber,

    herzlichen Dank für die "Blumen". Es freut mich, dass Ihnen mein Blog gefällt.
    Wenn Datensätze ohne einen TABLOCK aus einem HEAP gelöscht werden, werden die Datenseiten nicht freigegeben bis ein REBUILD durchgeführt wurde. Der nachfolgende Code (basierend auf den obigen Beispielen) verdeutlicht dieses Verhalten:

    -- Delete records from only one page
    DELETE dbo.heap WHERE Id BETWEEN 8 AND 14;
    GO

    DELETE dbo.heap WHERE Id BETWEEN 15 AND 21;
    GO

    Die obigen Zeilen löschen Daten aus jeweils einer vollständigen Datenseite. Eine erneute Analyse mit sys.dm_db_partition_stats zeigt, dass immer noch die gleiche Anzahl von Datenseiten alloziert sind.

    Nun wird die Tabelle neu aufgebaut:

    ALTER TABLE dbo.Heap REBUILD;

    Eine erneute Prüfung wird zeigen, dass die Datenseiten nun entfernt wurden.

    Danke fürs Lesen!
    Uwe Ricken

    AntwortenLöschen
  3. Hallo Herr Ricken,

    zunächst einmal: super Blogs und Vorträge, die mir schon oft sehr geholfen haben – vielen Danke dafür!

    Speziell mit dem Wissen über diese »Heap Freigabe Problematik« konnte ich unter anderem ein Problem in einer Kundendatenbank finden und beheben lassen. Dort hatte wir den Fall, dass ein Agent Job (jede Stunde / jeweils ca. 2.000 Datensätze - (24/7)) einer Stammdatentabelle gelöscht hat und diese dann einem Bulk Insert wieder komplett gefüllt wurde. - (Die Tabelle war ein Heap ohne PK und ohne Indexe) ….

    Meine Frage zu diesem Thema ist folgende:
    Kann man abfragen ob und wie stark eine Heap Tabelle mit nicht freigegebenen Datenseiten „belastet“ ist?


    Ich habe versucht, für diese Fragestellung eine Abfrage zu schreiben, die aufgrund von Durchschnittswerten (avg_record_size_in_bytes) zumindest einen groben „Fingerzeig“ geben könnte, welcher Heap evtl. viele nicht freigegebene Datenseiten hat und genauer betrachtet werden sollte:

    DECLARE
    @min_page_count INT = 5,
    @threshold_page_needed INT = 110


    DECLARE @index_physical_stats TABLE (
    database_id SMALLINT,
    object_id INT,
    index_id INT,
    partition_number INT,
    index_type_desc NVARCHAR (60),
    alloc_unit_type_desc NVARCHAR (60),
    index_depth TINYINT,
    index_level TINYINT,
    avg_fragmentation_in_percent FLOAT (8),
    fragment_count BIGINT,
    avg_fragment_size_in_pages FLOAT (8),
    page_count BIGINT,
    avg_page_space_used_in_percent FLOAT (8),
    record_count BIGINT,
    ghost_record_count BIGINT,
    version_ghost_record_count BIGINT,
    min_record_size_in_bytes INT,
    max_record_size_in_bytes INT,
    avg_record_size_in_bytes FLOAT (8),
    forwarded_record_count BIGINT,
    compressed_page_count BIGINT
    )

    INSERT INTO @index_physical_stats
    SELECT
    *
    FROM
    sys.dm_db_index_physical_stats (Db_Id (), DEFAULT, DEFAULT, DEFAULT, 'DETAILED')
    WHERE
    index_id = 0 AND IsNull (avg_record_size_in_bytes, 0) <> 0 AND alloc_unit_type_desc = 'IN_ROW_DATA' AND record_count > 0


    WITH stats (object_id, object_name, page_count, record_count, page_needed, avg_record_size_in_bytes)
    AS
    (SELECT
    object_id,
    Object_Name (object_id) AS object_name,
    page_count,
    record_count,
    Floor (record_count / Floor (8060 / avg_record_size_in_bytes)) + 1,
    avg_record_size_in_bytes
    FROM
    @index_physical_stats
    )
    SELECT
    object_id,
    object_name,
    page_count,
    page_needed,
    Cast((100 * page_count / page_needed) AS VARCHAR) + ' %' AS avg_page_needed_exceed_percent,
    record_count,
    avg_record_size_in_bytes
    FROM
    stats
    WHERE
    100 * page_count / page_needed > @threshold_page_needed AND page_count > @min_page_count


    Liebe Grüße
    Volker Salzer

    AntwortenLöschen