Eliminating TempDB Allocation Contention: A Deep Dive into GAM, SGAM, and PFS Page Wars

As a Junior DBA, few performance tuning tasks seem as intimidating as opening a monitoring dashboard during a high-traffic production slowdown and seeing thousands of active threads stalled on PAGELATCH_UP or PAGELATCH_EX wait types. When you dig deeper into the metadata, you notice that all these blocking chains point directly to a single location: Database ID 2 (the system TempDB).

TempDB is the universal workspace for your entire SQL Server instance. Every time an application query creates a local temporary table (#TempTable), uses a table variable (@TableVariable), sorts a massive dataset, or triggers row versioning (RCSI), the Storage Engine rushes to TempDB to allocate physical disk pages.

When hundreds of client connections attempt to build and drop temporary objects simultaneously, they end up colliding on the exact same management tracking pages. This architectural bottleneck is known as TempDB Allocation Contention. Let's lift the hood, look at the specialized allocation pages causing these disk wars, and learn the exact scripts and configurations needed to resolve them.

1. The Real-World Analogy: The Concert Ticket Office

To understand allocation contention, picture a massive stadium concert. Thousands of fans are rushing to buy physical tickets at the box office simultaneously.

• The Extent Data Pages (The Seats): The stadium has plenty of empty seats. Finding a physical location to sit down isn't the problem.

• The Allocation Page (The Single Ticket Ledger): To keep things organized, the ticket office forces every ticket agent to check a single, centralized clipboard ledger page to see which blocks of seats are still unassigned.

• The Bottleneck (Contention): Because there is only one ledger page, only one ticket agent can modify it at a exact fraction of a second. The other 50 ticket agents have to form a line, waiting for their turn to look at the ledger. The ticket windows grind to a halt—not because the stadium is full, but because the agents are fighting over the tracking ledger.

In SQL Server, GAM, SGAM, and PFS pages are those centralized clipboard ledgers.

2. Meet the Specialized Allocation Pages

SQL Server manages disk space in blocks of 8KB Pages. Eight of these pages grouped together form an 64KB Extent. To track whether an extent is empty, full, or hosting mixed table data, the storage engine relies on three internal metadata tracking structures:

GAM (Global Allocation Map)

The GAM tracks space allocation for uniform extents (extents owned by a single object). Each GAM page can track roughly 64,000 extents (about 4GB of file space). A bit value of 1 means the extent is free; 0 means it is allocated.

SGAM (Shared Global Allocation Map)

The SGAM tracks mixed extents (extents shared by multiple small tables or indexes). When a query creates a tiny temporary table that only needs a single 8KB page, the engine looks at the SGAM to find a mixed extent that has open slots.

PFS (Page Free Space)

While GAM and SGAM track whole extents, a PFS page tracks the precise byte-level availability of individual pages. It notes whether a page is empty, 50% full, 100% full, or contains ghost rows. A PFS page appears every 8,000 pages within a data file.

3. Diagram 1: The Allocation Page Traffic Jam

When a temporary table is created, the Storage Engine must modify the first GAM, SGAM, and PFS pages in the data file to reserve space. Under high concurrency, this creates a major traffic jam.

4. Diagnosing the War Zone via T-SQL

When TempDB allocation page wars strike your instance, SQL Server will register heavy latch waits. Run this script to scan active execution requests and decode whether your systems are actively blocking on specific TempDB allocation nodes:

SELECT 
    er.session_id AS [SessionID],
    er.wait_type AS [WaitType],
    er.wait_time AS [WaitTime_MS],
    er.blocking_session_id AS [BlockingSessionID],
    wt.resource_description AS [ResourceDescription],
    -- Parse the Page ID out of the database resource string
    CASE 
        WHEN wt.resource_description LIKE '2:%' THEN 
            CASE CAST(SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, 3) + 1, 10) AS INT)
                WHEN 1 THEN 'GAM Page (Global Allocation Map)'
                WHEN 2 THEN 'SGAM Page (Shared Global Allocation Map)'
                WHEN 3 THEN 'PFS Page (Page Free Space)'
                ELSE 'Standard User Data/Index Page'
            END
        ELSE 'Non-TempDB Resource'
    END AS [ContentionTarget],
    st.text AS [ExecutingSQL]
FROM sys.dm_exec_requests er
JOIN sys.dm_os_waiting_tasks wt ON er.session_id = wt.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE er.wait_type LIKE 'PAGELATCH_%'
  AND wt.resource_description LIKE '2:%'; -- Filter exclusively for Database ID 2 (TempDB)
GO

How to Read the Resource String (2:1:3)

The resource string follows a strict pattern: Database_ID : File_ID : Page_ID.

• 2:1:1: Database 2 (TempDB), File 1, Page 1 \(\rightarrow\) GAM Contention.

• 2:1:2: Database 2 (TempDB), File 1, Page 2 \(\rightarrow\) SGAM Contention.

• 2:1:3: Database 2 (TempDB), File 1, Page 3 \(\rightarrow\) PFS Contention.

5. Diagram 2: Multi-File Routing (The Resolution Architecture)

To resolve allocation contention, we have to copy our real-world box office fix: We buy more clipboards. By splitting TempDB into multiple data files of the exact same physical size, you introduce a dedicated set of GAM, SGAM, and PFS allocation pages for every single file. SQL Server automatically balances traffic across these files using a round-robin allocation algorithm, distributing the workload evenly.

6. The Production Triage & Remediation Playbook

If you detect allocation contention on your system, implement three enterprise-grade configuration standards to eliminate the bottleneck:

1. Apply the vCore Multi-File File Formula

Configure multiple TempDB data files to spread out your allocation targets. Follow Microsoft's gold-standard hardware core rule:

• If your server has 8 or fewer Logical Cores: Create a number of data files exactly equal to your number of logical cores.

• If your server has more than 8 Logical Cores: Start with exactly 8 data files. If you still see allocation latch waits after testing under peak workloads, scale up by adding 4 additional files at a time until the contention stops.

2. Enforce Perfect Size Equality

Every TempDB data file must be configured with the exact same initial size and auto-growth increment rules.

-- Sample cleanup to equalize TempDB file footprints
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 8GB, FILEGROWTH = 1024MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdb_data2', SIZE = 8GB, FILEGROWTH = 1024MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdb_data3', SIZE = 8GB, FILEGROWTH = 1024MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdb_data4', SIZE = 8GB, FILEGROWTH = 1024MB);
GO

If one file grows even slightly larger than the others, SQL Server’s proportional fill algorithm will route nearly all write requests to that single larger file, instantly re-introducing allocation page contention.

3. Leverage Modern Engine Defaults

• SQL Server 2016 through 2022+: The engine automatically enables the equivalent of historical Trace Flags 1117 (forcing all data files in a filegroup to grow simultaneously) and 1118 (forcing uniform allocation to bypass mixed extent SGAM pages entirely).

• SQL Server 2019+: Microsoft introduced Memory-Optimized TempDB Metadata. If your application relies heavily on massive object turnover, you can shift the system tracking tables completely out of disk storage and into in-memory structures:

-- Enable memory-optimized tempdb metadata tracking (Requires an instance restart)
ALTER DATABASE tempdb SET MEMORY_OPTIMIZED_METADATA = ON;
GO

By ensuring your TempDB layout is perfectly balanced and monitoring allocation footprints through catalog DMVs, you can eliminate structural latch stalls, maximize write throughput, and keep your production systems running smoothly under high transaction volumes.

How many data files do you currently utilize to manage TempDB constraints on your primary instances? Have you experimented with memory-optimized metadata settings on SQL Server 2019 or 2022? Let's discuss optimization strategies in the comments below!