The events table hit its wall at 4,100 inserts per second. Not at peak, not during a checkpoint — at 11:40 on an ordinary Wednesday, on a 96-core box showing eighteen percent CPU, with the storage array reporting sub-millisecond latency and plenty of headroom. The application team's graphs showed throughput pinned flat while their queue backed up, and the database looked completely healthy everywhere except one number: hundreds of sessions stacked on PAGELATCH_EX, every single one of them waiting on the same page. More hardware would have made the queue longer, not shorter.
This is last-page insert contention, the tax that ever-increasing keys charge at high insert rates. Everything here applies to SQL Server 2016 through 2022, with the one mitigation that is 2019-and-later flagged clearly when we get to it.
Why does an ever-increasing key funnel every insert into one page?
Because a b-tree keeps rows sorted, and a monotonically increasing key sorts every new row to the same place: the rightmost leaf page. Cluster the events table on an IDENTITY column and every insert, from every session, targets that single page until it fills and splits — at which point the new rightmost page inherits the crowd. The page itself lives permanently in the buffer pool, so this has nothing to do with disk. The bottleneck is the page latch: a lightweight, short-lived synchronization primitive that protects an in-memory page's structure while a thread modifies it. One thread may hold the page in exclusive mode at a time, so every inserter queues on PAGELATCH_EX for its turn, inserts, releases, and the next thread goes. Each individual operation takes microseconds, which is exactly why CPU stays low — the cores are mostly parked while the sessions spend their lives queued on one latch.
The distinction that matters operationally: a latch is not a lock. Locks protect transactional consistency, participate in deadlock detection, and show up in sys.dm_tran_locks; latches protect physical structures, cannot deadlock, and never appear in lock views. The blocking-chain playbook I use for LCK_M_ waits in my lead-blocker notes does not apply here, because there is no lead blocker to kill — the queue is the disease, and it reforms instantly behind any session you remove. Adding insert sessions or adding cores only lengthens the line at the same single door.
How do I confirm last-page insert contention and not something else?
Two pieces of evidence together are conclusive: PAGELATCH waits dominating sys.dm_os_wait_stats, and the wait resources pointing at data pages belonging to one user-table index. Wait stats alone are not enough, because PAGELATCH also covers the allocation-map contention that plagues tempdb — PFS, GAM, and SGAM pages — which is a different disease with a different cure. The giveaway for last-page contention is the resource: dm_exec_requests exposes wait_resource for page latches as database_id:file_id:page_id, and when the same page id (or a tight cluster of page ids near the end of one index) absorbs nearly all the waits, you have your diagnosis.
SELECT r.session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
SUBSTRING(t.text, (r.statement_start_offset / 2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN DATALENGTH(t.text)
ELSE r.statement_end_offset END
- r.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type LIKE N'PAGELATCH%'
ORDER BY r.wait_time DESC;Take a page id from the output and identify its owner. On SQL Server 2019 and later, sys.dm_db_page_info(database_id, file_id, page_id, 'LIMITED') returns the object and index directly; on older builds, DBCC PAGE with the WITH TABLERESULTS option tells you the same thing with worse ergonomics. When the answer comes back as your insert-heavy table's clustered index — and the page number climbs slightly each time you look, following the growing right edge — the case is closed. If the pages resolve to allocation maps or to tempdb instead, stop and go read about tempdb configuration, because none of what follows will help.
Does OPTIMIZE_FOR_SEQUENTIAL_KEY actually fix it?
For a sequential-key hotspot on SQL Server 2019 and later, it is the first move, and in my experience it usually turns the incident into a non-event. The option is an index property, not a hint: with it set, the engine changes how it grants the page latch for inserts it recognizes as landing on the last page. Instead of a strict first-come-first-served queue where every inserter waits behind whoever holds the latch, waiters that can make progress on the page are allowed to proceed — effectively letting multiple sequential inserts interleave on the hot page rather than march through one at a time. Throughput on the right edge climbs substantially; Microsoft positions the feature squarely at this exact scenario, IDENTITY and sequence-keyed tables included.
ALTER INDEX PK_Events ON dbo.Events
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
-- verify; the column exists on sys.indexes from 2019 onward
SELECT name, optimize_for_sequential_key
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Events');The honest cost sheet is short. It only addresses the sequential last-page pattern — it does nothing for allocation-map contention or lock waits. It is off by default, so every qualifying index needs an explicit ALTER, and the property lives on the index, so a rebuild with different options or a scripted recreation in a migration can silently drop it; put the verification query above into whatever schema-drift check you run. And it reduces the queue rather than abolishing physics: at extreme rates the right edge is still one page being modified, and some PAGELATCH traffic remains, just no longer queue-bound. For the 4,100-inserts-per-second events table, flipping this one property moved the ceiling past anything the application could generate, which is why it comes first.
What if I am on 2017, or the allocator is not enough?
Then the fix is to stop having a single rightmost page: scatter the inserts across many hot pages so no latch queue can form. The classic pre-2019 technique is the hash-bucketed key. Add a persisted computed column that buckets the identity value, then make that bucket the leading column of the clustered index — with 32 buckets you get 32 right edges, and the latch queue divides by roughly 32:
ALTER TABLE dbo.Events
ADD InsertBucket AS (EventId % 32) PERSISTED;
CREATE UNIQUE CLUSTERED INDEX PK_Events
ON dbo.Events (InsertBucket, EventId)
WITH (DROP_EXISTING = ON);The cost sheet here is real, so read it before the migration window. Every query that filters on EventId alone now has no leading-key match, because the bucket sits in front; point lookups either include the bucket — which the application can compute, since it is deterministic — or fan out into a seek across all 32 buckets. Range scans ordered by EventId lose their single contiguous run and pay for reassembly across buckets. Foreign keys and existing nonclustered indexes referencing the old key shape need rework. You are buying insert throughput with read-path complexity, and the right bucket count is the smallest number that clears the latch queue, not the largest you can think of.
The older variant of the same idea is the reversed or scattered key — byte-reversing the identity or hashing it so inserts land at pseudo-random positions throughout the tree. It removes the hotspot without a bucket column, but every insert now touches a random page, which widens the working set, increases buffer-pool churn, and brings mid-tree page splits back into your life. One more option belongs in the conversation for genuinely extreme rates: memory-optimized tables, whose indexes are latch-free by design and whose bucketed hash indexes shrug at this entire category of problem. The migration is a different durability, tooling, and feature-compatibility world, though — a rewrite decision, not a tuning decision — so I mention it as a direction, not a recommendation.
Do GUIDs fix the contention?
Yes, and that is the trap. Clustering on a random GUID from NEWID() scatters inserts across the entire tree, the latch hotspot evaporates on day one, and the postmortem reads like a victory — right up until the fragmentation report. Random inserts split pages at random positions in the tree, and those splits leave pages roughly half full that then receive few further inserts; the index settles at poor page density, often somewhere around two-thirds full where a sequential insert pattern holds pages near-full. The same logical data then occupies substantially more pages: the buffer pool holds fewer rows per gigabyte, range scans and readahead degrade because logically adjacent rows are physically scattered, and backup and checkdb times grow with the page count. You have converted a write-side serialization problem into a permanent storage and read-side tax.
NEWSEQUENTIALID() looks like the compromise — mostly-increasing GUIDs, globally unique — and it is, in the worst way: the values increase monotonically per machine, so the last-page hotspot comes straight back. My honest ranking for the events-table shape: OPTIMIZE_FOR_SEQUENTIAL_KEY on 2019+, a bucketed key when the read path can absorb it, and random GUIDs only when the table is queried almost exclusively by its own primary key and you have budgeted the fillfactor tuning and the rebuild schedule to manage the fragmentation you just bought. "Fix the latch, inherit the fragmentation" is a trade to make with open eyes, not a default.
Watching latch queues with MonPG when SQL Server support lands
The counters that matter are wait time per PAGELATCH subtype trended over time, the wait-resource distribution so one hot page lights up as one hot page, and insert throughput graphed against the latch queue — the flat-top curve from that Wednesday is the shape you want an alert on before the application team sees it. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the wait-resource query above run on a schedule, plus a sys.dm_os_wait_stats snapshot diffed every few minutes, is the whole toolkit — and it is enough to catch the queue forming while you can still do something graceful about it.