Page Splits – teure Datenbankoperationen im Detail

Page Splits sind kostenintensive Operationen, die zwangsläufig in Indexen auftreten, wenn die Schlüsselattribute nicht kontinuierlich / fortlaufend gefüllt werden. Der nachfolgende Artikel zeigt auf, welches Datenvolumen ein Page Split im im Transaktionsprotokoll verursachen kann und welche Einzeloperationen innerhalb einer solchen Transaktion von Microsoft SQL Server ausgeführt werden müssen.

Was ist ein Page Split

Ein Page Split tritt immer dann auf, wenn ein neuer Datensatz auf einer Indexseite eingetragen werden muss aber kein ausreichender Platz mehr auf der Datenseite zur Verfügung steht. Das gleiche Verfahren wird angewendet, wenn ein Datensatz aktualisiert werden soll und der neue Eintrag mehr Speicherplatz benötigt als der ursprüngliche Datensatz. Dieses Verhalten tritt aber ausschließlich bei Attributen mit variablen Datenlängen auf.

Die obige Abbildung verdeutlicht den Sachverhalt im Detail. Zunächst sind die Indexseiten sequentiell/fortlaufend angeordnet. Dieser Zustand ist in den meisten Fällen bei einem Neuaufbau eines Indexes vorhanden. Wird nun auf der dritten Datenseite ein neuer Datensatz mit dem Schlüsselattribut “RAC” eingetragen, erkennt Microsoft SQL Server, dass der verfügbare Speicherplatz nicht ausreichend ist und teilt die Daten der betroffenen Datenseite auf. Dabei werden ~50% der vorhandenen Daten auf eine neue Datenseite verschoben und anschließend der neue Datensatz auf der ursprüngliche Datenseite eingetragen. In der zweiten Abbildung ist zu erkennen, dass die “neue” Datenseite nicht unmittelbar hinter der ursprünglichen Datenseite angesiedelt ist sondern von Microsoft SQL Server am Ende positioniert wurde. Durch dieses Verfahren hat sich die sequentielle Leserichtung geändert! Es kann nu nicht mehr in der Reihenfolge 1, 2, 3, 4, 5 gelesen werden sondern Microsoft SQL Server muss die Daten in der Reihenfolge 1, 2, 3, 6, 4, 5 lesen, wenn die Relation vollständig gelesen werden soll – der Index ist fragmentiert. Wie hoch der tatsächliche Aufwand für diese Operation ist, belegt das folgende Beispiel:

Testumgebung

Für die Demonstration sowie die einzelnen Erläuterungen wird die nachfolgende Datenstruktur verwendet, in die 500 Datensätze eingetragen werden:

-- Erstellen der benötigten Relation

IF OBJECT_ID('dbo.demo_page_split', 'U') IS NOT NULL

    DROP TABLE dbo.demo_page_split;

    GO

  

CREATE TABLE dbo.demo_page_split

(

    Id    int          NOT NULL,

    c1    char(200)    NOT NULL    DEFAULT ('only filling stuff'),

   c2    char(200)    NOT NULL    DEFAULT ('additional filling stuff'),

  

   CONSTRAINT pk_demo_page_split PRIMARY KEY CLUSTERED (Id)

);

GO

  

-- Befüllen der Relation mit Beispieldaten

SET NOCOUNT ON;

GO

  

DECLARE    @i int = 1

WHILE @i <= 1000

BEGIN

    IF @i % 2 = 0

        INSERT INTO dbo.demo_page_split (Id) VALUES (@i)

  

    SET    @i += 1;

END

GO

Bestandsanalyse

Nachdem die Datensätze eingetragen worden sind, ist zunächst die physikalische Beschaffenheit des Clustered Index von Relevanz, bevor neue Daten eingetragen werden.

-- Ausgabe der Fragmentierung des Index

SELECT  fragment_count,

        avg_fragment_size_in_pages,

        avg_fragmentation_in_percent,

        avg_page_space_used_in_percent,

        page_count,

        record_count

FROM    sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.demo_page_split', 'U'), 1, DEFAULT, 'DETAILED');

Das Ergebnis der Abfrage zeigt, dass 500 Datensätze auf insgesamt 27 Datenseiten aufgeteilt sind. Die durchschnittliche Belegung pro Datenseite (Density) liegt bei ca. 95%. Obwohl die Daten sequentiell (aufsteigende Nummerierung) in die Relation eingetragen worden sind, zählt Microsoft SQL Server 7 Fragmentierungen. Wie kommt so etwas?

Grundsätzlich ist die Zahl nicht kritisch – sie besagt lediglich, dass in den Indexseiten 6 Positionen vorhanden sind, bei denen die Indexseiten numerisch nicht unmittelbar aufeinander folgen. Die nachfolgende Abfrage zeigt, auf welchen Datenseiten die Datensätze eingetragen worden sind.

-- Ausgabe der Daten nebst physikalischer Position

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

Die Abbildung zeigt, dass die Datensätze fortlaufend eingetragen wurden aber die Datenseiten nicht unmittelbar aneinander liegen. Auf die Datenseite 418 folgt nicht die Datenseite 419 sondern Datenseite 421. Dieser “Abstand” wird von Microsoft SQL Server als Fragment bewertet und fließt somit in die Gesamtzahl der Fragmentierungen mit ein. Gemäß der vorherigen Analyse gibt es insgesamt 7 Positionen, an denen die Datenseiten nicht unmittelbar aufeinander folgen (Fragmente).

Unter der Motorhaube – Blick auf eine Datenseite

Für das Verständnis der von Microsoft SQL Server durchzuführenden Operationen bei einem Page Split ist es unumgänglich, zu verstehen, wie Microsoft SQL Server Daten eines Index “intern” verwaltet. Jede Datenseite hat eine feste Größe von 8.192 Bytes und besteht aus drei wesentlichen Bereichen:

Header

Der Header einer Datenseite speichert alle Systeminformationen über die Datenseite wie Seitennummer, Seitentyp, Summe des freien Speichers, AllocationUnitId des Objekts, das die Datenseite belegt sowie – sofern es sich um indizierte Datenseiten handelt – die Datenseitennummer der vorherigen Datenseite und der nachfolgenden Datenseite. Beispielhaft sieht der Pageheader der Datenseite 421 (siehe Beispiel oben) wie folgt aus:

-- Ausgabe des PageHeaders 

DBCC TRACEON (3604);

DBCC PAGE ('db_fillfactor', 1, 421, 0);

Die für diesen Artikel wesentlichen Flags sind rot gekennzeichnet und haben folgende Bedeutung:

m_pageId	Aktuelle Seitennummer
m_prevPage	Verweis auf vorherige Seitennummer
m_nextPage	Verweis auf die nächste Seitennummer
m_SlotCnt	Anzahl der belegten Slots (Anzahl Datensätze)
m_freeCnt	Verfügbare Bytes auf der Datenseite
m_freeData	Offset der Position, ab der neue Daten eingetragen werden können

Daten

Der Datenbereich speichert Datenzeilen mit allen Daten, ausgenommen text-, ntext-, image-, nvarchar(max)-, varchar(max)-, varbinary(max)- und xml-Daten.

Slot(s) Array

Der Slot(s) Array speichert die Offsets der einzelnen Datensätze aus dem Datenbereich. Der Slot(s) Array beginnt am Ende der Seite, und jeder Slot(s) Array enthält einen Eintrag für jede Zeile auf der Seite. Jeder dieser Einträge zeichnet auf, wie weit das erste Byte der Zeile vom Beginn der Seite entfernt ist. Die Einträge im Slot(s) Array befinden sich in umgekehrter Reihenfolge zur Reihenfolge der Zeilen auf der Seite.

-- Ausgabe des Slot Arrays

DBCC TRACEON (3604);

DBCC PAGE ('db_fillfactor', 1, 421, 1);

Eintragen eines neuen Datensatzes

Nun wird ein neuer Datensatz mit der ID = 43 in die Relation eingetragen. Dieser Datensatz müsste gemäß der obigen Abbildung auf Datenseite 421 eingetragen werden, da die Datensätze mit der ID = 42 und ID = 44 sich ebenfalls auf dieser Datenseite befinden. Das Attribut bildet den Indexschlüssel und erzwingt somit die logische Einordnung des Datensatzes an dieser Position. Um das Datenvolumen zu “messen”, das während dieser Operation entsteht, wird die Aktion in einer expliziten – nicht abgeschlossenen -Transaktion aufgezeichnet wird.

-- Einleiten der Transaktion

BEGIN TRANSACTION

  

-- Eintragen des Datensatzes mit ID = 43

INSERT INTO dbo.demo_page_split (Id, c1, c2) VALUES (43, DEFAULT, DEFAULT);

  

-- Welches Transaktionsvolumen ist entstanden

SELECT  database_transaction_log_bytes_used

FROM    sys.dm_tran_database_transactions

WHERE   database_id = db_id();

Interessant ist das Ergebnis des für die Transaktion benötigten Datenvolumens von ~6.500 Bytes! Um einen Datensatz mit einer Satzlänge von 410 Bytes einzutragen, musste Microsoft SQL Server ~6 KBytes Transaktionsvolumen generieren. Ein Blick auf die physikalischen Indexstrukturen sowie den physikalischen Speicherort der Datensätze zeigt, dass eben nicht nur ein Datensatz eingetragen worden ist, sondern im Hintergrund eine nicht unerhebliche Menge an zusätzlichen Operationen ausgeführt wurde, um den Datensatz zu speichern:

-- Ausgabe der Fragmentierung des Index

SELECT  fragment_count,

        avg_fragment_size_in_pages,

        avg_fragmentation_in_percent,

        avg_page_space_used_in_percent,

        page_count,

        record_count

FROM    sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.demo_page_split', 'U'), 1, DEFAULT, 'DETAILED');

  

-- Ausgabe der Daten nebst physikalischer Position

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

Die Anzahl der Fragmente hat sich erhöht und man kann bei der Ausgabe der logischen Position der Datensätze erkennen, dass sich auf der Datenseite 421 nur noch 9 Datensätze befinden. Die restlichen Datensätze, die sich zuvor auf der gleichen Datenseite befunden haben, wurden auf eine neue Datenseite 428 verschoben! Ein Blick auf den Header der Datenseite zeigt diese Veränderungen ebenfalls:

-- Ausgabe des PageHeaders 

DBCC TRACEON (3604);

DBCC PAGE ('db_fillfactor', 1, 421, 0);

Deutlich ist zu erkennen, dass das Flag m_nextPage geändert wurde (Verweis auf die neue “nachfolgende” Datenseite). Durch das Verschieben von Datensätzen wurde auf der Datenseite Platz für neue Datensätze geschaffen. Das wird durch das Flag m_freeCnt angezeigt – insgesamt stehen auf der Datenseite 421 nun 4.379 Bytes für die Speicherung neuer Daten zur Verfügung. Das Offset für die Speicherung von neuen Datensätzen beginnt bei 3.795. Insgesamt sind 9 Datensätze auf der Datenseite vorhanden, wie m_slotCnt belegt.

Auswertung des Transaktionsprotokolls

Basierend auf den obigen Auswertungen ist ersichtlich, dass Microsoft SQL Server für das Organisieren der Datenseiten eine nicht unerhebliche Anzahl von verschiedenen Operationen durchzuführen hatte. Diese Operationen kann man im Detail abrufen und man sieht deutlich, warum ein Page Split ein hohes Datenvolumen im Transaktionsprotokoll erzeugt.

SELECT  Operation,

        Context,

        AllocUnitName,

        [Page ID]

FROM    sys.fn_dblog(NULL, NULL)

ORDER BY

        [Current LSN];

Das Ergebnis der obigen Abfrage beschränke ich aus Gründen der besseren Lesbarkeit ausschließlich auf die INSERT-Transaktion.

Die obige Abbildung zeigt die Transaktionsoperationen, die während der INSERT-Operation ausgeführt wurden. In Zeile 4 beginnt die Transaktion (äußere Transaktion), die mit BEGIN TRANSACTION eingeleitet wurde. Innerhalb dieser Transaktion jedoch wird explizit eine weitere Transaktion (Zeile 5 – 22) eingeleitet. Exakt dieser Teil des Transaktionsprotokolls beschreibt die notwendigen Operationen, die während eines Page Splits auftreten. Für die bessere Verdeutlichung der betroffenen Objekte dient die nachfolgende “Skizze”.

Zeile 6 – 8

Zunächst werden die betroffenen Datenseiten als “Objekte in einer Systemtransaktion” gekennzeichnet (LOP_INSYSACT). Betroffen sind die Datenseiten 1a4, 1a5 und 1a6 (420, 421, 422). Bei Datenseite 420 handelt es sich um eine B-Tree-Seite, in der die Datenseiten (Leaf) verwaltet werden. Bei Datenseite 421 und 422 handelt es sich um die eigentlichen Datenseiten.

Microsoft SQL Server “plant” bereits den Page Split und erkennt, dass die Datenseiten 421 und 422 unmittelbar miteinander über m_PreviousPage (421) und m_NextPage (422) miteinander verbunden sind. Die B-Tree-Seite muss ebenfalls für die Transaktion gekennzeichnet werden, da – bedingt durch den Page Split – eine weitere Datenseite in die Verwaltung mit aufgenommen werden muss (Datenseite 428).

Zeile 9 – 11

Die nächsten drei Zeilen im Transaktionsprotokoll beschreiben bereits die Allokierung der neu zu erstellenden Seite. Hierzu wird zunächst in der PFS (Page Free Space) (LCX_PFS) ein Eintrag vorgenommen (LOP_MODIFY_ROW), der das zur Verfügung stehenden Datenvolumen der neuen Datenseite beschreibt. Gleichzeitig muss aber für die “alten” Datenseiten ebenfalls eine Anpassung vorgenommen werden. Auch diese Modifizierung wird durch die Operation durchgeführt.

Da eine neue Datenseite in der Datenbank allokiert wurde, muss diese Information ebenfalls in der Datenbank hinterlegt werden. Hierzu wird intern der Zähler für die Verwaltung der Datenseiten um 1 hochgezählt (LOP_HOBT_DELTA). Anschließend kann die neu allokierte Datenseite (428) für die Speicherung von Datensätzen formatiert werden (zeroed out) (LOP_FORMAT_PAGE).

Zeile 12 – 17

Nachdem die neue Datenseite erfolgreich in der Datenbank “registriert” wurde, kann sie der Systemtransaktion ebenfalls hinzu gefügt werden. Anschließend werden ca. 50% der Bestandsdaten aus Datenseite 421 auf die Datenseite 428 verschoben. Der interessanteste Teil der gesamten Operation folgt anschließend. Die Headerinformationen der Indexseiten müssen angepasst werden, da – bedingt durch den Page Split nun nicht mehr Datenseite 422 auf 421 folgt sondern 428. Genau diese Informationen müssen nun in den Headern der betroffenen Datenseiten aktualisiert werden.

Dazu wird der Header der Datenseite 421 zunächst aktualisiert und der Wert des Flag m_NextPage wird auf 428 geändert. Anschließend erfolgt die Änderung des Flags m_PreviousPage im Header von Datenseite 422, auch hier wird der Wert auf 428 geändert.

Nachdem alle Änderungen an den Datenseiten im Leaf durchgeführt wurden, wird die neue Datenseite im B-Tree (Datenseite 420) registriert (LOP_INSERT_ROW) und der Vorgang ist so gut wie abgeschlossen.

Zeile 17 – 22

Nachdem der Page Split abgeschlossen wurde, werden die gesperrten Datenseiten wieder freigegeben und die Transaktion wird abgeschlossen (LOP_COMMIT_XACT).

Erst, nachdem der Page Split beendet ist, kann die eigentliche Operation durchgeführt werden – das Eintragen des neuen Datensatzes, das ebenfalls mit einem LOP_COMMIT_XACT beendet wird.

Nach dem Page Split ergibt sich für die physikalische Anordnung der Datenseiten folgendes Bild:

Ein Blick in die Header der betroffenen Datenseiten zeigt die Auswirkungen der Transaktionsschritte.

Veränderungen im B-Tree (Page 420)

Da eine neue Datenseite auf der Datenebene (Leaf) hinzugefügt wurde, muss diese Datenseite über den B-Tree verfügbar gemacht werden. Die nachfolgende Abbildung zeigt, wie die neue Datenseite (428) sich in den Kontext der anderen Datenseiten eingegliedert wurde:

DBCC TRACEON (3604);

DBCC PAGE ('db_fillfactor', 1, 420, 3);

Alle Datensätze von Id = 40 – 55 befinden sich auf Datenseite 421 während die Datensätze 56 – 77 auf die neue Datenseite 428 verschoben wurden. Um einen Indexscan durchzuführen, müssen nun die Datenseiten in der Reihenfolge 418, 421, 428, 422, ... gelesen werden.

Veränderungen auf Datenseiten (421, 422, 428)

Diese Prüfung mag sich fast schon erübrigen jedoch lohnt der Blick auf die Header der Datenseiten.

DBCC PAGE ('db_fillfactor', 1, 421, 0);

DBCC PAGE ('db_fillfactor', 1, 422, 0);

DBCC PAGE ('db_fillfactor', 1, 428, 0);

Fazit

Page Splits lassen sich in Indexen nicht vermeiden. Sobald ein Index (sei es ein Clustered Index oder ein Non Clustered Index) keine fortlaufend wachsenden Werte in den Schlüsselattributen besitzt, wird es immer wieder zu Page Splits kommen. Von einigen Entwicklern habe ich in diesem Zusammenhang immer wieder gehört, dass es nicht dramatisch sei; man habe ja das Wiederherstellungsmodell “SIMPLE” gewählt.

Tatsächlich ist jedoch die Datenmenge der Transaktionen unabhängig vom Wiederherstellungsmodell – ein Page Split ist immer eine vollständig protokollierte Transaktion. Auch, wenn Speichermedien heute günstig sind, sollte man im Hinterkopf behalten, dass jede Transaktion neben dem Volumen auch ZEIT kostet. Hat man OLTP-Systeme, die möglichst schnell Daten verarbeiten müssen, sollte man diese “Grundhaltung” womöglich noch einmal überdenken.

Befindet sich eine Datenbank in einer Hochverfügbarkeitsumgebung (AlwaysOn, Mirroring, LogShipping), sind Page Splits noch sorgfältiger zu beachten. Da es sich um vollständig protokollierte Transaktionen handelt, müssen diese Transaktionen auch auf die Hochverfügbarkeitslösungen übertragen werden.

Strategien zur Vermeidung von Page Splits habe ich bereits im Artikel “FILLFACTOR – Vor- und Nachteile” beschrieben.

Herzlichen Dank fürs Lesen!

Tipps und Tricks, Optimierung / Performance, Level 400

Print article

This entry was posted by Uwe Ricken on 28.10.13 at 15:52:00 . Follow any responses to this post through RSS 2.0.

Uwe Ricken