Part of the SQL Server Wait Types Library.
Related deep dive: PAGELATCH_EX Wait Type.
PAGELATCH_UP is a wait for an update-mode (UP) latch on a data file page that is already in memory. Update mode is used when a thread needs to modify a page structure that others may still read concurrently, and its most common customers are allocation bitmap pages: PFS, GAM, and SGAM pages that track which pages and extents are in use.
That makes PAGELATCH_UP the classic signature of allocation page contention, with tempdb the usual crime scene. This is an in-memory wait; if the page had to come from disk it would be PAGEIOLATCH_UP instead.
Is It a Problem?
Yes, when it is sustained, because it means many sessions are queuing to update the same allocation bitmap. The giveaway is the resource_description in sys.dm_os_waiting_tasks, formatted db:file:page. Database id 2 is tempdb; page 1 is PFS, pages 2 and 3 are GAM and SGAM, and every 8,088th page repeats PFS. Seeing 2:1:1 or 2:1:3 repeatedly is tempdb allocation contention, full stop.
Small transient amounts on busy systems are normal background.
Common Causes
- tempdb allocation contention: many concurrent sessions creating and dropping temp tables and table variables, all updating the same PFS/SGAM pages.
- Too few tempdb data files concentrating allocation on one set of bitmaps (less common since SQL Server 2016’s multi-file defaults).
- Allocation-heavy patterns in user databases: mass inserts into new pages, bulk loads allocating extents at speed.
What To Do
- Confirm the target pages via
sys.dm_os_waiting_taskswhile the wait is live. - For tempdb: use multiple equally sized data files (one per logical core up to eight is a solid start), keep them the same size with autogrowth uniform.
- On SQL Server 2022 and later much of this is relieved automatically (concurrent PFS/GAM updates); on 2016 to 2019, trace flags 1117 and 1118 behaviour is already the default for tempdb.
- Cut tempdb churn at the source: fewer needless temp objects, cached temp tables in frequently called procedures.
How To See It
Rank it against everything else with Get-WaitStatistics. If PAGELATCH_UP ranks highly, the page numbers in sys.dm_os_waiting_tasks will name the exact bitmap under contention.
Leave a Reply