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 :)
