Years ago I blogged about Latches and why they are needed by SQL Server. In today’s blog post I want to concentrate a little bit more on the Buffer Latches (BUF), and how they are used by SQL Server during Index Seek Operations. Here you will learn a concept called Latch Coupling.

Index Seek Operations

As you know SQL Server can access the data in an index (Clustered Index or Non-Clustered Index) with a Scan and with a Seek Operation. A Seek operation here uses the Navigation Structure of the B-Tree structure to search for specific records in the leaf level. The following picture illustrates this concept.

In that simple example SQL Server reads the Index Root Page, an Index Page in the level below, and finally the Data Page in the Leaf Level. Every time that SQL Server accesses such a page in the Buffer Pool, a Shared Latch is acquired on that page. That Shared Latch is essential, because it makes the page (which is currently processed) read only in memory:

• Every Exclusive Latch is incompatible with the Shared Latch

Therefore requests to acquire an Exclusive Latch will be blocked, and SQL Server reports the PAGELATCH_EX Wait Type to you.

Now let’s have a look at how SQL Server acquires and releases these Latches on the pages of the Index during the Seek operation. The following code shows a simple Extended Event session that captures the latch_acquired and latch_released event for a specific session id (make sure to change that session id accordingly).

CREATE EVENT SESSION LatchTracking ON SERVER 
ADD EVENT sqlserver.latch_acquired
(
    ACTION
	(
		sqlserver.database_id,
		sqlserver.session_id,
		sqlserver.sql_text
	)
    WHERE
	(
		[package0].[equal_uint64]([sqlserver].[session_id],(54)) AND [class]=(28))
	),
ADD EVENT sqlserver.latch_released
(
    ACTION
	(
		sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text
	)
    WHERE
	(
		[package0].[equal_uint64]([sqlserver].[session_id],(54)) AND [class]=(28))
	)
ADD TARGET package0.event_file
(
	SET filename=N'c:\temp\LatchTracking.xel'
)
WITH
(
	MAX_MEMORY = 4096 KB,
	EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
	MAX_DISPATCH_LATENCY = 30 SECONDS,
	MAX_EVENT_SIZE = 0 KB,
	MEMORY_PARTITION_MODE = NONE,
	TRACK_CAUSALITY = OFF,
	STARTUP_STATE = OFF
)
GO

Note: The Filter Predicate on the property “class” restricts to Buffer Latches. Their internal ID is 28. Yes, Extended Events are self-describing…

In the next step I’m now using a simple table to perform a Clustered Index Seek operation. On that table I have previously created a Clustered Index, which consists of 3 Levels in the Navigation Structure (including the Leaf Level).

Latch Coupling in Action

The Extended Event session shows you now which Buffer Latches are acquired for this specific session (as long as you have changed the session id accordingly…) during the Clustered Index Seek operation. When you look at the output you can see that we have captured 6 events: 3 latch_acquired events, and 3 latch_released events.

But the more interesting thing is the order in which SQL Server has acquired and released those Latches. Normally you expect that SQL Server acquires a Latch on a page, processes the page, and finally releases the Latch. But that’s not the truth!

Let’s have a more detailed look at that. In the first step SQL Server acquires a Shared Latch on the Index Root Page (on Page ID 975 in my case). After SQL Server has processed that page, the Clustered Index Seek operation continues by reading the requested page in the level below and acquires a Latch on it (Page ID 257).

Note that the Latch that was acquired on the Index Root Page (Page ID 975) has NOT yet been released. It still remains acquired!

SQL Server only releases the Latch on the Index Root Page when the Latch on the next Index Page was acquired successfully. This approach is called Latch Coupling. It is necessary because SQL Server follows a pointer from one page to another one in the B-Tree structure.

And this pointer must remain stable during the page processing, e.g. it is not allowed that this pointer is invalidated by another worker thread (like a page split operation) in the mean-time. Therefore SQL Server concurrently holds 2 Latches during a (single-threaded) Index Seek operation. The following picture illustrates this very important concept.

The Shared Latch on the Index Root Page (Page ID 975) is released, when SQL Server has successfully acquired the Shared Latch on the page in the level below (page ID 257). When SQL Server has processed this page in the intermediate level, SQL Server again acquires the Shared Latch on the Data Page in the Leaf Level (Page ID 256), and *afterwards* releases the Shared Latch on the page in the Level above (Page ID 257). And as a final step the Shared Latch on Page ID 256 is released when the page was successfully processed.

Summary

In this blog post I have shown you how SQL Server acquires and releases Latches during an Index Seek operation through a concept called Latch Coupling. One common misconception is that SQL Server only acquires a Latch on one specific page during the Seek operation. But this is not really the truth as you have seen today.

Thanks for your time,

-Klaus