Demystifying TempDB Allocation Contention: GAM, SGAM, and PFS Page Wars
In a high-throughput SQL Server environment, TempDB is often the most heavily utilized database on the instance. When applications rely intensely on temporary tables (#temp), table variables (@table), or complex joins that spill to disk, TempDB can quickly become a massive operational bottleneck.
If you see performance degrading and find your sessions waiting on PAGELATCH_UP or PAGELATCH_EX resource types inside database ID 2, you are likely at war with TempDB allocation contention. Let's look at how the storage engine tracks space and how to eliminate this bottleneck permanently.
1. The Core Infrastructure: GAM, SGAM, and PFS
To understand allocation contention, you must understand how SQL Server tracks data page availability. The engine allocates space in groups of 8 pages (each page is 8KB), known as an Extent (64KB). It uses three special system page types to manage this:
+-----------------------------------------------------------------------+
| TEMPDB ALLOCATION TRACKING |
+-----------------------------------------------------------------------+
| [ GAM Page ] --> Tracks which extents are completely free or full. |
| [ SGAM Page ] --> Tracks mixed extents containing unallocated pages. |
| [ PFS Page ] --> Tracks byte-level allocation status & page fullness|
+-----------------------------------------------------------------------+
Global Allocation Map (GAM): Tracks which extents have been allocated. Each GAM page covers roughly 4GB of data file space.
Shared Global Allocation Map (SGAM): Tracks extents currently used as "mixed extents" (extents shared by multiple small objects). Like GAM, each page tracks 4GB of space.
Page Free Space (PFS): Tracks the allocation status and exact amount of free space inside individual pages. A PFS page occurs every 64MB (or every 8,000 pages).
2. Why the Contention Happens
Every time a session creates a temporary table, the storage engine must allocate a fresh page. It scans the first GAM/SGAM page to find an available extent, and then checks the PFS page to register the allocation.
When hundreds of concurrent application threads try to create and destroy temporary objects simultaneously, they all rush to modify the exact same allocation pages at the start of the TempDB data file.
Because only one thread can modify a memory page at an absolute microsecond, a massive queue forms. Threads stall, and your wait statistics show high PAGELATCH delays on resources like 2:1:1 (Database 2, File 1, Page 1 - GAM), 2:1:2 (SGAM), or 2:1:3 (PFS).
3. The Enterprise Remedy Checklist
Modern versions of SQL Server have built-in defenses against these allocation wars, but you must ensure your configuration follows senior DBA best practices:
File Multiplicity (The Standard Rule)
Divide your TempDB data storage across multiple physical files of identical size.
The Guideline: Create 8 data files if your server has 8 or more logical cores. If contention persists under extreme workloads, scale up in increments of 4 (e.g., 12 or 16 files).
Why it works: SQL Server uses a proportional-fill round-robin algorithm. Spreading data across 8 files means you now have 8 GAM, 8 SGAM, and 8 PFS pages operating in parallel, splitting the thread workload cleanly.
Enable Native Behaviors (SQL 2016+)
If you are running older legacy systems, ensure Trace Flags 1117 (Autogrow all files equally) and 1118 (Eliminate mixed extents, force uniform allocations) are active. In modern versions (SQL 2016 through 2025), these behaviors are permanently enabled by default under the hood.
Metadata Optimization (SQL 2019+)
If your contention is happening at the system catalog table layer rather than the storage pages, enable Memory-Optimized TempDB Metadata:
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
-- Requires a SQL Server service restart to take effect
This shifts the tracking of TempDB system tables into lock-free, latch-free memory-optimized structures, completely eliminating system table latches.
Have you run into TempDB page wars on your production environments? Let's discuss your tuning configurations in the comments below!