SQL Server PAGELATCH_EX on inserts: fix last-page contention with OPTIMIZE_FOR_SEQUENTIAL_KEY

#Symptom

Under high concurrent inserts, throughput stops rising, or even falls, as you add sessions. CPU may not be maxed out, disk latency looks fine and wait stats show lots of PAGELATCH_EX. A bigger server doesn't help, and more cores can make it worse.

#What's happening

With a clustered index on an ever-increasing key, such as an IDENTITY column, every new row goes to the last page of the index. Each insert needs an exclusive latch on that one page in memory. With many sessions, they all queue up on it.

PAGELATCH isn't PAGEIOLATCH:

#Step 1: confirm it's in your database

Latch waits often come from tempdb allocation pages, which need a different fix. Check where the waits are:

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       wt.resource_description,
       DB_NAME(CAST(PARSENAME(REPLACE(wt.resource_description, ':', '.'), 3) AS int)) AS db_name
FROM sys.dm_os_waiting_tasks AS wt
WHERE wt.wait_type LIKE 'PAGELATCH%'
ORDER BY wt.wait_duration_ms DESC;

resource_description is in dbid:fileid:pageid format. If you see the same page again and again, across many sessions, that's your hot page. Database ID 2 is tempdb. See tempdb contention for that.

#Step 2: find the object and page type

On SQL Server 2019 and later, this query runs against current requests:

SELECT r.session_id,
       r.wait_type,
       r.wait_time,
       pi.database_id,
       DB_NAME(pi.database_id) AS db_name,
       OBJECT_NAME(pi.object_id, pi.database_id) AS object_name,
       pi.index_id,
       pi.page_type_desc,
       pi.page_level,
       pi.next_page_page_id,
       LEFT(t.text, 120) AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS pc
CROSS APPLY sys.dm_db_page_info(pc.db_id, pc.file_id, pc.page_id, 'DETAILED') AS pi
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type LIKE 'PAGELATCH%';

Signs of last-page contention:

• page_type_desc is DATA_PAGE or INDEX_PAGE, on a user table.
• page_level = 0, the leaf level.
• next_page_page_id is 0, meaning it's the last page in the index.
• The sql_text is an INSERT.

If page_type_desc is PFS_PAGE, GAM_PAGE or SGAM_PAGE, you have an allocation problem, not last-page contention.

Check which indexes have taken the most latch time:

SELECT TOP (20)
       OBJECT_NAME(s.object_id) AS table_name,
       i.name AS index_name,
       s.page_latch_wait_count,
       s.page_latch_wait_in_ms,
       s.leaf_insert_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id
ORDER BY s.page_latch_wait_in_ms DESC;

Remember nonclustered indexes too. An index on a created_at column has the same problem as an identity key.

#Step 3: choose a fix

#Fix 1: OPTIMIZE_FOR_SEQUENTIAL_KEY

This option, available from SQL Server 2019, controls how many threads may insert at once, and favors threads likely to finish quickly. It improves throughput under contention. It's a good first step because it doesn't change your keys.

ALTER INDEX PK_Orders ON dbo.Orders
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

Check it:

SELECT OBJECT_NAME(object_id) AS table_name, name, optimize_for_sequential_key
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Orders');

After you turn it on, you'll see a new wait type, BTREE_INSERT_FLOW_CONTROL. That's expected, because it's the feature pacing threads. Judge success by throughput rather than by wait time alone.

It reduces the damage from contention; it doesn't remove the last-page pattern. Some workloads gain less, particularly low-concurrency ones, where it can add a small overhead. Test it under realistic load.

#Fix 2: hash partitioning

This spreads inserts across several "last pages":

CREATE PARTITION FUNCTION pf_hash16 (tinyint)
AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14);

CREATE PARTITION SCHEME ps_hash16
AS PARTITION pf_hash16 ALL TO ([PRIMARY]);

ALTER TABLE dbo.Orders
ADD HashKey AS CONVERT(tinyint, ABS(CONVERT(bigint, OrderId) % 16)) PERSISTED NOT NULL;

The clustered index then has to be rebuilt on the partition scheme, including HashKey.

Caution

Adding a persisted column and rebuilding a clustered index on a big table is a major change. It takes time, disk and log space, and it can block access. Plan a tested migration, ideally by building a new table and switching over. Also expect queries that seek a range on OrderId to read all partitions, unless they can filter on HashKey. A unique index must include the partitioning column, too.

#Fix 3: a non-sequential key

For a new design, you could reverse the bits of a sequence, or use a random value, to spread inserts:

-- Example of using a random GUID as the clustered key
CREATE TABLE dbo.OrdersNew (
    OrderId uniqueidentifier NOT NULL
        CONSTRAINT DF_OrdersNew_Id DEFAULT NEWID()
        CONSTRAINT PK_OrdersNew PRIMARY KEY CLUSTERED,
    CreatedAt datetime2 NOT NULL
);

The cost: page splits, fragmentation, a wider key that gets copied to all nonclustered indexes, and less buffer pool efficiency. Don't use this for a contention problem unless other fixes aren't enough. Avoid NEWSEQUENTIALID() here, because it's sequential again and hits the same pattern.

#Fix 4: shorten the path

Check for things that make the latch queue worse:

• Long transactions with many inserts plus extra work. Move non-database work, like API calls, out of the transaction.
• Row-by-row inserts in a loop. Batching may reduce total latch requests. Use table-valued parameters or BULK INSERT.
• Unneeded indexes. Each index with a sequential leading column has its own last-page contention.
• Triggers and foreign key checks that add time to each insert.

#Step 4: test the change

Don't judge a fix by one wait type. Measure the same load before and after, using a tool such as ostress:

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGELATCH_EX', 'PAGELATCH_SH', 'BTREE_INSERT_FLOW_CONTROL', 'WRITELOG');

If WRITELOG becomes the top wait after the fix, the bottleneck has moved to the transaction log. Check log disk latency next.

Warning

This isn't a problem to fix unless it's measured. Many systems have some PAGELATCH_EX waits with no real effect. Act when it's significant in wait statistics and it's limiting throughput.

#Prevent it next time

• Load test high-insert tables at expected concurrency before go-live.
• Turn on OPTIMIZE_FOR_SEQUENTIAL_KEY for known hot tables on new builds.
• Capture wait stats regularly, so that you can see this trend appear.
• Related: threadpool exhaustion and the DBA cheat sheet.