Montag, 28. Oktober 2013

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.

PageSplits

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:

   1: -- Erstellen der benötigten Relation
   2: IF OBJECT_ID('dbo.demo_page_split', 'U') IS NOT NULL
   3:     DROP TABLE dbo.demo_page_split;
   4:     GO
   5:  
   6: CREATE TABLE dbo.demo_page_split
   7: (
   8:     Id    int          NOT NULL,
   9:     c1    char(200)    NOT NULL    DEFAULT ('only filling stuff'),
  10:     c2    char(200)    NOT NULL    DEFAULT ('additional filling stuff'),
  11:  
  12:     CONSTRAINT pk_demo_page_split PRIMARY KEY CLUSTERED (Id)
  13: );
  14: GO
  15:  
  16: -- Befüllen der Relation mit Beispieldaten
  17: SET NOCOUNT ON;
  18: GO
  19:  
  20: DECLARE    @i int = 1
  21: WHILE @i <= 1000
  22: BEGIN
  23:     IF @i % 2 = 0
  24:         INSERT INTO dbo.demo_page_split (Id) VALUES (@i)
  25:  
  26:     SET    @i += 1;
  27: END
  28: 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.



   1: -- Ausgabe der Fragmentierung des Index
   2: SELECT  fragment_count,
   3:         avg_fragment_size_in_pages,
   4:         avg_fragmentation_in_percent,
   5:         avg_page_space_used_in_percent,
   6:         page_count,
   7:         record_count
   8: FROM    sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.demo_page_split', 'U'), 1, DEFAULT, 'DETAILED');
dm_db_index_physical_stats_01

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.



   1: -- Ausgabe der Daten nebst physikalischer Position
   2: SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS Location, * FROM dbo.demo_page_split;

RecordSet_01


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:



   1: -- Ausgabe des PageHeaders 
   2: DBCC TRACEON (3604);
   3: DBCC PAGE ('db_fillfactor', 1, 421, 0);

DBCC_PAGE_01


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





















m_pageIdAktuelle Seitennummer
m_prevPageVerweis auf vorherige Seitennummer
m_nextPageVerweis auf die nächste Seitennummer
m_SlotCntAnzahl der belegten Slots (Anzahl Datensätze)
m_freeCntVerfügbare Bytes auf der Datenseite
m_freeDataOffset 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.



   1: -- Ausgabe des Slot Arrays
   2: DBCC TRACEON (3604);
   3: DBCC PAGE ('db_fillfactor', 1, 421, 1);

SLOT_ARRAY_01



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.



   1: -- Einleiten der Transaktion
   2: BEGIN TRANSACTION
   3:  
   4: -- Eintragen des Datensatzes mit ID = 43
   5: INSERT INTO dbo.demo_page_split (Id, c1, c2) VALUES (43, DEFAULT, DEFAULT);
   6:  
   7: -- Welches Transaktionsvolumen ist entstanden
   8: SELECT  database_transaction_log_bytes_used
   9: FROM    sys.dm_tran_database_transactions
  10: 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:



   1: -- Ausgabe der Fragmentierung des Index
   2: SELECT  fragment_count,
   3:         avg_fragment_size_in_pages,
   4:         avg_fragmentation_in_percent,
   5:         avg_page_space_used_in_percent,
   6:         page_count,
   7:         record_count
   8: FROM    sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.demo_page_split', 'U'), 1, DEFAULT, 'DETAILED');
   9:  
  10: -- Ausgabe der Daten nebst physikalischer Position
  11: SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS Location, * FROM dbo.demo_page_split;
dm_db_index_physical_stats_02

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:



   1: -- Ausgabe des PageHeaders 
   2: DBCC TRACEON (3604);
   3: DBCC PAGE ('db_fillfactor', 1, 421, 0);
DBCC_PAGE_02

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.



   1: SELECT  Operation,
   2:         Context,
   3:         AllocUnitName,
   4:         [Page ID]
   5: FROM    sys.fn_dblog(NULL, NULL)
   6: ORDER BY
   7:         [Current LSN];

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


FN_DBLOG_01


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”.


PAGE_STRUCTURE_01


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:


PAGE_STRUCTURE_02


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:



   1: DBCC TRACEON (3604);
   2: DBCC PAGE ('db_fillfactor', 1, 420, 3);
DBCC_PAGE_03

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.



   1: DBCC PAGE ('db_fillfactor', 1, 421, 0);
   2: DBCC PAGE ('db_fillfactor', 1, 422, 0);
   3: DBCC PAGE ('db_fillfactor', 1, 428, 0);

DBCC_PAGE_04


DBCC_PAGE_05


DBCC_PAGE_06


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!

Kommentare :

  1. Hallo Uwe,
    Erstmal super Artikel so wie immer.
    Ich hätte aber eine frage dazu:
    "Du schreibst Pagesplitts lassen sich nicht vermeiden"
    Kann man den sagen , bzw woran kann man ein "normales" Pagesplitt verhalten erkennen?
    ist ein wert von 100 Splitts bei 5000 Updates einer Tabelle viel?

    Liebe Grüße
    Alex Weber

    AntwortenLöschen
  2. Hallo Alex,
    seit Microsoft SQL Server 2005 sind "geloggede" Transaktionen immer "bad splits".
    Leider kann man nicht sagen, ob 100 Splits bei 5.000 Eintragungen viel sind, da es immer abhängig ist vom Workload.

    Ein Beispiel:
    100 Page Splits bei 5.000 Eintragungen von Datensätzen mit einer Länge von 100 Bytes ist sehr viel.
    100 Page Splits bei 5.000 Eintragungen von Datensätzen mit einer Länge von 1.000 Bytes sind widerum recht wenig.
    Entscheident ist immer, wie viele Datensätze auf eine Datenseite passen und wie "zufällig" die Index-Key-Attribute sind.

    AntwortenLöschen
  3. Hallo Uwe,

    erst einmal ein dickes Lob für diese Erläuterung!!!

    Ich hab mal etwas mit dem Code experimentiert und nach der Transaktion ein ROLLBACK ausgeführt. Anschließend habe ich die Transaktion wiederholt. Dabei fielen dann aber nur ca 700 Bytes Transaktionsprotokoll an. Kann es sein, dass der PageSplit physikalisch ausgeführt wird, auch wenn die Transaktion abgebrochen wird?

    Das Transaktionslog kann man leichter lesen, wenn man ein CHECKPOINT davor absetzt (ab SQL 2012). Und in der Abfrage würde ich die Spalte Description mitnehmen. Da sieht man den Split und das Moving der Rows sehr schön.

    Viele Grüße
    Stephan Walther

    AntwortenLöschen
  4. Hallo Stephan,

    herzlichen Dank für das Lob - um so mehr Spaß macht bei solchen Kommentaren das Schreiben :)
    Tatsächlich ist es so, dass der Page Split eine eigene systeminterne Transaktion ist!

    Der Page Split selbst ist autark und wird auch in der Transaktion selbst mit einem EIGENEN Commit beendet.
    Bei einem ROLLBACK sind also die Daten selbst bereits auf die neue Seite verschoben worden.

    Das kannst Du sehr einfach testen, indem Du vor dem ROLLBACK folgendes Statement absetzt:

    SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.page_split;

    Das gleiche Statement NACH dem ROLLBACK liefert ein identisches Ergebnis.
    Da also der Page Split bereits durchgeführt worden ist, ist ein erneuter Versuch mit weniger Transaktionsvolumen verbunden.

    Herzlichen Dank fürs Lesen!
    Uwe Ricken

    AntwortenLöschen