Solving Last-Page Insert Latch Contention by Index Partitioning

According to a Microsoft KB, 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.
Let's discuss more details about what happens internally in SQL Server when a query reading and writing pages in memory. When SQL Server accesses a page in the buffer pool, the page access must be synchronized among multiple worker threads. Every time a query read a page, its worker thread has to acquire a shared latch (PAGELATCH_SH), and every time a query write a page, its worker thread has to acquire an exclusive latch (PAGELATCH_EX). When you run an INSERT statement, the worker thread exclusively latches the page, in the meantime no any other concurrent worker threads can read and write from/to this page. With an ever-increasing index, parallel INSERT queries are contending about an exclusive latch on the same last page in that index, illustrated as below:

In this blog post, I'm going to demonstrate how to solve this problem by partitioning the index. Starting from SQL Server 2016 SP1, index partitioning feature is available on all editions.
Let's create the demo table first, with an identity primary key:
CREATE TABLE Ticket (
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    colA varchar(50)
);
By using the SQLQueryStress tool created by Adam Mechanic, you can simulate multiple concurrent INSERT statements into the demo table:

 

You can see there are PAGELATCH_EX wait on some sessions:
SELECT wait_type, wait_resource, COUNT(1) cnt FROM sys.dm_exec_requests GROUP BY wait_type, wait_resource ORDER BY cnt DESC; 

The wait resource in this case is 6:1:134489, meaning database id 6, file 1 and page 134489. You can check that page to determine what it is by using the DBCC PAGE command:
DBCC TRACEON(3604);
GO
DBCC PAGE(6, 1, 134489, 0);
GO

The ObjectId is 146099561. You can get the actual object name using the OBJECT_NAME function, which gives you the Ticket table you just created:

In order to minimize the PATCHLATCH_EX contention on the last page, we can use partitioning with a computed column. Let’s do this:
-- suppose you have 4 CPU cores in your SQL Server
CREATE PARTITION FUNCTION pf_hash (tinyint) AS RANGE LEFT FOR VALUES (0, 1, 2, 3);
GO
CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]);
GO
DROP TABLE Ticket;
GO
CREATE TABLE Ticket (
    id int IDENTITY(1, 1) NOT NULL,
    colA varchar(50),
    hashId AS CONVERT(tinyint, ABS(id % 4)) PERSISTED NOT NULL,
    CONSTRAINT PK_Ticket PRIMARY KEY CLUSTERED (id, hashId)
) ON ps_hash(hashId);
GO
Let's check the table is partitioned:
SELECT DISTINCT
    table_name = O.[name],
    partition_index_name = I.[name],
    partition_index_type = I.[type_desc],
    partition_scheme = PS.[name],
    partition_function = PS.[name]
FROM sys.partitions P
JOIN sys.objects O ON O.[object_id] = P.[object_id]
JOIN sys.indexes I ON P.[object_id] = I.[object_id] AND P.index_id = I.index_id
JOIN sys.data_spaces DS ON I.data_space_id = DS.data_space_id
join sys.partition_schemes PS on DS.data_space_id = PS.data_space_id
JOIN sys.partition_functions PF on PS.function_id = PF.function_id;

Now run the stress test again, you will the PAGELATCH_EX wait becomes fewer than before.