PAGELATCH_SH Wait Type in SQL Server

Part of the SQL Server Wait Types Library.
Related deep dive: PAGELATCH_EX, TempDB Allocation Contention.

---

PAGELATCH_SH is a wait for a shared (read) latch on a page that is already in memory. Latches are lightweight locks SQL Server uses to protect in-memory structures while they are being read or changed. This wait means the page is sitting in the buffer pool, so the thread only has to queue for latch access, not for disk.

That distinction matters. PAGELATCH_SH is not a disk problem. If you are chasing storage latency you want PAGEIOLATCH_*, where the page still has to be read from disk. PAGELATCH_* is pure in-memory contention, several threads wanting the same hot page at the same time.

Is It a Problem?

A small amount is normal on any busy system. It becomes a bottleneck when a single page is hot enough that threads spend real time queuing for it. If PAGELATCH_SH or its exclusive sibling PAGELATCH_EX is climbing toward the top of your wait profile, you have a hotspot worth finding.

The tell is the resource_description in sys.dm_os_waiting_tasks, which reads db:file:page. A value like 2:1:1, 2:1:3 (PFS), or 2:1:2 (GAM) points straight at tempdb allocation page contention.

Common Causes

• tempdb allocation contention: many sessions creating and dropping small temp tables or table variables at once, all hitting the same PFS, GAM, or SGAM pages in tempdb.
• A hot application page: a small, heavily read lookup table that many sessions touch constantly.
• Ascending key insert hotspots: high-concurrency inserts into an index with an ever-increasing key, all landing on the final page.

What To Do

1. Read resource_description to identify the page. If it is tempdb (2:...), treat it as tempdb contention.
2. For tempdb, use multiple equally sized data files. One per logical core up to eight is a good starting point, which spreads allocation across more pages.
3. For last page insert hotspots on SQL Server 2019 and later, consider OPTIMIZE_FOR_SEQUENTIAL_KEY = ON on the index.
4. For a hot lookup page, reduce how often it is read, or accept the small latch cost if the table is tiny and already cached.

How To See It

Rank it against everything else with Get-WaitStatistics. If PAGELATCH_SH is near the top of your total wait time, drop into sys.dm_os_waiting_tasks to find the specific page.

---