PAGELATCH_SH Wait Type in SQL Server

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX, TempDB Allocation ContentionRelated pillar: Performance & Troubleshooting
⚡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

  1. 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.
  2. For last-page insert contention on SQL Server 2019 and later, consider OPTIMIZE_FOR_SEQUENTIAL_KEY = ON on the index, or a key that does not always land on the last page.
  3. 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.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *