PAGELATCH, PAGEIOLATCH, and LATCH waits in SQL Server

By Kendra Little on September 12, 2023

I’ve long found it tricky to remember and explain the differences between three similar-sounding waits in SQL Server that all have “LATCH” in the name: PAGELATCH, LATCH, and PAGEIOLATCH waits.

Here’s an illustration that explains these waits, along with wait subtypes.

PAGEIOLATCH waits

PAGEIOLATCH at least has a giveaway in its name: the “IO” characters indicate I/O, aka “Input/Output”– a reference to transferring data, in this case from storage to memory.

My definition: “I need to get PAGES from DISK (storage) into BUFFERS in memory. To investigate or reduce this wait, find queries with highest reads and look for indexing & tuning improvements.”

Microsoft docs definition: “Occurs when a task is waiting on a latch for a buffer that is in an I/O request.”

PAGELATCH waits

There’s no “IO” in this one. That’s because the page is already in memory.

My definition: I need to get access to a page that is already in memory. Sometimes this is “last page” insert contention, Page Free Space (PFS) contention, tempdb contention.

Microsoft docs definition: “Occurs when a task is waiting on a latch for a buffer that isn’t in an I/O request.”

LATCH waits

There’s no “PAGE” or “IO” in this one! Awkward. This one is the hardest to explain, too.

My definition: I need to access a structure or resource in memory that is NOT a data page. Can be anything from transaction log management to supporting parallel scans.

Microsoft docs definition: “Occurs when waiting for an [latch subtype] latch. This doesn’t include buffer latches or transaction mark latches.”

Wait Subtypes: EX, UP, SH

First, there are more subtypes than these three (KP! DT!)– but these are the most common subtypes I see. Here is how I remember these:

_UP = Update: I need to make some mods but others can read
_EX= Exclusive: I need this to myself, everyone else wait!
_SH = shared: I need to read this

Learn more about waits!

There are tons of great free resources to learn about wait types on the web. Some of my favorites are from Microsoft Learn and Paul Randal of SQL Skills: