PAGELATCH in SQL Server

A PAGELATCH waits in SQL Server occur when multiple concurrent sessions compete to access the same in-memory page, and SQL Server must serialize access using latches. They are NOT disk I/O waits, they are memory-level synchronization waits typically seen in TempDB under high concurrency or on heavily inserted hotspot tables. They lead to severe performance slowdowns, blocking and CPU spikes. Fixes include configuring multiple TempDB files, optimizing TempDB usage, spreading insert hotspots, and improving table/index design.

The most common types of page latches include:

Ø  PAGELATCH_SH (Shared): Acquired when reading a page structure.

Ø  PAGELATCH_EX (Exclusive): Acquired when modifying a page, blocking other threads from reading or writing to it.

Ø  PAGELATCH_UP (Update): Used as an intermediate mode between SH and EX, often related to allocation page contention in tempdb. 

There is one question in our mind when do PAGELATCH waits Happen. Whenever SQL Server needs to:

Ø  allocate pages

Ø  insert rows

Ø  update metadata

Ø  modify data pages

Ø  access TempDB structures

and multiple sessions try to access the same page at the same time.

Below query is used to get the PAGELATCH details

SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%';

TempDB is the BIGGEST source PAGELATCH because TempDB uses shared metadata pages PFS (Page Free Space), GAM (Global Allocation Map), SGAM (Shared Global Allocation Map) in the high concurrency and every process fights these pages. In the TEMPDB many temp tables being created/dropped and doing heavy SELECT INTO creating table variables, SQL server performs version store activity due to that hash & sort spills happen.

Symptoms of PAGELATCH Issues

Ø  queries slow only under concurrency

Ø  random slowdowns

Ø  CPU spikes

Ø  NOT I/O bound

Ø  application timeouts

Ø  TempDB slowness