Latch Contention on Index Leaves

Diagnose lock contention at the leaf nodes of heavily modified B-tree indexes.

In this lesson, you will learn how high-concurrency workloads can create a traffic jam at the leaf level of your B-tree indexes, slowing down your database performance.

The "Hot Spot" Problem

We have previously discussed how B-tree indexes speed up searches. However, B-trees aren't just for reading; they must also be updated. When multiple sessions try to modify the same page in an index simultaneously, the database engine must protect that page’s physical integrity to prevent data corruption. It does this using a latch.

A latch is a lightweight, short-term synchronization object. If ten different processes try to INSERT a row that belongs on the exact same index leaf page at the same time, they will compete for that page's latch. Only one process gets to write to the page; the others must wait in line. This phenomenon is called latch contention.

Why it Happens

Contention typically occurs when your index key is "monotonically increasing"—such as an IDENTITY column or a DATETIME stamp. Because these values always go up, every new row is inserted into the same right-most leaf page of the B-tree.

Imagine a line of cars all trying to enter the exact same parking spot at the same time. The structure looks like this:

-- An index on this column will cause 'Last-Page' latch contention
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY, -- The "Hot Spot"
    OrderDate DATETIME DEFAULT GETDATE(),  -- Another "Hot Spot"
    TotalAmount DECIMAL(10,2)
);

Because every new OrderID is higher than the last, the database engine constantly locks the very last page of the B-tree to write the new record. As your traffic increases, the time spent waiting for this latch accumulates, leading to a performance bottleneck that isn't caused by slow queries, but by structural congestion.

How to Diagnose

You can usually identify this by looking at wait statistics. If you see high PAGELATCH_EX (Exclusive) wait types, your system is likely waiting for access to these busy index pages. To solve this, developers often use strategies like partitioning the index or changing the key to distribute inserts across different pages.

Key Takeaway: Latch contention at the leaf nodes occurs when high-concurrency inserts fight over the same index page, usually caused by monotonically increasing keys.