Platz für die nächste Zeile

Eine Reihe zu Indexwartung

Über Fragmentierung, Fillfactor und Indexwartung wird in der SQL-Server-Community regelmäßig diskutiert, unter anderem auf LinkedIn. Diese dreiteilige Reihe bietet einen verständlichen Einstieg und eine gemeinsame Grundlage für die Praxis: Was passiert auf den Indexseiten? Was verändern REORGANIZE und REBUILD? Und woran lässt sich erkennen, ob Wartung tatsächlich hilft? Wir erklären die Mechanismen mit Seitenbildern, prüfen sie mit eigenen Labversuchen und ordnen die Ergebnisse für unterschiedliche Schreib- und Lesemuster ein.

Der Wartungsjob ist erfolgreich durchgelaufen. Die Fragmentierung ist niedrig, trotzdem entstehen bei den nächsten Inserts wieder Splits. Um das einzuordnen, müssen wir drei Eigenschaften auseinanderhalten: die Reihenfolge der Seiten, ihre Füllung und den freien Platz auf der nächsten Zielseite.

Dafür reichen ein paar Seiten mit nummerierten Einträgen. Teil 2 und Teil 3 prüfen das Bild anschließend mit Messungen.

Zeilen liegen auf Seiten

SQL Server speichert Daten in Seiten. Eine Seite ist 8 KiB groß. Nach dem Seitenkopf bleiben 8.096 Byte für die Einträge und ihr Slot-Verzeichnis (Microsoft: Pages and extents). Bei einem Clustered Index liegen die Zeilen selbst auf den Seiten der untersten Ebene, den Blattseiten, geordnet nach dem Schlüssel.

Damit SQL Server nicht jede Seite durchsuchen muss, gibt es darüber einen Wegweiser: „bis 40 auf Seite 1, bis 80 auf Seite 2, danach Seite 3“. Bei großen Tabellen hat dieser Wegweiser selbst mehrere Ebenen. Für alles Weitere reichen die Blattseiten und ein Schild darüber.

In unseren Bildern hat jede Seite vier Plätze, und alle Einträge sind gleich groß. Die Zahlen zeigen die logische Schlüsselordnung, nicht die Lage der Bytes auf einer echten Seite. Echte Seiten fassen je nach Zeilenbreite Dutzende oder Hunderte Zeilen. Das Prinzip bleibt dasselbe.

Lesen: Wie viele Seiten muss ich anfassen?

SQL Server liest immer ganze Seiten. Ein Punktzugriff auf eine einzelne Zeile folgt dem Wegweiser zu genau einer Blattseite. Ein Scan über alle Zeilen von 10 bis 120 liest alle Blattseiten nacheinander.

Das ist die Seitendichte: wie viel Platz auf den Seiten im Mittel belegt ist. Im ersten Bild 100 %, im zweiten 75 %. In der Dokumentation heißt der Wert avg_page_space_used_in_percent (Microsoft: sys.dm_db_index_physical_stats). Für Scans ist hohe Dichte gut: Derselbe Bestand belegt weniger Blattseiten, im Arbeitsspeicher wie auf dem Datenträger. Seiten, die schon im Buffer Pool liegen, müssen dabei nicht erneut von der Platte gelesen werden.

Wäre das alles, sollte jede Seite immer randvoll sein. Aber es kommen neue Zeilen dazu.

Der Schlüssel bestimmt die Zielseite

Eine neue Zeile kann nicht irgendwohin. Der Schlüssel bestimmt, auf welche Seite sie gehört. Die 25 gehört zwischen 20 und 30, also auf Seite 1.

Wenn neue Schlüssel am oberen Ende des vorhandenen Bereichs liegen, wie meist bei IDENTITY, wächst der Index überwiegend am Rand. Das ist der zweite Fall. Zufällig verteilte Schlüssel, etwa GUIDs aus NEWID(), treffen dagegen auch bestehende Bereiche mitten im Index. Das ist der erste Fall, und um ihn geht es in dieser Reihe vor allem.

Die Zielseite ist voll: der Page Split

Was, wenn die 25 kommt und Seite 1 voll ist?

Das ist ein Page Split. Er kostet mehr als ein normales Einfügen: Einträge werden verschoben, eine neue Seite wird belegt, der Wegweiser angepasst, und all das wird im Transaktionslog festgehalten. In unserem Modell werden die Einträge genau hälftig verteilt; in SQL Server ist das Verhältnis nicht immer exakt 50:50. Ein einzelner Split ist kein Drama. Wiederkehrende Splits sind zusätzliche Schreibarbeit bei einem Teil der Inserts.

Nach dem Split ist auf beiden Seiten wieder Platz für die nächsten Zeilen. Gleichzeitig sinkt die Dichte.

Brent Ozar zeigt in seinem Beitrag über Page Splits animiert, welche zusätzliche Schreibarbeit dabei entsteht.

Fragmentierung ändert die Schlüsselordnung nicht

Schauen Sie noch einmal auf das Ergebnis von Bild 4: Seite 1, dann Seite 7, dann Seite 2. Wer den Index in Schlüsselreihenfolge liest, springt in der Datei von vorne nach hinten und wieder zurück.

Das ist logische Fragmentierung: der Anteil der Blattseiten, deren Lage in der Datei nicht zur Schlüsselreihenfolge passt. Die logische Ordnung des Index ist in beiden Bildern vollständig erhalten; der Index findet jeden Eintrag. Ein fragmentierter Index ist kein kaputter Index. (Eine bestimmte Reihenfolge im Ergebnis einer Abfrage garantiert übrigens nur ORDER BY, mit oder ohne Fragmentierung.)

Fragmentierung und Seitendichte sind zwei verschiedene Eigenschaften. Microsoft beschreibt das ausdrücklich und hält die Seitendichte in vielen Fällen für die wichtigere (Microsoft: Index fragmentation and page density). Fragmentierung spielt vor allem eine Rolle, wenn große Bereiche von einem langsamen Datenträger gelesen werden.

Reserve: Platz auf der Zielseite

Splits entstehen, weil auf der Zielseite kein Platz ist. Die naheliegende Idee: Platz lassen. Aber wo?

Beide Zustände enthalten dieselben sechs Einträge und zusammen zwei freie Plätze. Die mittlere Dichte ist in beiden 75 %. Jetzt kommt die 25, Zielseite ist Seite 1. In Zustand B ist dort ein Platz frei, sie passt. In Zustand A ist Seite 1 voll: Split, obwohl zwei Plätze frei sind, nur eben auf Seite 2.

Das ist der Kern dieser Reihe: Für Inserts in bestehende Schlüsselbereiche zählt der freie Platz auf der Zielseite, nicht nur der Durchschnitt. Bei zufällig verteilten Schlüsseln kann die Zielseite fast jede Seite sein. Ob sich Reserve lohnt, hängt davon ab, wie oft solche Inserts kommen und was die zusätzlichen Seiten beim Lesen kosten.

Fillfactor: Reserve beim Aufbau

SQL Server kann Reserve gezielt anlegen. Der Fillfactor beeinflusst, wie voll die Blattseiten beim Aufbau und beim REBUILD eines Index werden (Microsoft: Specify fill factor).

Drei Dinge sind dabei wichtig:

• Der Fillfactor ist ein Ziel beim Aufbau und REBUILD. Die tatsächliche Füllung hängt von der Zeilengröße ab und entspricht nicht auf jeder Seite exakt dem Prozentwert.
• Danach füllen Inserts die Reserve wieder auf. SQL Server hält den Platz nicht dauerhaft frei.
• Reserve kostet Seiten. Fillfactor 75 heißt im Bild: vier statt drei Seiten, die ein Scan anfassen und der Speicher halten muss.

Für Indizes, in die zufällig verteilt geschrieben wird, kann sich das lohnen. Für Indizes, die überwiegend gelesen werden oder nur am Rand wachsen, ist es oft verschenkter Platz. Einen Wert, der für alle Indizes passt, gibt es nicht.

REORGANIZE und REBUILD

Nach einer Weile mit vielen zufälligen Inserts sieht der Index gemischt aus: Manche Seiten sind frisch geteilt und halb leer, andere haben ihre Reserve aufgebraucht und sind voll. Die Seiten liegen kreuz und quer in der Datei. SQL Server bietet zwei Wege, das zu ändern.

REORGANIZE arbeitet auf den vorhandenen Blattseiten. Es sortiert sie um und schiebt Zeilen von wenig gefüllten Seiten zusammen, höchstens bis zum zuletzt gesetzten Fillfactor. Seiten, die schon voller sind, leert es nicht (Microsoft: Increase page density or reduce page splits). Es läuft online und lässt sich abbrechen, belastet das System aber trotzdem.

REBUILD baut den Index neu auf und wendet dabei den Fillfactor auf die Blattseiten an. Er braucht mehr Ressourcen auf einmal. Offline kann er den Zugriff auf die Tabelle für die Dauer blockieren; auch ein Online-REBUILD hat kurze Sperrphasen (Microsoft: How online index operations work).

Beide Wege senken die Fragmentierung. Welcher passt, hängt vom Ziel ab: Für kompaktere Seiten zum Lesen kann REORGANIZE genügen. Für neue Reserve auf vollen Seiten braucht es einen REBUILD. Ob einer der beiden den Aufwand lohnt, entscheidet die Last.

Statistiken: ein zweiter Wirkungspfad

Neben den Seiten gibt es zu jedem Index eine Statistik: eine kleine Übersicht, wie die Werte verteilt sind, etwa „etwa 30 % der Einträge liegen unter 50“. Der Optimizer nutzt sie, um zu schätzen, wie viele Zeilen eine Abfrage liefert, und wählt danach seinen Plan (Microsoft: Statistics).

Ein Statistikupdate ordnet die Daten auf den Blattseiten nicht neu und schafft dort keine Reserve. Umgekehrt aktualisiert ein REBUILD die Statistik dieses Index gleich mit, REORGANIZE nicht (Microsoft: Statistics update during index build). Wenn eine Abfrage nach einem REBUILD schneller ist, kann das deshalb zwei Gründe haben: andere Seiten oder ein anderer Plan.

Was die Messungen sagen

So weit das Modell. Ob es sich in SQL Server so verhält, haben wir im Lab geprüft, auf SQL Server 2022 und 2025, mit einem synthetischen Index und zufällig verteilten GUID-Schlüsseln:

• Teil 2: Nach REORGANIZE war der Index nur noch zu 0,28 % fragmentiert. Die nächsten 100.000 Inserts erzeugten trotzdem 14.870 Split-Logrecords, nach einem REBUILD mit Fillfactor 81 nur 2.632. Das entspricht Bild 8. → Aufgeräumt, aber ohne Platz
• Teil 3: Ganz ohne Wartung stabilisierte sich die mittlere Seitendichte in unserem Versuch um 68 %, und Split-Logrecords entstanden weiter. Das ist Bild 4, millionenfach wiederholt. → Wenn der Index ohne Wartung wächst

Gezählt haben wir nicht den bekannten Perfmon-Zähler Page Splits/sec, denn der zählt auch das Anhängen einer neuen Seite am Ende aus Bild 3. Wir zählen die Logoperation LOP_DELETE_SPLIT; Teil 2 erklärt, was sie erfasst und was nicht.

Eine Wartungsentscheidung treffen

Aus den Bildern folgt keine feste Regel, sondern eine Reihenfolge von Fragen. Unser Vorschlag als Leitlinie:

Für einen ersten Arbeitsauftrag reicht ein einziger Index: den wichtigsten auswählen, aufschreiben, wie er gelesen und beschrieben wird, das Ziel festlegen und den Ausgangszustand erfassen. Teil 2 zeigt, wie so ein Vergleich aussieht.

Zusammenfassung

• Ein Index speichert Zeilen auf Seiten mit fester Größe; ein Wegweiser führt zur richtigen Seite.
• Seitendichte sagt, wie voll die Seiten im Mittel sind. Hohe Dichte hilft Scans.
• Der Schlüssel bestimmt die Zielseite einer neuen Zeile. Ist sie voll, kommt es zum Page Split.
• Fragmentierung heißt: Die Seiten liegen in der Datei nicht in Schlüsselreihenfolge. Die logische Ordnung des Index bleibt erhalten.
• Reserve hilft Inserts, wenn sie auf der Zielseite liegt. Der Durchschnitt verrät das nicht.
• REORGANIZE verdichtet und ordnet, schafft aber keine Reserve auf vollen Seiten; REBUILD baut sie mit dem Fillfactor neu auf.
• Statistiken wirken über den Plan, nicht über die Seiten.

Weiterlesen

• PSG: Teil 2 · Aufgeräumt, aber ohne Platz
• PSG: Teil 3 · Wenn der Index ohne Wartung wächst
• Jeff Moden: „Black Arts“ Index Maintenance #1
• Brent Ozar: You've Heard of Page Splits. Here's What They Really Mean.
• Microsoft: Index architecture and design guide