PAGELATCH_SH is a thread waiting to read a page that is already in memory, because another thread has it latched for a change. It is not a disk wait. When it climbs, one page is hot, very often a tempdb allocation page. Run the check to name the page.Microsoft describes it as a task waiting on a latch for a buffer that isn’t in an I/O request, in shared mode, which lets many threads read a page but not change it. The thread cannot get the page because another thread holds it in exclusive mode.
That makes it in-memory contention, several threads wanting the same page at once. If you are chasing storage latency, you want PAGEIOLATCH_SH instead, where the page still has to come from disk.
Is It a Problem?
A small amount is normal on any busy system. It becomes a bottleneck when one page is hot enough that threads spend real time queuing for it. Judge it over an interval rather than the cumulative totals: the baseline to monitor is page latch wait time per interval across PAGELATCH_SH, PAGELATCH_UP and PAGELATCH_EX, which usually rise together around the same page.
Common Causes
- tempdb allocation contention: many sessions creating and dropping temp tables at once, all hitting the same allocation pages.
- Last-page insert contention: many sessions inserting into an index with an ever-increasing key, so readers and writers meet on the final page.
- A small lookup table that every session reads constantly while something updates it.
The Check To Run
Rank it against everything else with Get Wait Statistics. While it is happening, sys.dm_os_waiting_tasks names the page as database_id:file_id:page_id:
SELECT
wt.session_id,
wt.wait_type,
wt.wait_duration_ms,
wt.blocking_session_id,
wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
WHERE wt.wait_type LIKE N'PAGELATCH_%'
ORDER BY wt.wait_duration_ms DESC;
Database 2 is tempdb. Page 1 of a data file is its PFS page, page 2 the GAM and page 3 the SGAM, so 2:1:1, 2:1:2 or 2:1:3 point straight at tempdb allocation contention; PFS pages repeat further into each file. Any other page is an object page, and the hot index is the lead. The query needs VIEW SERVER STATE.
What To Do
- For tempdb, follow the tempdb guidance: several equally sized data files, as many as logical processors up to eight to start with, adding more in fours if contention continues.
- For last-page insert contention on SQL Server 2019 and later, consider
OPTIMIZE_FOR_SEQUENTIAL_KEY = ONon the index, or a key that does not always land on the last page. - For a hot lookup page, reduce how often it changes while it is being read, or cache it in the application.
The PAGELATCH Family
Each latch mode on an in-memory page has its own wait type. Shared, update and exclusive are where contention shows up; keep, destroy and null exist for completeness. The main page is PAGELATCH_EX, with tempdb allocation contention.
- PAGELATCH_EXExclusive. Writing to a page in memory; last-page inserts and tempdb contention.The main page for this family
- PAGELATCH_SHShared. Reading a page in memory, queued behind a writer.Worth a look when it climbs. This page.
- PAGELATCH_UPUpdate. Often allocation pages such as PFS, GAM and SGAM.Worth investigating
- PAGELATCH_KPKeep. Stops the page being destroyed by another thread.Usually noise
- PAGELATCH_DTDestroy. Taken before the contents of a page are deleted.Usually noise
- PAGELATCH_NLNull. Documented for information only, not supported.Usually noise
Where To Go Next
If PAGELATCH_SH is sitting near the top of your wait stats, these are the next places to look.
- PAGELATCH_EX, TempDB Allocation Contention, the tempdb allocation walkthrough.
- The waits that usually matter, ranked, with what each one is telling you.
- Performance & Troubleshooting, the scripts and guides for this part of the stack.
Leave a Reply