Optimize for sequential key: what the option really changes at the end of the index
Many writers, a clustered index on an IDENTITY column, and all of them want the same last page. OPTIMIZE_FOR_SEQUENTIAL_KEY rearranges how they get access there. In the lab, the option clearly reduced latch wait time in every test. Whether more inserts per second went through afterwards was decided by another part of the system: the log flush at commit.
ON: flow control lets one writer per scheduler request the latch. Before that, writers wait on BTREE_INSERT_FLOW_CONTROL, and more inserts get done.
In short
- The problem is called last page insert contention. With an ascending key, every new row lands on the last page of the index. Many concurrent writers wait there on
PAGELATCH_EX. - The option doesn't change the key, it changes the order. SQL Server makes writers wait in a controlled way before the latch and favors those that finish quickly. The new wait is called
BTREE_INSERT_FLOW_CONTROL. - Throughput only rose when the end of the index was the bottleneck. With a fast log path or without waiting for the log flush, throughput with 64 writers rose by 35 to 96%. With regular commits on SSD, the log flush set the pace, and the option changed throughput very little.
- Individual writers can wait longer. In one test, throughput rose by 35% while the slowest 5% of write blocks took 3.5 times as long.
- Measure before, measure after. Query 1 shows whether the inserts really pile up at the last page. Query 3 shows whether the log is the more likely brake. In the end, what counts is completed inserts per second and the latency that matters to the application.
All inserts want the same page
A clustered index on bigint IDENTITY, a sequence or a timestamp sorts every new row to the end. For reading and maintenance, that is usually favorable, because the index stays compact and hardly fragments. For writing under high concurrency, it becomes a bottleneck: all inserts need the same last leaf page.
Schematic drawing, keys and page sizes simplified.
To keep two threads from changing a page in memory at the same time, SQL Server protects it with a latch. Whoever wants to write needs it exclusively and otherwise waits on PAGELATCH_EX. That is not a lock on a row and not a wait for the disk: PAGEIOLATCH_* would be the wait for a page to come from disk. When the last page is full, SQL Server allocates a new one and adds it to the page above. Contention can briefly arise there as well.
In the lab, we resolved the waiters to their pages during the load. They sat on last leaf pages of the index (DATA_PAGE, level 0, no next page) and on index pages at levels 1 and 2 of the same index. So it was exactly the textbook picture.
What the option changes
OPTIMIZE_FOR_SEQUENTIAL_KEY exists from SQL Server 2019 and in Azure SQL. The option is set per index and is off by default. Pam Lahoud described for the SQL Server product team what it does (Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY):
- Without the option, all writers crowd directly at the latch. If a thread gets the latch but then loses the CPU or has to allocate a new page, the whole queue stands still. She calls this a convoy.
- With the option, SQL Server regulates access before the latch. Only one thread per scheduler may request the latch, and threads that are expected to finish quickly are served first.
- Whoever waits in this preliminary stage waits on
BTREE_INSERT_FLOW_CONTROL. The key, the page and the insertion point stay the same.
OFFwithout the optiondefault
- INSERTnext key, target is the last leaf page
- No pre-stageevery writer goes straight to the latch
- Request the latchexclusive, on the last leaf page
PAGELATCH_EXmany wait at once - Write the rowchange the page in memory, release the latch
- Commitwait for the log flush (regular commits)
WRITELOGlog flush - Insert done
ONOPTIMIZE_
- INSERTnext key, target is the last leaf page
- Flow controlone per scheduler to the latch, likely fast finishers go first
BTREE_INSERT_FLOW_CONTROLnew wait before the latch - Request the latchonly a few at once
PAGELATCH_EXshort - Write the rowchange the page in memory, release the latch
- Commitwait for the log flush (regular commits)
WRITELOGlog flush - Insert done
Schematic after the description by the SQL Server product team (Pam Lahoud, 2019). With delayed durability the commit returns before the log flush, so the WRITELOG wait disappears.
The expectation follows from this: the option can raise throughput if threads lose time at the latch. But it doesn't make any insert faster that is waiting for something else. And it distributes the wait time unevenly, because it deliberately favors some threads.
In practice, the result is not always clear-cut. Erik Darling reproduced the option twice in 2019. In his first run with 150 connections, latch waits fell clearly, but the load took about 19 instead of 17 seconds, and WRITELOG remained the largest wait. In the second run with 500 connections and a different VM, it took about 24 instead of 35 seconds with the option (part 1, part 2). That is exactly the question we followed up on: what determines whether fewer latch waits turn into more throughput?
The test
Test setup: SQL Server 2025 CU10 (17.0.5035.5) and, as a cross-check, SQL Server 2022 CU27 (16.0.4295.3), each in a container with four visible schedulers and 4 GB
max server memory. One table with a clustered index onbigint IDENTITYand 500 bytes of payload, 10,000 starting rows, created anew for every measurement window. Each writer inserts single rows in a loop in autocommit. Between the arms of a comparison, only the option changes. OFF and ON run alternately, three to four windows per variant, and the median is reported. Synthetic data, Linux container. The results apply to this index and this load.
This is what the test table looks like, and this is how each writer writes:
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) -- OFF in the comparison arm); -- per writer, 64 writers at the same timeDECLARE @i int = 0;WHILE @i < 2500BEGIN INSERT dbo.Appends (payload) VALUES ('x'); -- every row is its own commit SET @i += 1;END;We measured completed inserts per second, the instance's wait times and, to see fairness, the time for every 128 consecutive inserts of a writer.
To separate what the index and what the log contribute, the same comparison ran with different log paths:
- Regular commits, log on the data SSD: the way most systems run. Every commit waits until its log record is on the SSD.
- Regular commits, log on its own SSD: the same load, with the log on a second physical drive.
- Log in RAM: the log file in a Docker
tmpfs. Commits still wait for the flush, which now costs hardly any time. The content is volatile, so this is a pure lab setup. - Delayed durability (
DELAYED_DURABILITY = FORCED): the commit returns before the log is written. This is a diagnostic variant to expose the end of the index, not a recommendation for production.
Result 1: When the log flush is the brake, the option does little
When the log flush is the brake, the option barely changes throughput. When the log is fast, or nobody waits for the flush, ON gains clearly.
Bottleneck: log flush (WRITELOG)
Regular commits, log on the data SSD
64 writers, SQL Server 2025. Every commit waits for the log flush. Median of 3 (main run) or 4 windows (repeat run).
Main runON −6%
Repeat runON +3%
Bottleneck: log flush (WRITELOG)
Log on its own SSD
64 writers, SQL Server 2025, regular commits. Log file on a second physical drive. Median of 4 windows.
Regular commitsON −3%
Bottleneck: end of the index (PAGELATCH_EX)
Log in RAM (volatile)
64 writers, SQL Server 2025. The commit still waits for the flush, which costs almost nothing. Lab-only setup. Median of 4 windows.
Log in RAMON +35%
Comparison: log on its own SSD, same seriesON +5%
Bottleneck: end of the index (PAGELATCH_EX)
Delayed durability (diagnostic)
64 writers, DELAYED_DURABILITY = FORCED: the commit returns before the log flush. Diagnostic variant, not a recommendation. Median of 3 windows.
SQL Server 2025ON +35%
SQL Server 2022ON +96%
Completed inserts per second, shared scale starting at zero. Median of alternating OFF/ON windows per variant; synthetic clustered index on bigint IDENTITY, four schedulers, SQL Server 2025 CU10 and 2022 CU27. Each comparison holds within its own series.
With regular commits and the log on the data SSD, OFF and ON were close together. In the main run with 64 writers, it was 4,184 versus 3,931 inserts per second, so a median of 6% fewer with the option. In two of three pairs, OFF was faster, in one ON. In the later repetition, ON was slightly ahead with 4,342 versus 4,208. Both are within the noise of this setup.
The wait times of the repetition show why. With the option, PAGELATCH_EX fell from about 1 ms to 0.1 ms per insert. In return, 0.8 ms of BTREE_INSERT_FLOW_CONTROL was added. But the largest part was WRITELOG: about 5 ms per insert. The writers stood mostly at the log flush. Improving the order at the latch changes nothing about that.
The second drive confirmed it. In the same test series, moving the log from the data SSD to its own SSD raised throughput by 83% without and by 73% with the option, to 7,714 and 7,506 inserts per second. The location of the log file therefore had by far the largest effect. Between OFF and ON, it remained close here as well: three of four pairs were within 0.6%, and one ON run was 24% slower.
Result 2: When the log is fast, the end of the index becomes the bottleneck
With the log in RAM, WRITELOG dropped to 0.3 ms per insert. Now the end of the index moved to the foreground: without the option, PAGELATCH_EX rose from 0.9 to 4.6 ms per insert. The writers came back from the commit faster and, in return, stood at the last page at the same time more often.
This is exactly where the option helped. Throughput rose from 8,073 to 10,863 inserts per second, so by 35%, and in all four blocks (between +26 and +42%). The comparison runs with the log on its own SSD reached 7,466 without and 7,854 with the option in the same series. PAGELATCH_EX fell to 0.2 ms per insert, and 4.3 ms moved into the flow control.
WRITELOG log flushPAGELATCH_EX latch on the last pageBTREE_INSERT_FLOW_CONTROL flow controlLog on the data SSD
WRITELOG 5.146PAGELATCH_EX 0.992BTREE_INSERT_FLOW_CONTROL 0
WRITELOG 4.885PAGELATCH_EX 0.117BTREE_INSERT_FLOW_CONTROL 0.772
Log on its own SSD
WRITELOG 2.537PAGELATCH_EX 0.761BTREE_INSERT_FLOW_CONTROL 0
WRITELOG 2.558PAGELATCH_EX 0.139BTREE_INSERT_FLOW_CONTROL 0.753
Log in RAM (volatile)
WRITELOG 0.315PAGELATCH_EX 4.597BTREE_INSERT_FLOW_CONTROL 0
WRITELOG 0.320PAGELATCH_EX 0.184BTREE_INSERT_FLOW_CONTROL 4.340
Milliseconds per insert: wait times summed over all tasks and divided by the number of inserts, not a breakdown of a single insert. 64 writers, regular commits, SQL Server 2025, median of four windows per variant. With the log in RAM, an additional 0.232 or 0.284 ms of PREEMPTIVE_OS_FLUSHFILEBUFFERS, not shown.
With delayed durability, so with no waiting for the flush at all, the same pattern showed at every writer count:
| Version | Writers | Inserts/s OFF | Inserts/s ON | Change |
|---|---|---|---|---|
| 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% |
With 64 writers on SQL Server 2025, the summed PAGELATCH_EX wait time fell from 846 to 34 seconds, and about 572 seconds of flow control were added. Notably, even four writers on four schedulers benefited. Microsoft describes the benefit typically at high concurrency relative to the number of schedulers. With only one writer, there was no benefit: 29,229 versus 27,862 inserts per second, and neither flow control nor latch wait time at the index.
You shouldn't overrate the difference between SQL Server 2022 and 2025. Every comparison ran on the same instance with the same build. Without the option, though, 2022 was clearly lower than 2025, and we haven't investigated why.
Throughput is not latency
The option favors threads that finish quickly. That raises throughput across all writers but can make individual ones wait longer. In the lab, this was clearly visible.
Delayed durability (diagnostic)
SQL Server 2025, 64 writers, DELAYED_DURABILITY = FORCED
Throughput ON +35%
Time for 128 inserts by one writer
Median
p95: the slowest 5% of blocks
More throughput and the typical block finishes sooner, but the slowest take 3.5 times as long.
Log in RAM (volatile)
SQL Server 2025, 64 writers, regular commits
Throughput ON +35%
Time for 128 inserts by one writer
Median: not evaluated in this series
p95: the slowest 5% of blocks
More throughput, and the slowest blocks finish sooner, too.
A block is 128 consecutive inserts by one writer. Separate scales starting at zero for inserts per second and seconds per block. Median of the measurement windows per variant. Delayed durability changes the durability guarantee and the RAM log is volatile: both are lab tools only.
With delayed durability and 64 writers on SQL Server 2025, the median for 128 consecutive inserts of a writer fell from 970 to 46 ms. The slowest 5% of the blocks (p95), however, took 3.56 instead of 1.02 seconds. Some writers flew through, others stood still. With the log in RAM, it was the other way round: there, the p95 also fell, from 1.06 to 0.88 seconds.
For a decision, this means: anyone who logs many independent events looks mainly at total throughput. If, on the other hand, a user or an interface waits for every single insert, the distribution counts. Both belong in the comparison.
Checking your own load
Before the option is set, it should be clear that the inserts really pile up at the end of the index. Query 1 takes a snapshot during the load and resolves every PAGELATCH waiter to database, object, index and page. The method follows Microsoft's guide to PAGELATCH_EX.
/* PSG · OFSK · Query 1: Where are inserts piling up right now? Snapshot taken under load: requests waiting on PAGELATCH_*, resolved to database, object, index and page. Reads system views only. Changes nothing. SQL Server 2019 and later (sys.fn_PageResCracker, sys.dm_db_page_info). Permissions: VIEW SERVER STATE (on SQL Server 2022 and later VIEW SERVER PERFORMANCE STATE is enough); that also covers the page resolution, so no separate database permission is needed. The login does need a user in the affected database: without one the query fails with error 916 as soon as a latch waiter sits there. If it may not see the metadata (e.g. no VIEW DEFINITION), the column object_name is NULL. Run it several times a few seconds apart: a single snapshot can come back empty. OPTION (MAXDOP 1): the diagnostic should not take parallel workers, especially when the server is under pressure. */SELECT r.wait_type AS wait_type, COUNT(*) AS waiting, MAX(r.wait_time) AS max_wait_ms, DB_NAME(pc.db_id) AS database_name, OBJECT_SCHEMA_NAME(pi.object_id, pc.db_id) + N'.' + OBJECT_NAME(pi.object_id, pc.db_id) AS object_name, pi.index_id AS index_id, pi.page_type_desc AS page_type, pi.page_level AS page_level, CONCAT(pc.file_id, N':', pc.page_id) AS page, CASE WHEN pi.page_level = 0 AND pi.next_page_page_id = 0 THEN N'yes' ELSE N'no' END AS last_leaf_pageFROM 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 waiting DESC, max_wait_ms DESCOPTION (MAXDOP 1);In our query test on SQL Server 2025 with 32 writers, roughly one capture in four caught waiting requests, for example (shortened):
wait_type waiting database_name object_name index_id page_type page_level page last_leaf_page
PAGELATCH_EX 4 OfskTest dbo.Appends 1 DATA_PAGE 0 1:110674 yes
PAGELATCH_EX 7 OfskTest dbo.Appends 1 INDEX_PAGE 1 1:111559 noTypical of last page insert contention are many waiters on the same page of an index, often with last_leaf_page = yes, plus pages at level 1 or 2 of the same index. If the waiters are on scattered pages or in TempDB instead, it is a different problem. The option doesn't help then.
Query 2 shows, in the affected database, which indexes have accumulated the most latch wait time over time, which key column they start with and whether the option is already set.
/* PSG · OFSK · Query 2: Which indexes wait on page latches, and is the option set? Run it in the affected database. Shows, per index, the latch wait time, inserts, new leaf pages, the first key column and the value of OPTIMIZE_FOR_SEQUENTIAL_KEY. The counters run from the moment the index metadata is in memory and are lost on restart. Reads system views only. Changes nothing. SQL Server 2019 and later (column optimize_for_sequential_key). Permissions: VIEW DATABASE STATE (on SQL Server 2022 and later VIEW DATABASE PERFORMANCE STATE is enough) and visibility of the table metadata (e.g. VIEW DEFINITION); without a metadata permission the result stays empty. OPTION (MAXDOP 1): the diagnostic should not take parallel workers. */SELECT TOP (20) OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id) AS object_name, i.name AS index_name, i.type_desc AS index_type, k.name AS first_key_column, TYPE_NAME(k.user_type_id) AS data_type, k.is_identity AS is_identity, i.optimize_for_sequential_key AS ofsk, s.leaf_inserts, s.new_leaf_pages, 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_per_waitFROM (SELECT ios.object_id, ios.index_id, SUM(ios.leaf_insert_count) AS leaf_inserts, SUM(ios.leaf_allocation_count) AS new_leaf_pages, 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);After the load, Query 2 returned this row in our query test (shortened, index name truncated):
object_name index_name first_key_column data_type is_identity ofsk leaf_inserts new_leaf_pages latch_waits latch_wait_ms ms_per_wait
dbo.Appends PK__Appends… id bigint 1 1 217382 17123 289212 191719 0.66Query 3 answers the second question from the lab: does the load stand more at the log or at the end of the index? It shows the three wait places instance-wide since the last start. More informative is the difference between two snapshots over a load window, for example before and after a change.
/* PSG · OFSK · Query 3: Is it the log or the end of the index that slows things down? Instance-wide wait time since the last start (or since the statistics were last cleared) for the three wait places that matter when inserting at the end of an index. To compare before and after a change, run it twice and take the difference. Reads only. Changes nothing. BTREE_INSERT_FLOW_CONTROL appears from SQL Server 2019 on, as soon as the option takes effect on an index. Permissions: VIEW SERVER STATE (on SQL Server 2022 and later VIEW SERVER PERFORMANCE STATE is enough). OPTION (MAXDOP 1): the diagnostic should not take parallel workers. */SELECT ws.wait_type AS wait_type, CASE ws.wait_type WHEN N'WRITELOG' THEN N'Commit waits for the log flush' WHEN N'PAGELATCH_EX' THEN N'Waiting for exclusive access to a page in memory' WHEN N'BTREE_INSERT_FLOW_CONTROL' THEN N'Waiting in the flow control of OPTIMIZE_FOR_SEQUENTIAL_KEY' END AS meaning, ws.waiting_tasks_count AS waits, ws.wait_time_ms AS wait_time_ms, CAST(1.0 * ws.wait_time_ms / NULLIF(ws.waiting_tasks_count, 0) AS decimal(12, 2)) AS ms_per_wait, CAST(100.0 * ws.wait_time_ms / NULLIF(SUM(ws.wait_time_ms) OVER (), 0) AS decimal(5, 1)) AS share_percentFROM 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);If WRITELOG is far ahead, the log is the first lever: faster storage for the log file, fewer commits by batching inserts, or a check whether the application really has to confirm every row individually. If PAGELATCH_EX is ahead and Query 1 shows the last page, the option is a candidate.
Turning it on, comparing, turning it off
The option can be set when the index is created or changed on an existing index. ALTER INDEX … SET doesn't rebuild the index.
-- turn it on for the existing index (no rebuild)ALTER INDEX PK_Appends ON dbo.Appends SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON); -- checkSELECT name, optimize_for_sequential_keyFROM sys.indexesWHERE object_id = OBJECT_ID(N'dbo.Appends'); -- turn it offALTER INDEX PK_Appends ON dbo.Appends SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF);In the lab, the ALTER INDEX changed the catalog value on the existing index immediately from 1 to 0. For the comparison, the same applies as in the lab:
- Measure under the real load and with the real commit settings. Turning on delayed durability only to make the option look good distorts the picture and changes the guarantee that committed transactions are preserved.
- Compare completed inserts per second and a latency that fits the application, ideally with several repetitions. Individual runs scattered by up to 24% in the lab.
- Record the wait times before and after (Query 3 as a difference). Less
PAGELATCH_EXalone is not yet a success.
Other ways against last page insert contention, such as a different key, hash partitioning or In-Memory OLTP, change the table or the application and have their own consequences for reads and maintenance. Microsoft presents them in the same guide. We haven't measured them here.
What the test shows and what it doesn't
So anyone who evaluates OFSK needs two answers: do more inserts get through overall? And how long do the writers wait for whom latency counts? If the commit stands at the log flush, the option helps little. If the inserts really pile up at the last page, it can help a lot.
Sources
- 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
Read on: Slow under load · Log flush per commit on Linux and Docker · SQL Server transaction log full · Index maintenance explained