Wissen · Schreiblast

Optimize for Sequential Key: Was die Option am Indexende wirklich ändert

Viele Schreiber, ein Clustered Index auf einer IDENTITY-Spalte, und alle wollen auf dieselbe letzte Seite. OPTIMIZE_FOR_SEQUENTIAL_KEY regelt dort den Zugang neu. Im Lab senkte die Option die Latch-Wartezeit in jedem Versuch deutlich. Ob danach auch mehr Inserts pro Sekunde durchgingen, entschied ein anderer Teil des Systems: der Logflush beim Commit.

Stand 20.11.2026 · ca. 11 Minuten

Lab SQL Server 2022 · 2025SQL-Abfragen 3Thema PAGELATCH · Flow Control · WRITELOG
OPTIMIZE_FOR_SEQUENTIAL_KEY
SCHEMATISCH · MODELL NACH MICROSOFT1 · 3011 · 101 · 201301 · 401 · …1 …101 …201 …301 …letzte Seite401 … 437Flusssteuerungje Scheduler einer zum LatchPAGELATCH_EXStau direkt an der SeiteBTREE_INSERT_FLOW_CONTROLwarten vor dem LatchKonvoiHalter verliert die CPU, alle stehenSchreiberfügen Zeile für Zeile ein
Fertige Inserts je Zeit schematischOFF

ON: Die Flusssteuerung lässt je Scheduler einen Schreiber zum Latch. Davor wartet man auf BTREE_INSERT_FLOW_CONTROL, und es werden mehr Inserts fertig.

Kurz gesagt

  1. Das Problem heißt Last Page Insert Contention. Bei einem aufsteigenden Schlüssel landet jeder neue Datensatz auf der letzten Seite des Index. Viele gleichzeitige Schreiber warten dort auf PAGELATCH_EX.
  2. Die Option ändert nicht den Schlüssel, sondern die Reihenfolge. SQL Server lässt Schreiber vor dem Latch kontrolliert warten und bevorzugt die, die zügig fertig werden. Die neue Wartezeit heißt BTREE_INSERT_FLOW_CONTROL.
  3. Mehr Durchsatz gab es nur, wenn das Indexende der Engpass war. Mit schnellem Logpfad oder ohne Warten auf den Logflush stieg der Durchsatz bei 64 Schreibern um 35 bis 96 %. Mit regulären Commits auf SSD bestimmte der Logflush das Tempo, und die Option änderte am Durchsatz kaum etwas.
  4. Einzelne Schreiber können länger warten. In einem Versuch stieg der Durchsatz um 35 %, während die langsamsten 5 % der Schreibblöcke 3,5-mal so lange brauchten.
  5. Vorher messen, nachher messen. Abfrage 1 zeigt, ob die Inserts wirklich an der letzten Seite stauen. Abfrage 3 zeigt, ob eher das Log bremst. Entscheidend sind am Ende abgeschlossene Inserts pro Sekunde und die Latenz, die für die Anwendung zählt.

Alle Inserts wollen auf dieselbe Seite

Ein Clustered Index auf bigint IDENTITY, einer Sequenz oder einem Zeitstempel sortiert jeden neuen Datensatz ans Ende. Das ist für Lesen und Wartung meist günstig, denn der Index bleibt kompakt und fragmentiert kaum. Beim Schreiben unter hoher Parallelität wird es zum Nadelöhr: Alle Inserts brauchen dieselbe letzte Blattseite.

Ebene 2Ebene 1Ebene 0Blattseiten1 · 3011 · 101 · 201301 · 4011–100101–200201–300301–400letzte Blattseite401–437INSERT 438INSERT 439INSERT 440INSERT 441INSERT 442Alle Inserts wollen auf dieselbe Seite.Wenn die Seite voll ist301 · 401 · 501401–500voll501–…neue BlattseiteSQL Server legt eine neueBlattseite an und trägt sie mit501 in die Seite darüber ein.Auch dort entsteht kurzKonkurrenz.
1 · 3011 · 101 · 201301 · 4011–100…301–400letzte Blattseite401–437INSERT 438INSERT 439INSERT 440INSERT 441Alle Inserts wollen auf dieselbe Seite.Wenn die Seite voll ist301 · 401 · 501401–500voll501–…neue BlattseiteSQL Server legt eine neue Blattseite an und trägtsie mit 501 in die Seite darüber ein. Auch dortentsteht kurz Konkurrenz.

Schematische Zeichnung, Schlüssel und Seitengrößen vereinfacht.

Bei einem aufsteigenden Schlüssel landet jeder neue Datensatz auf der letzten Blattseite. Ist sie voll, kommt eine neue Seite hinzu, und die Seite darüber erhält einen Eintrag.

Damit zwei Threads eine Seite im Speicher nicht gleichzeitig verändern, schützt SQL Server sie mit einem Latch. Wer schreiben will, braucht ihn exklusiv und wartet sonst auf PAGELATCH_EX. Das ist kein Lock auf einer Zeile und kein Warten auf die Festplatte: PAGEIOLATCH_* wäre das Warten darauf, dass eine Seite vom Datenträger kommt. Ist die letzte Seite voll, legt SQL Server eine neue an und trägt sie in die Seite darüber ein. Auch dort entsteht dann kurz Konkurrenz.

Im Lab haben wir die Wartenden während der Last zu ihren Seiten aufgelöst. Sie standen auf letzten Blattseiten des Index (DATA_PAGE, Ebene 0, keine Folgeseite) und auf Indexseiten der Ebenen 1 und 2 desselben Index. Es war also genau das Bild aus dem Lehrbuch.

Was die Option umstellt

OPTIMIZE_FOR_SEQUENTIAL_KEY gibt es ab SQL Server 2019 und in Azure SQL. Die Option wird je Index gesetzt und ist standardmäßig aus. Pam Lahoud hat für das SQL-Server-Produktteam beschrieben, was sie bewirkt (Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY):

  • Ohne Option drängen sich alle Schreiber direkt am Latch. Bekommt ein Thread den Latch, verliert dann aber die CPU oder muss eine neue Seite anlegen, steht die ganze Schlange. Das nennt sie einen Konvoi.
  • Mit Option regelt SQL Server den Zugang vor dem Latch. Je Scheduler darf nur ein Thread den Latch anfordern, und Threads, die voraussichtlich schnell fertig werden, kommen bevorzugt dran.
  • Wer in dieser Vorstufe wartet, wartet auf BTREE_INSERT_FLOW_CONTROL. Der Schlüssel, die Seite und der Einfügepunkt bleiben gleich.

OFFohne OptionStandard

  1. INSERTnächster Schlüssel, Ziel ist die letzte Blattseite
  2. Keine Vorstufealle Schreiber gehen direkt zum Latch
  3. Latch anfordernexklusiv auf der letzten BlattseitePAGELATCH_EXviele warten gleichzeitig
  4. Zeile schreibenSeite im Speicher ändern, Latch freigeben
  5. Commitauf den Logflush warten (reguläre Commits)WRITELOGLogflush
  6. Insert fertig

ONOPTIMIZE_FOR_SEQUENTIAL_KEY = ONje Index gesetzt

  1. INSERTnächster Schlüssel, Ziel ist die letzte Blattseite
  2. Flusssteuerungje Scheduler einer zum Latch, wer schnell fertig wird, kommt eher dranBTREE_INSERT_FLOW_CONTROLneuer Warteort vor dem Latch
  3. Latch anfordernnur noch wenige gleichzeitigPAGELATCH_EXkurz
  4. Zeile schreibenSeite im Speicher ändern, Latch freigeben
  5. Commitauf den Logflush warten (reguläre Commits)WRITELOGLogflush
  6. Insert fertig

Schematisch nach der Beschreibung des SQL-Server-Produktteams (Pam Lahoud, 2019). Mit verzögerter Dauerhaftigkeit kehrt der Commit vor dem Logflush zurück, dann entfällt das Warten auf WRITELOG.

Wo ein Insert am Indexende warten kann, ohne und mit OPTIMIZE_FOR_SEQUENTIAL_KEY.

Daraus folgt schon die Erwartung: Die Option kann den Durchsatz erhöhen, wenn Threads am Latch Zeit verlieren. Sie macht aber keinen Insert schneller, der auf etwas anderes wartet. Und sie verteilt die Wartezeit ungleich, weil sie bewusst manche Threads vorzieht.

Das Ergebnis ist in der Praxis nicht immer eindeutig. Erik Darling hat die Option 2019 zweimal nachgestellt. In seinem ersten Lauf mit 150 Verbindungen fielen die Latch-Waits deutlich, die Last lief aber rund 19 statt 17 Sekunden, und WRITELOG blieb der größte Wait. Im zweiten Lauf mit 500 Verbindungen und anderer VM brauchte sie mit Option rund 24 statt 35 Sekunden (Teil 1, Teil 2). Genau diese Frage haben wir weiterverfolgt: Wovon hängt ab, ob aus weniger Latch-Warten mehr Durchsatz wird?

Der Versuch

So sieht die Testtabelle aus, und so schreibt jeder Schreiber:

CREATE TABLE dbo.Appends (    id      bigint IDENTITY,    payload char(500) NOT NULL,    CONSTRAINT PK_Appends PRIMARY KEY CLUSTERED (id)        WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)   -- im Vergleichsarm OFF); -- je Schreiber, 64 Schreiber gleichzeitigDECLARE @i int = 0;WHILE @i < 2500BEGIN    INSERT dbo.Appends (payload) VALUES ('x');   -- jede Zeile ein eigener Commit    SET @i += 1;END;

Gemessen haben wir abgeschlossene Inserts pro Sekunde, die Wartezeiten der Instanz und, um die Fairness zu sehen, die Zeit für je 128 aufeinanderfolgende Inserts eines Schreibers.

Um zu trennen, was der Index und was das Log beiträgt, lief derselbe Vergleich mit verschiedenen Logpfaden:

  • Reguläre Commits, Log auf der Daten-SSD: so, wie die meisten Systeme laufen. Jeder Commit wartet, bis sein Logeintrag auf der SSD liegt.
  • Reguläre Commits, Log auf eigener SSD: dieselbe Last, das Log auf einem zweiten physischen Laufwerk.
  • Log im RAM: die Logdatei in einem Docker-tmpfs. Commits warten weiter auf den Flush, der aber kaum Zeit kostet. Der Inhalt ist flüchtig, das ist ein reiner Laboraufbau.
  • Verzögerte Dauerhaftigkeit (DELAYED_DURABILITY = FORCED): Der Commit kehrt zurück, bevor das Log geschrieben ist. Das ist eine Diagnosevariante, um das Indexende freizulegen, keine Empfehlung für den Betrieb.

Ergebnis 1: Wenn der Logflush bremst, bringt die Option kaum etwas

Bremst der Logflush, ändert die Option am Durchsatz kaum etwas. Ist das Log schnell oder wird nicht auf den Flush gewartet, legt ON deutlich zu.

Engpass: Logflush (WRITELOG)

Reguläre Commits, Log auf der Daten-SSD

64 Schreiber, SQL Server 2025. Jeder Commit wartet auf den Logflush. Median aus 3 (Hauptlauf) bzw. 4 Fenstern (Wiederholung).

HauptlaufON −6 %

OFF4.184
ON3.931

WiederholungON +3 %

OFF4.208
ON4.342

Engpass: Logflush (WRITELOG)

Log auf eigener SSD

64 Schreiber, SQL Server 2025, reguläre Commits. Logdatei auf einem zweiten physischen Laufwerk. Median aus 4 Fenstern.

Reguläre CommitsON −3 %

OFF7.714
ON7.506

Engpass: Indexende (PAGELATCH_EX)

Log im RAM (flüchtig)

64 Schreiber, SQL Server 2025. Der Commit wartet weiter auf den Flush, der aber kaum Zeit kostet. Reiner Laboraufbau. Median aus 4 Fenstern.

Log im RAMON +35 %

OFF8.073
ON10.863

Vergleich: Log auf eigener SSD, gleiche ReiheON +5 %

OFF7.466
ON7.854

Engpass: Indexende (PAGELATCH_EX)

Verzögerte Dauerhaftigkeit (Diagnose)

64 Schreiber, DELAYED_DURABILITY = FORCED: Der Commit kehrt vor dem Logflush zurück. Diagnosevariante, keine Empfehlung. Median aus 3 Fenstern.

SQL Server 2025ON +35 %

OFF8.423
ON11.385

SQL Server 2022ON +96 %

OFF4.709
ON9.243

Abgeschlossene Inserts pro Sekunde, gemeinsamer Maßstab ab null. Median abwechselnder OFF/ON-Fenster je Variante; synthetischer Clustered Index auf bigint IDENTITY, vier Scheduler, SQL Server 2025 CU10 und 2022 CU27. Die Vergleiche gelten jeweils innerhalb einer Reihe.

Abgeschlossene Inserts pro Sekunde ohne und mit Option bei 64 Schreibern, gemessen im PSG-Lab.

Mit regulären Commits und dem Log auf der Daten-SSD lagen OFF und ON nah beieinander. Im Hauptlauf bei 64 Schreibern waren es 4.184 gegen 3.931 Inserts pro Sekunde, im Median also 6 % weniger mit Option. In zwei von drei Paaren war OFF schneller, in einem ON. In der späteren Wiederholung lag ON mit 4.342 gegen 4.208 knapp vorn. Beides liegt im Rauschen dieses Aufbaus.

Die Wartezeiten der Wiederholung zeigen, warum. Mit Option fiel PAGELATCH_EX von rund 1 ms auf 0,1 ms je Insert. Dafür kamen 0,8 ms BTREE_INSERT_FLOW_CONTROL hinzu. Den größten Teil machte aber WRITELOG aus: rund 5 ms je Insert. Die Schreiber standen vor allem am Logflush. Wer die Reihenfolge am Latch verbessert, ändert daran nichts.

Das zweite Laufwerk bestätigte das. In derselben Messreihe stieg der Durchsatz mit dem Log auf eigener SSD gegenüber der Daten-SSD um 83 % ohne und um 73 % mit Option, auf 7.714 und 7.506 Inserts pro Sekunde. Der Ort der Logdatei hatte damit den mit Abstand größten Effekt. Zwischen OFF und ON blieb es dagegen auch hier knapp: Drei von vier Paaren lagen innerhalb von 0,6 %, ein ON-Lauf war 24 % langsamer.

Ergebnis 2: Wenn das Log schnell ist, wird das Indexende zum Engpass

Mit dem Log im RAM sank WRITELOG auf 0,3 ms je Insert. Jetzt rückte das Indexende in den Vordergrund: Ohne Option stieg PAGELATCH_EX von 0,9 auf 4,6 ms je Insert. Die Schreiber kamen schneller vom Commit zurück und standen dafür öfter gleichzeitig an der letzten Seite.

Genau hier half die Option. Der Durchsatz stieg von 8.073 auf 10.863 Inserts pro Sekunde, also um 35 %, und das in allen vier Blöcken (zwischen +26 und +42 %). Die Vergleichsläufe mit dem Log auf eigener SSD lagen in derselben Reihe bei 7.466 ohne und 7.854 mit Option. PAGELATCH_EX fiel auf 0,2 ms je Insert, 4,3 ms wanderten in die Flusssteuerung.

WRITELOG LogflushPAGELATCH_EX Latch an der letzten SeiteBTREE_INSERT_FLOW_CONTROL Flusssteuerung

Log auf der Daten-SSD

OFF6,14 ms

WRITELOG 5,146PAGELATCH_EX 0,992BTREE_INSERT_FLOW_CONTROL 0

ON5,77 ms

WRITELOG 4,885PAGELATCH_EX 0,117BTREE_INSERT_FLOW_CONTROL 0,772

Log auf eigener SSD

OFF3,30 ms

WRITELOG 2,537PAGELATCH_EX 0,761BTREE_INSERT_FLOW_CONTROL 0

ON3,45 ms

WRITELOG 2,558PAGELATCH_EX 0,139BTREE_INSERT_FLOW_CONTROL 0,753

Log im RAM (flüchtig)

OFF4,91 ms

WRITELOG 0,315PAGELATCH_EX 4,597BTREE_INSERT_FLOW_CONTROL 0

ON4,84 ms

WRITELOG 0,320PAGELATCH_EX 0,184BTREE_INSERT_FLOW_CONTROL 4,340

Millisekunden je Insert: Wartezeiten über alle Tasks summiert und durch die Zahl der Inserts geteilt, keine Zerlegung eines einzelnen Inserts. 64 Schreiber, reguläre Commits, SQL Server 2025, Median aus vier Fenstern je Variante. Beim Log im RAM zusätzlich 0,232 bzw. 0,284 ms PREEMPTIVE_OS_FLUSHFILEBUFFERS, nicht dargestellt.

Wo die Zeit hingeht: Mit Option wandert die Latch-Wartezeit in die Flusssteuerung, WRITELOG bleibt, wo es war. Gemessen im PSG-Lab.

Mit verzögerter Dauerhaftigkeit, also ganz ohne Warten auf den Flush, zeigte sich dasselbe Muster bei jeder Schreiberzahl:

VersionSchreiberInserts/s OFFInserts/s ONÄnderung
SQL Server 2025420.98428.302+35 %
SQL Server 2025169.38712.928+38 %
SQL Server 2025648.42311.385+35 %
SQL Server 2022644.7099.243+96 %

Bei 64 Schreibern auf SQL Server 2025 fiel die summierte PAGELATCH_EX-Wartezeit von 846 auf 34 Sekunden, rund 572 Sekunden Flusssteuerung kamen hinzu. Auffällig: Schon vier Schreiber auf vier Schedulern profitierten. Microsoft beschreibt den Nutzen typischerweise bei hoher Parallelität im Verhältnis zur Zahl der Scheduler. Bei nur einem Schreiber gab es keinen Nutzen: 29.229 gegen 27.862 Inserts pro Sekunde, und weder Flusssteuerung noch Latch-Wartezeit am Index.

Den Unterschied zwischen SQL Server 2022 und 2025 sollte man nicht überbewerten. Jeder Vergleich lief auf derselben Instanz mit demselben Build. Ohne Option lag 2022 aber deutlich niedriger als 2025, und woran das liegt, haben wir nicht untersucht.

Durchsatz ist nicht Latenz

Die Option bevorzugt Threads, die schnell fertig werden. Das erhöht den Durchsatz über alle Schreiber, kann aber einzelne länger warten lassen. Im Lab war das deutlich zu sehen.

Verzögerte Dauerhaftigkeit (Diagnose)

SQL Server 2025, 64 Schreiber, DELAYED_DURABILITY = FORCED

Durchsatz ON +35 %

OFF8.423
ON11.385

Zeit für 128 Inserts eines Schreibers

Median

OFF970 ms
ON46 ms

p95: die langsamsten 5 % der Blöcke

OFF1,02 s
ON3,56 s

Mehr Durchsatz, der typische Block ist schneller fertig, aber die langsamsten brauchen 3,5-mal so lange.

Log im RAM (flüchtig)

SQL Server 2025, 64 Schreiber, reguläre Commits

Durchsatz ON +35 %

OFF8.073
ON10.863

Zeit für 128 Inserts eines Schreibers

Median: in dieser Reihe nicht ausgewertet

p95: die langsamsten 5 % der Blöcke

OFF1,06 s
ON0,88 s

Mehr Durchsatz, und auch die langsamsten Blöcke werden schneller fertig.

Ein Block sind 128 aufeinanderfolgende Inserts eines Schreibers. Getrennte Skalen ab null für Inserts pro Sekunde und Sekunden je Block. Median der Messfenster je Variante. Verzögerte Dauerhaftigkeit ändert die Zusage zur Dauerhaftigkeit, das RAM-Log ist flüchtig: beides nur Laborwerkzeuge.

Mehr Durchsatz kann ungleichere Wartezeiten bedeuten, muss aber nicht. Gemessen im PSG-Lab.

Mit verzögerter Dauerhaftigkeit und 64 Schreibern auf SQL Server 2025 fiel der Median für 128 aufeinanderfolgende Inserts eines Schreibers von 970 auf 46 ms. Die langsamsten 5 % der Blöcke (p95) brauchten aber 3,56 statt 1,02 Sekunden. Manche Schreiber flogen durch, andere standen. Mit dem Log im RAM war es umgekehrt: Dort fiel auch der p95 von 1,06 auf 0,88 Sekunden.

Für eine Entscheidung heißt das: Wer viele unabhängige Ereignisse protokolliert, achtet vor allem auf den Gesamtdurchsatz. Wartet dagegen ein Anwender oder eine Schnittstelle auf jeden einzelnen Insert, zählt die Verteilung. Beides gehört in den Vergleich.

Die eigene Last prüfen

Bevor die Option gesetzt wird, sollte feststehen, dass die Inserts tatsächlich am Indexende stauen. Abfrage 1 macht eine Momentaufnahme während der Last und löst jeden PAGELATCH-Wartenden zu Datenbank, Objekt, Index und Seite auf. Das Verfahren folgt Microsofts Anleitung zu PAGELATCH_EX.

Abfrage 1: Wo stauen sich die Inserts gerade? · nur lesend · geprüft auf SQL Server 2019–2025
/* PSG · OFSK · Abfrage 1: Wo stauen sich die Inserts gerade?   Momentaufnahme während der Last: wartende Requests auf PAGELATCH_*, aufgelöst zu Datenbank, Objekt, Index und Seite.   Liest nur Systemsichten. Ändert nichts. Ab SQL Server 2019 (sys.fn_PageResCracker, sys.dm_db_page_info).   Rechte: VIEW SERVER STATE (ab SQL Server 2022 genügt VIEW SERVER PERFORMANCE STATE); damit ist auch die Seitenauflösung   abgedeckt, ein eigenes Datenbankrecht ist nicht nötig. Der Anmelder braucht aber einen Benutzer in der betroffenen   Datenbank: Fehlt er, bricht die Abfrage mit Fehler 916 ab, sobald dort ein Latch-Wartender steht. Darf er die Metadaten   nicht sehen (z. B. ohne VIEW DEFINITION), bleibt die Spalte objekt NULL.   Im Abstand weniger Sekunden mehrmals ausführen: Eine einzelne Aufnahme kann leer sein.   OPTION (MAXDOP 1): Die Diagnose soll keine parallelen Worker belegen, gerade wenn der Server unter Druck steht. */SELECT    r.wait_type                                                     AS wait_typ,    COUNT(*)                                                        AS wartende,    MAX(r.wait_time)                                                AS max_wait_ms,    DB_NAME(pc.db_id)                                               AS datenbank,    OBJECT_SCHEMA_NAME(pi.object_id, pc.db_id) + N'.'        + OBJECT_NAME(pi.object_id, pc.db_id)                       AS objekt,    pi.index_id                                                     AS index_id,    pi.page_type_desc                                               AS seitentyp,    pi.page_level                                                   AS ebene,    CONCAT(pc.file_id, N':', pc.page_id)                            AS seite,    CASE WHEN pi.page_level = 0 AND pi.next_page_page_id = 0         THEN N'ja' ELSE N'nein' END                                AS letzte_blattseiteFROM sys.dm_exec_requests AS rCROSS APPLY sys.fn_PageResCracker(r.page_resource) AS pcCROSS APPLY sys.dm_db_page_info(pc.db_id, pc.file_id, pc.page_id, 'DETAILED') AS piWHERE r.wait_type LIKE N'PAGELATCH%'GROUP BY r.wait_type, pc.db_id, pi.object_id, pi.index_id, pi.page_type_desc, pi.page_level,         pc.file_id, pc.page_id, pi.next_page_page_idORDER BY wartende DESC, max_wait_ms DESCOPTION (MAXDOP 1);

Im Abfragetest auf SQL Server 2025 mit 32 Schreibern traf etwa jede vierte Aufnahme Wartende, etwa so (gekürzt):

Beispielausgabe
wait_typ      wartende  datenbank  objekt       index_id  seitentyp   ebene  seite     letzte_blattseite
PAGELATCH_EX  4         OfskTest   dbo.Appends  1         DATA_PAGE   0      1:110674  ja
PAGELATCH_EX  7         OfskTest   dbo.Appends  1         INDEX_PAGE  1      1:111559  nein

Typisch für Last Page Insert Contention sind viele Wartende auf derselben Seite eines Index, oft mit letzte_blattseite = ja, dazu Seiten auf Ebene 1 oder 2 desselben Index. Stehen die Wartenden dagegen auf verstreuten Seiten oder in der TempDB, ist es ein anderes Problem. Dann hilft die Option nicht.

Abfrage 2 zeigt in der betroffenen Datenbank, welche Indizes sich über die Zeit am meisten Latch-Wartezeit angesammelt haben, mit welcher Schlüsselspalte sie beginnen und ob die Option schon gesetzt ist.

Abfrage 2: Welche Indizes warten auf Seitenlatches? · nur lesend · geprüft auf SQL Server 2019–2025
/* PSG · OFSK · Abfrage 2: Welche Indizes warten auf Seitenlatches, und ist die Option gesetzt?   In der betroffenen Datenbank ausführen. Zeigt je Index die Latch-Wartezeit, Inserts, neue Blattseiten,   die erste Schlüsselspalte und den Wert von OPTIMIZE_FOR_SEQUENTIAL_KEY.   Die Zähler laufen, seit die Metadaten des Index im Speicher liegen, und gehen bei einem Neustart verloren.   Liest nur Systemsichten. Ändert nichts. Ab SQL Server 2019 (Spalte optimize_for_sequential_key).   Rechte: VIEW DATABASE STATE (ab SQL Server 2022 genügt VIEW DATABASE PERFORMANCE STATE) und Sicht auf die Metadaten   der Tabellen (z. B. VIEW DEFINITION); ohne Metadatenrecht bleibt das Ergebnis leer.   OPTION (MAXDOP 1): Die Diagnose soll keine parallelen Worker belegen. */SELECT TOP (20)    OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id) AS objekt,    i.name                                                          AS index_name,    i.type_desc                                                     AS indexart,    k.name                                                          AS erste_schluesselspalte,    TYPE_NAME(k.user_type_id)                                       AS datentyp,    k.is_identity                                                   AS ist_identity,    i.optimize_for_sequential_key                                   AS ofsk,    s.blatt_inserts,    s.neue_blattseiten,    s.latch_waits,    s.latch_wait_ms,    CAST(1.0 * s.latch_wait_ms / NULLIF(s.latch_waits, 0) AS decimal(10, 2)) AS ms_je_waitFROM (SELECT ios.object_id, ios.index_id,             SUM(ios.leaf_insert_count)        AS blatt_inserts,             SUM(ios.leaf_allocation_count)    AS neue_blattseiten,             SUM(ios.page_latch_wait_count)    AS latch_waits,             SUM(ios.page_latch_wait_in_ms)    AS latch_wait_ms      FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios      GROUP BY ios.object_id, ios.index_id) AS sJOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_idOUTER APPLY (SELECT c.name, c.user_type_id, c.is_identity             FROM sys.index_columns AS ic             JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id             WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal = 1) AS kWHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1  AND s.latch_waits > 0ORDER BY s.latch_wait_ms DESCOPTION (MAXDOP 1);

Nach der Last zeigte Abfrage 2 im Abfragetest diese Zeile (gekürzt, Indexname gekürzt):

Beispielausgabe
objekt       index_name    erste_schluesselspalte  datentyp  ist_identity  ofsk  blatt_inserts  neue_blattseiten  latch_waits  latch_wait_ms  ms_je_wait
dbo.Appends  PK__Appends…  id                      bigint    1             1     217382         17123             289212       191719         0.66

Abfrage 3 beantwortet die zweite Frage aus dem Lab: Steht die Last eher am Log oder am Indexende? Sie zeigt die drei Warteorte instanzweit seit dem letzten Start. Aussagekräftiger ist die Differenz zweier Aufnahmen über ein Lastfenster, etwa vor und nach einer Änderung.

Abfrage 3: Bremst eher das Log oder das Indexende? · nur lesend · geprüft auf SQL Server 2019–2025
/* PSG · OFSK · Abfrage 3: Bremst eher das Log oder das Indexende?   Instanzweite Wartezeit seit dem letzten Start (oder seit dem letzten Leeren der Statistik) für die drei Warteorte,   um die es beim Einfügen am Indexende geht. Für einen Vergleich vor und nach einer Änderung zweimal ausführen   und die Differenz bilden. Liest nur. Ändert nichts. BTREE_INSERT_FLOW_CONTROL erscheint ab SQL Server 2019,   sobald die Option an einem Index greift.   Rechte: VIEW SERVER STATE (ab SQL Server 2022 genügt VIEW SERVER PERFORMANCE STATE).   OPTION (MAXDOP 1): Die Diagnose soll keine parallelen Worker belegen. */SELECT    ws.wait_type                                                    AS wait_typ,    CASE ws.wait_type        WHEN N'WRITELOG'                  THEN N'Commit wartet auf den Logflush'        WHEN N'PAGELATCH_EX'              THEN N'Warten auf exklusiven Zugriff auf eine Seite im Speicher'        WHEN N'BTREE_INSERT_FLOW_CONTROL' THEN N'Warten in der Flusssteuerung von OPTIMIZE_FOR_SEQUENTIAL_KEY'    END                                                             AS bedeutung,    ws.waiting_tasks_count                                          AS anzahl,    ws.wait_time_ms                                                 AS wartezeit_ms,    CAST(1.0 * ws.wait_time_ms / NULLIF(ws.waiting_tasks_count, 0) AS decimal(12, 2)) AS ms_je_wait,    CAST(100.0 * ws.wait_time_ms / NULLIF(SUM(ws.wait_time_ms) OVER (), 0) AS decimal(5, 1)) AS anteil_prozentFROM sys.dm_os_wait_stats AS wsWHERE ws.wait_type IN (N'WRITELOG', N'PAGELATCH_EX', N'BTREE_INSERT_FLOW_CONTROL')ORDER BY ws.wait_time_ms DESCOPTION (MAXDOP 1);

Liegt WRITELOG weit vorn, ist das Log der erste Hebel: schnellerer Speicher für die Logdatei, weniger Commits durch zusammengefasste Inserts, oder eine Prüfung, ob die Anwendung wirklich jede Zeile einzeln bestätigen muss. Liegt PAGELATCH_EX vorn und zeigt Abfrage 1 die letzte Seite, ist die Option ein Kandidat.

Einschalten, vergleichen, zurücknehmen

Die Option lässt sich beim Anlegen setzen oder an einem bestehenden Index ändern. ALTER INDEX … SET baut den Index dabei nicht neu auf.

-- am bestehenden Index einschalten (kein Rebuild)ALTER INDEX PK_Appends ON dbo.Appends SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON); -- prüfenSELECT name, optimize_for_sequential_keyFROM sys.indexesWHERE object_id = OBJECT_ID(N'dbo.Appends'); -- zurücknehmenALTER INDEX PK_Appends ON dbo.Appends SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF);

Im Lab änderte das ALTER INDEX den Katalogwert am bestehenden Index sofort von 1 auf 0. Für den Vergleich gilt, was auch im Lab galt:

  • Unter der echten Last und mit den echten Commit-Einstellungen messen. Verzögerte Dauerhaftigkeit nur einzuschalten, um die Option gut aussehen zu lassen, verfälscht das Bild und ändert die Zusage, dass bestätigte Transaktionen erhalten bleiben.
  • Abgeschlossene Inserts pro Sekunde und eine zur Anwendung passende Latenz vergleichen, am besten mit mehreren Wiederholungen. Einzelne Läufe streuten im Lab um bis zu 24 %.
  • Die Wartezeiten vorher und nachher festhalten (Abfrage 3 als Differenz). Weniger PAGELATCH_EX allein ist noch kein Erfolg.

Andere Wege gegen Last Page Insert Contention, etwa ein anderer Schlüssel, Hash-Partitionierung oder In-Memory OLTP, ändern Tabelle oder Anwendung und haben eigene Folgen für Lesezugriffe und Wartung. Microsoft stellt sie in derselben Anleitung vor. Wir haben sie hier nicht gemessen.

Was der Versuch zeigt und was nicht

Wer OFSK prüft, braucht also zwei Antworten: Kommen insgesamt mehr Inserts durch? Und wie lange warten die Schreiber, für die Latenz zählt? Steht der Commit am Logflush, hilft die Option kaum. Stauen die Inserts wirklich an der letzten Seite, kann sie viel bringen.

Quellen

Weiterlesen: Unter Last langsam · Logflush je Commit unter Linux und Docker · Transaktionslog voll · Indexwartung verstehen