Solving Last-Page Insert Latch Contention by SQL Server 2019 new feature: OPTIMIZE_FOR_SEQUENTIAL_KEY

Two years ago I wrote a blog post Solving Last-Page Insert Latch Contention by Index Partitioning which explains how to solve a problem called last-page insert PAGELATCH_EX contention, That is if you have an even-increasing index on a table, such as IDENTITY or GETDATE(), then during huge amount of concurrent queries/threads inserting new rows into that table, different threads will contend for exclusive latch PAGELATCH_EX on the last data page of that index in the buffer pool. The solution that I provided on that old post requires adding a computed column on the table, append that column into the clustered index, and enabling table partitioning, which involved quite a lot of table structure changes. SQL Server 2019 introduces a new feature, OPTIMIZE_FOR_SEQUENTIAL_KEY, which is a new index option that intended to address the last page insert contention. Below demonstration shows how you do that.

--When you create the table, e.g.

CREATE TABLE DemoTbl (

Id INT IDENTITY(1, 1) NOT NULL,

col1 varchar(50),

CONSTRAINT PK_DemoTbl PRIMARY KEY CLUSTERED (Id ASC) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)

);

GO

--When you create the index, e.g.

CREATE INDEX DemoTbl_Id ON DemoTbl (Id) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

GO

--Or if you need to alter an existing index:

ALTER INDEX DemoTbl_Id ON DemoTbl SET(OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

GO

--Check which indexes enabled this option in the current database:

SELECT * FROM sys.indexes WHERE optimize_for_sequential_key = 1;

GO

Simple enough :)