SQL Server: Important Discussion about the Latches

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In the previous series of the articles, I have shared details about the locking and deadlocking of the SQL Server.

In this post, I am going share similar details, but this is going to be a very important discussion about the SQL Server Latches.

What is Latche in the SQL Server?

Previously SQL Server Professionals are facing problem like Lost Updates in which two transactions are unaware about the each other and last transaction overwrites the transaction updated by the first transaction.
Now SQL Server has power of row-level locking in which Latching is playing the main role.

Latches are lightweight internal structures which ensure the physical data integrity. It is performing like the thread synchronization.

When we are reading a particular data page, internally that data page is moved from disk to memory buffer pool area and it creates a require structures for its execution like memory allocation, page modification details, remark about the dirty page and others.

During this process, it will create one or more Latches to prevent corruption by the other threads.
If multiple threads are going to update data into the same page, Latches creates an exclusive lock for all rows.
A data page can only be latched while it is in memory.

Once the data page is modified in memory and written back to the disk, the Latches will be released and other threads will be able to access that data page and memory structure again.

The latching can also be used with Read-Ahead in which reading operation moves data page from disk to memory and Latches prevents further modification on that data page.

The important note is:
SQL DBA doesn’t have any control on Latches because internally SQL Server controls the utilization of the Latches.
But DBA can monitor the different wait types related to Latches and can take the steps for performance optimization.

Three different types of Latches:

IO Latches: It makes sure that all I/O operations are done and should not be executed multiple times while reading and writing same data page into a disk and buffer pool.

Buffer Latches: It protects the pages into the buffer pool from the concurrent running threads.

Non-Buffer Latches: It protects the shared data structures of the buffer pool.

The different between Locks and Latches:

Locks ensure that same record cannot be modified by two different connections and Latches ensure that record resides in a proper data page for further reading and writing operation.

Locks provide a consistency of logical transaction and Latches provide a consistency of the memory area.

The DBA can control and manage database locks by applying different Isolation Levels and for Latches, DBA doesn’t have any control because it’s managed by the SQL Server.