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.
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
- 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. - 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. - 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.
- 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.
- 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.
Schematische Zeichnung, Schlüssel und Seitengrößen vereinfacht.
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
- INSERTnächster Schlüssel, Ziel ist die letzte Blattseite
- Keine Vorstufealle Schreiber gehen direkt zum Latch
- Latch anfordernexklusiv auf der letzten Blattseite
PAGELATCH_EXviele warten gleichzeitig - Zeile schreibenSeite im Speicher ändern, Latch freigeben
- Commitauf den Logflush warten (reguläre Commits)
WRITELOGLogflush - Insert fertig
ONOPTIMIZE_
- INSERTnächster Schlüssel, Ziel ist die letzte Blattseite
- Flusssteuerungje Scheduler einer zum Latch, wer schnell fertig wird, kommt eher dran
BTREE_INSERT_FLOW_CONTROLneuer Warteort vor dem Latch - Latch anfordernnur noch wenige gleichzeitig
PAGELATCH_EXkurz - Zeile schreibenSeite im Speicher ändern, Latch freigeben
- Commitauf den Logflush warten (reguläre Commits)
WRITELOGLogflush - 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.
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 %
WiederholungON +3 %
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 %
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 %
Vergleich: Log auf eigener SSD, gleiche ReiheON +5 %
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 %
SQL Server 2022ON +96 %
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.
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 FlusssteuerungLog auf der Daten-SSD
WRITELOG 5,146PAGELATCH_EX 0,992BTREE_INSERT_FLOW_CONTROL 0
WRITELOG 4,885PAGELATCH_EX 0,117BTREE_INSERT_FLOW_CONTROL 0,772
Log auf eigener SSD
WRITELOG 2,537PAGELATCH_EX 0,761BTREE_INSERT_FLOW_CONTROL 0
WRITELOG 2,558PAGELATCH_EX 0,139BTREE_INSERT_FLOW_CONTROL 0,753
Log im RAM (flüchtig)
WRITELOG 0,315PAGELATCH_EX 4,597BTREE_INSERT_FLOW_CONTROL 0
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.
Mit verzögerter Dauerhaftigkeit, also ganz ohne Warten auf den Flush, zeigte sich dasselbe Muster bei jeder Schreiberzahl:
| Version | Schreiber | Inserts/s OFF | Inserts/s ON | Änderung |
|---|---|---|---|---|
| SQL Server 2025 | 4 | 20.984 | 28.302 | +35 % |
| SQL Server 2025 | 16 | 9.387 | 12.928 | +38 % |
| SQL Server 2025 | 64 | 8.423 | 11.385 | +35 % |
| SQL Server 2022 | 64 | 4.709 | 9.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 %
Zeit für 128 Inserts eines Schreibers
Median
p95: die langsamsten 5 % der Blöcke
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 %
Zeit für 128 Inserts eines Schreibers
Median: in dieser Reihe nicht ausgewertet
p95: die langsamsten 5 % der Blöcke
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.
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.
/* 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):
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 neinTypisch 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.
/* 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):
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.66Abfrage 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.
/* 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_EXallein 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
- Pam Lahoud, Microsoft: Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY (2019)
- Microsoft Learn: ALTER INDEX, Resolve last-page insert PAGELATCH_EX contention, sys.dm_db_index_operational_stats, Control transaction durability
- Erik Darling: OPTIMIZE FOR SEQUENTIAL KEY In SQL Server 2019, How OPTIMIZE FOR SEQUENTIAL KEY Improves Highly Concurrent Insert Performance
Weiterlesen: Unter Last langsam · Logflush je Commit unter Linux und Docker · Transaktionslog voll · Indexwartung verstehen