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.

Quick Comparison

Here’s a table that compares the three latch wait types:

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 and 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, or 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. 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:

• List of waits: sys.dm_os_wait_stats (Microsoft Learn)
• Transaction Locking and Row Versioning Guide (Microsoft Learn)
• Wait Stats Library (SQLskills)
• Latch Contention Whitepaper (Microsoft Learn)
• Latch Classes Library (SQLskills)
• Most common latch classes (SQLskills)