SQL Server TempDB PAGELATCH_UP Contention: The Hidden Bottleneck in High-Throughput Fleets
A 64-core, 512GB RAM SQL Server instance was stalling at 25% CPU with hundreds of worker threads blocked on PAGELATCH_UP in database 2. Here is the architecture of TempDB allocation bottlenecks and how to resolve them.
Our high-throughput order processing service was struggling on a 64-core enterprise server with 512GB of RAM. CPU utilization hovered around 25%, disk queue lengths were flat, yet transaction response times were degrading rapidly.
When I inspected sys.dm_os_waiting_tasks, over 180 worker threads were blocked in a waiting state. The wait type was PAGELATCH_UP and PAGELATCH_SH, and the resource description pointed directly to database ID 2, page 2:1:1 and 2:1:3.
Database ID 2 in SQL Server is TempDB. The server was not waiting on physical disk I/O; hundreds of concurrent worker threads were fighting for in-memory latches on the exact same allocation bitmap page inside TempDB.
What TempDB does and why allocation pages matter
TempDB is a global, shared resource used by all databases on a SQL Server instance. It stores temporary tables (#temp), table variables (@temp), worktables for sorts and hash joins, and row versioning stores for RCSI.
Whenever a stored procedure creates a temporary table, SQL Server must allocate data pages to store the rows. To track which pages are free, SQL Server uses special allocation tracking pages:
- PFS (Page Free Space): Tracks the allocation status and approximate free space of individual pages. Page 1 of any data file is always a PFS page.
- GAM (Global Allocation Map): Tracks which extents (blocks of 8 pages) have been allocated. Page 2 is always a GAM page.
- SGAM (Shared Global Allocation Map): Tracks which extents are currently being used as mixed extents with free pages. Page 3 is always an SGAM page.
The anatomy of PAGELATCH contention
A PAGELATCH wait occurs when a thread needs to read or modify a page currently held in memory, but another thread already holds an incompatible latch on that page. It has nothing to do with disk I/O (which is PAGEIOLATCH).
When dozens of concurrent queries execute procedures that create temporary tables simultaneously, every single thread must acquire an exclusive or update latch on the exact same PFS and SGAM pages to reserve space. The threads queue up serially, CPU cycles are wasted spinning on latches, and throughput collapses.
-- Identifying TempDB allocation page contention in real time
SELECT
wt.session_id,
wt.wait_type,
wt.wait_duration_ms,
wt.resource_description,
er.command,
er.blocking_session_id
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
WHERE wt.wait_type LIKE 'PAGELATCH_%'
AND wt.resource_description LIKE '2:%'
ORDER BY wt.wait_duration_ms DESC;The multi-file configuration rule
The primary architectural fix for allocation contention is creating multiple TempDB data files of equal size.
SQL Server uses a proportional fill algorithm across data files. If you configure multiple TempDB data files, each file has its own independent PFS, GAM, and SGAM allocation pages. Allocations are distributed across files, eliminating the single-page latch bottleneck.
Standard rule: Configure one TempDB data file per logical processor up to 8 files. If contention persists on servers with more than 8 cores, increase by increments of 4 files up to a maximum of the number of logical cores.
Ensure that all TempDB files have identical initial sizes and identical auto-growth settings to prevent one file from becoming disproportionately full.
Replacing #temp tables with Memory-Optimized table types
Even with 16 TempDB files, extreme transaction volumes can still cause metadata latching in system catalogs like sysobjvalues.
For small lookup sets and temporary arrays inside stored procedures, replace #temp tables with memory-optimized table types (In-Memory OLTP). Memory-optimized table variables bypass TempDB allocation tracking entirely, completely removing latching from the execution path:
-- Creating a memory-optimized table type to bypass TempDB allocation
CREATE TYPE dbo.IdListType AS TABLE
(
Id INT NOT NULL PRIMARY KEY NONCLUSTERED
)
WITH (MEMORY_OPTIMIZED = ON);
GO
-- Using the memory-optimized table variable inside a procedure
DECLARE @TargetIds dbo.IdListType;
INSERT INTO @TargetIds (Id) VALUES (101), (102), (103);
SELECT o.*
FROM dbo.Orders o
JOIN @TargetIds t ON o.CustomerId = t.Id;The practical standard
High-level database architecture is not about drawn boxes on an infrastructure diagram. It is about how the engine manages shared resources under concurrency — memory, latches, write-ahead logs, and lock tables. When things break at 2 AM, the fix is rarely adding another replica or throwing more CPU at the host. The fix is understanding the underlying resource bottleneck, measuring the exact wait event, and applying the architectural constraint that makes the system predictable.