Latch coupling in SQL Server (Latch coupling)

A few years ago, I wrote an article about latches and why SQL Server needs them. In today's article, I want to talk a little more about non-cache latches (Non-buffer latches)and how SQL server uses them during an index lookup operation. Here you will learn the concept called latch coupling (Latch coupling) .

Index Lookup operation (Index seek Operations)

As you know, SQL Server uses scanning (scan) and find (seek) operations to access data in indexes (clustered and nonclustered indexes). The find operation here uses the navigation structure of the B-tree to find specific records on the leaf nodes. Demonstrates this concept.

In this example, SQL Server reads the index root page, the index page under the hierarchy, and finally reads the data page at the leaf level. Each time SQL Server accesses this page in cache dominant, the page needs to obtain a shared latch (GKFX Latch). A shared latch is critical because in memory it makes the page that is currently being processed read-only:

• Each exclusive latch (Exclusive Latch) is incompatible with the shared latch .

So requesting an exclusive latch will block, and SQL Server will prompt you for a pagelatch_ex wait type.

Now let's look at how SQL Server obtains and frees these latches during a lookup operation on the index page. The following code shows that for a specific session ID, you can capture the extended events session for the latch_acquired and latch_released events (modify the session ID as appropriate).

CREATEEVENT SESSION latchtracking onSERVERADDEVENT sqlserver.latch_acquired (ACTION (sqlserver.database_id, sqlserver.session_id, Sqls Erver.sql_text)WHERE    (        [PACKAGE0].[Equal_uint64]([SQL Server].[session_id],( Wu)) and [class]=( -))    ),ADDEVENT sqlserver.latch_released (ACTION (sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text )    WHERE    (        [PACKAGE0].[Equal_uint64]([SQL Server].[session_id],( Wu)) and [class]=( -))    )ADDTARGET Package0.event_file (SETFileName=N'C:\temp\LatchTracking.xel') with(Max_memory= 4096KB, Event_retention_mode=Allow_single_event_loss, Max_dispatch_latency=  -SECONDS, Max_event_size= 0KB, Memory_partition_mode=NONE, Track_causality= OFF, Startup_state= OFF)GO

Note: The filter predicate on the "class" attribute restricts the cache latch. Their internal ID is 28. Yes, extended events are self-describing ...

Next, I am now using a table for the clustered index lookup operation, where I have created a clustered index that contains three layers (including the leaf layer) in the navigation hierarchy.

latch coupling Combat (Latch coupling in Action)

The extended events session shows you that the cache latch is required for the entire session during the clustered index lookup operation (as long as the session ID you modified is correct ...). ）。 When you look at the output, you will 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 obtains and releases these latches. Generally you expect SQL Server to get a latch on the page and finally release the latch. But that's not the case!

Let's take a look at the details. First, SQL obtains a shared latch on the Index root page (page No. 975). After SQL Server processes that page, the clustered index lookup operation continues to read the requested page at the next level, and gets the latch on it (page No. 257).

Note the latch obtained on the index root page is not released yet, and it remains available!

When the latch is successfully acquired on the next index page, the latch on the index root page is released. This method is called latch coupling (Latch coupling). This is necessary because SQL Server follows a pointer from one page to another in the B-tree structure.

This pointer must remain stable during page processing. For example, this pointer is not allowed to be invalidated by another worker thread (such as a paging operation) during this period. Therefore, SQL Server holds 2 latches simultaneously during a (single-threaded) index lookup operation. The following picture illustrates this important concept very well.

When SQL Server successfully acquires the shared latch on a lower-level page (page number 257), the shared latch on the Index root page (page number 975) is freed. When SQL Server processes the page at the intermediate level, SQL Server obtains the shared latch on the leaf-level data page (page number 256), and then releases the shared latch on the upper page (page number 257). When this page is successfully processed, the shared latch that was last on page number 265 is also released.

Summary

In this article I show you how SQL Server obtains and releases a latch in an index lookup operation through the so-called latch coupling concept. A common misconception is that SQL Server acquires the latch only on a specific page during a lookup operation. As you can see in today's article, this is not really true.

Thank you for your attention!

original link

http://www.sqlpassion.at/archive/2016/10/24/latch-coupling-in-sql-server/

Latch coupling in SQL Server (Latch coupling)