SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 10. Latches

Latches are lightweight synchronization objects that protect the consistency of SQL Server internal data structures. As the opposite of locks, which protect transactional data consistency, latches prevent corruption of the data structures in memory.

In most cases, latches are short-lived and may be unnoticeable in systems with light loads. However, as loads grow, latch contention may become an issue and can limit system scalability and throughput. In this chapter, I will discuss how to detect and address those situations.

I will start this chapter with an overview of latches and their categories and types. Next, I will discuss page latches and several mitigation techniques to address their contention. Finally, I will cover other ways to deal with common latch types.

Introduction to Latches

There is a concept in computer science called mutual exclusion, which means multiple threads or processes cannot execute critical code simultaneously. Think about the multithreaded application in which threads use the shared objects. In those systems, you often need to serialize the code that accesses those objects to avoid creating race conditions when multiple threads read and update them simultaneously.¹

Internally, SQL Server enforces mutual exclusion and protects in-memory data structures by using latches. People are often confused by latches’ similarities to locks: both types of objects affect concurrency and may prevent simultaneous access to the same data. There ...