SQL Server TempDB PAGELATCH contention: allocation page latch waits on PFS, GAM, and SGAM

CPU is moderate, I/O latency is normal, the buffer pool is healthy, and throughput has dropped. Wait statistics show PAGELATCH_UP or PAGELATCH_EX dominating, and sys.dm_os_waiting_tasks shows sessions waiting on pages like 2:1:1, 2:1:2, or 2:1:3.

This is TempDB allocation page latch contention. Every temp table, spilled sort, or worktable allocation needs space in TempDB. SQL Server tracks free space and allocation state in bitmap pages: PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map). When sessions contend for the same allocation pages, they serialize on in-memory latches. Disk and CPU are not the bottleneck. The problem is logical contention on a fixed set of pages in the buffer pool.

The classic cause is a single TempDB data file on a multi-core server. The fix is to add equally sized data files so allocation requests spread across independent sets of allocation pages.

What this means

PAGELATCH waits are in-memory latch waits on pages already in the buffer pool, distinct from PAGEIOLATCH waits that wait for a page to be read from disk. Allocation pages (PFS, GAM, SGAM) are updated on every extent or page allocation or deallocation in a file.

In TempDB (database_id 2), these allocation pages live at fixed locations in each data file. In file_id 1:

PageAddressRole
PFS2:1:1Tracks free space per page (one byte per page)
GAM2:1:2Tracks which extents are fully allocated (uniform extents)
SGAM2:1:3Tracks which extents have at least one free mixed page

Each additional data file gets its own set of these pages at the same offsets within that file. With one TempDB file, every allocation in TempDB serializes through the same PFS, GAM, and SGAM pages. With eight files, allocation requests spread across eight independent sets of allocation pages, reducing per-page contention roughly eightfold.

flowchart TD
    subgraph Single["1 TempDB data file"]
        A1["Alloc req 1"] -->|"latch OK"| P1["PFS page 2:1:1"]
        A2["Alloc req 2"] -.->|"waits"| P1
        A3["Alloc req 3"] -.->|"waits"| P1
    end
    subgraph Multi["4 TempDB data files"]
        B1["Alloc req 1"] --> P2["PFS 2:1:1"]
        B2["Alloc req 2"] --> P3["PFS 2:2:1"]
        B3["Alloc req 3"] --> P4["PFS 2:3:1"]
        B4["Alloc req 4"] --> P5["PFS 2:4:1"]
    end

The constraint is a latch on a single in-memory page, which is why CPU and I/O are both underutilized relative to the throughput drop.

Common causes

CauseWhat it looks likeFirst thing to check
Single TempDB data filePAGELATCH_UP dominant, resource_description 2:1:1 or 2:1:3Count TempDB data files via sys.master_files WHERE database_id = 2 AND type = 0
Too few files for core countContention persists after adding some files, still on early allocation pagesCompare file count to logical CPU count from sys.dm_os_sys_info
Unequal file sizesContention returns after uneven autogrowthCheck size column in sys.master_files for TempDB across all files
Metadata contention misdiagnosed as allocationPAGELATCH_EX on non-allocation pages (not 2:1:1/2/3)Check exact page IDs in resource_description

Quick checks

All of these are read-only and safe to run during an active incident.

-- PAGELATCH waits. Snapshot twice 30s apart and compute the delta.
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms - signal_wait_time_ms AS resource_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH_%'
ORDER BY wait_time_ms DESC;

-- Confirm the waits are on TempDB (database_id = 2).
SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%'
  AND resource_description LIKE '2:%';

-- Count TempDB data files and check they are equally sized.
SELECT file_id, name, size * 8 / 1024 AS size_mb, growth, is_percent_growth
FROM sys.master_files
WHERE database_id = 2 AND type = 0
ORDER BY file_id;

-- Logical CPU count drives the target file count.
SELECT cpu_count FROM sys.dm_os_sys_info;

-- SQL Server version determines which engine improvements apply.
SELECT @@VERSION;

-- TempDB space consumers (rule out exhaustion as a separate problem).
SELECT
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

How to diagnose it

  1. Confirm PAGELATCH is the top wait category. Snapshot sys.dm_os_wait_stats twice, 30 seconds apart, and compute deltas. If PAGELATCH_UP or PAGELATCH_EX appears in the top waits by resource wait time (after excluding idle waits like LAZYWRITER_SLEEP, WAITFOR, and BROKER_*), proceed.

  2. Confirm the waits are on TempDB. Query sys.dm_os_waiting_tasks filtering for wait_type LIKE 'PAGELATCH_%' AND resource_description LIKE '2:%'. The 2 prefix means database_id 2 (TempDB). PAGELATCH waits on other database IDs are hot page contention in a user database, not TempDB allocation contention.

  3. Identify which allocation pages are contended. resource_description returns the page address as db_id:file_id:page_id. For allocation contention, expect pages like 2:1:1 through 2:1:3 (PFS, GAM, SGAM in file 1), or the same low page numbers in other files (2:2:1, 2:3:1, and so on).

  4. Rule out metadata contention. If resource_description shows higher page IDs (for example 2:1:128 or other non-allocation pages), this is metadata latch contention on system tables (such as sys.sysobjvalues), not allocation contention. Adding TempDB data files will not fix it. On SQL Server 2016 and 2017, KB4058174 addressed a known sysobjvalues contention bug that caused PAGELATCH_EX on non-allocation pages during heavy temp table DDL. On SQL Server 2019+, memory-optimized TempDB metadata can eliminate metadata latch contention on these system tables.

  5. Verify file count and sizing. Check sys.master_files for database_id 2. Count the data files (type = 0). A single file on a multi-core machine is the root cause. If files exist but are unequally sized, proportional fill sends more allocations to the larger file and recreates contention on that file’s allocation pages.

  6. Rule out TempDB space exhaustion as a compounding issue. Allocation contention and space exhaustion are independent problems that can coexist. If free space is low, queries may also fail with error 1105. Address both.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
PAGELATCH_UP/EX as percentage of total waitsIndicates allocation page contention severitySustained above 5% of total wait time
resource_description pattern 2:1:%Confirms TempDB allocation pages, not user database hot pagesRepeated waits on pages 1, 2, or 3 in file 1
TempDB data file countMust scale with logical CPU countSingle file, or fewer than 8 on a multi-core box
TempDB file size equalityUnequal sizes cause uneven allocation via proportional fillAny file more than a few MB different from others
Batch requests/sec during contentionQuantifies throughput impactDrop in batch requests while connections remain steady
Version store sizeRCSI and snapshot workloads add TempDB allocation pressureVersion store above 50% of TempDB space

Fixes

Add TempDB data files

This is the primary fix. The recommendation is one data file per logical CPU, up to 8. If contention persists after reaching 8 files, add files in multiples of 4, up to the number of logical processors.

All files must be the same size. SQL Server uses proportional fill: it weights new allocations toward files with more free space. If one file is larger, it gets a disproportionate share of allocations and its allocation pages become the new bottleneck.

-- Current file configuration
SELECT file_id, name, physical_name, size * 8 / 1024 AS size_mb,
       growth, is_percent_growth
FROM sys.master_files
WHERE database_id = 2 AND type = 0
ORDER BY file_id;

-- Example: add a second TempDB data file matching existing file size.
-- Replace the path and SIZE/FILEGROWTH with your existing file's values.
-- Verify the target directory exists and the SQL Server service account can write to it.
ALTER DATABASE tempdb ADD FILE (
    NAME = N'tempdev2',
    FILENAME = N'E:\SQLData\tempdb2.ndf',
    SIZE = 25600MB,
    FILEGROWTH = 256MB
);

The new file is created on disk immediately and SQL Server begins using it for new allocations. No restart is required. The catalog change is permanent across restarts. If the existing primary file is much larger than the new files, resize it down or grow the new files to match so all files are equal.

Disable percent growth

If TempDB files use percent growth (is_percent_growth = 1), each autogrow event makes the file larger by a percentage of its current size, causing files to diverge. Switch to fixed-size growth increments applied equally to all files.

Pre-size TempDB files

TempDB is recreated at its configured initial size on every restart. If the initial size is too small, the instance pays for autogrowth events during startup and early workload warmup. Pre-size all TempDB files to handle the expected working set at peak, with the same initial size and the same fixed growth increment on every file.

Version-specific engine improvements

The fundamental fix (multiple equally sized files) is the standard recommendation across all versions, but newer engines reduce the per-page serialization:

  • SQL Server 2016+: Uniform extent allocation is enabled by default for TempDB, and all data files autogrow together. Trace flags 1117 and 1118, previously needed to enable these behaviors, are no longer required for TempDB. TF 1118 still applies to user databases on older versions for the same purpose.

  • SQL Server 2019+: PFS page updates use shared latches instead of exclusive latches, reducing PFS contention natively. SQL Server 2019 also introduced memory-optimized TempDB metadata, which moves system tables (such as sys.sysobjvalues, sys.sysschobjs) into latch-free in-memory structures. This eliminates metadata latch contention but requires ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED_TEMPDB_METADATA = ON followed by a restart, and it is not enabled by default. Tradeoffs: a single transaction cannot access memory-optimized tables in more than one database, and columnstore indexes on temp tables are not supported when it is enabled.

  • SQL Server 2022+: GAM and SGAM page updates also use shared latches, allowing concurrent updates. Microsoft states that TempDB allocation contention is near-completely addressed in SQL Server 2022 and these improvements are on by default.

Even on SQL Server 2022, Microsoft recommends keeping multiple equally sized TempDB data files. The concurrent latch improvements reduce per-page serialization but do not eliminate the benefit of distributing allocations across files.

What does NOT fix allocation contention

  • Adding more CPU. The constraint is logical serialization on a page, not CPU throughput.
  • Faster storage. PAGELATCH waits do not involve disk I/O. PAGEIOLATCH waits do.
  • More memory. The contended pages are already in the buffer pool.
  • Adding TempDB files when the waits are on metadata pages, not allocation pages. Check the exact page IDs in resource_description first.

Prevention

  • Start with the right file count at deployment. On SQL Server 2016+, Setup creates multiple TempDB data files by default (one per logical CPU up to 8). Do not override this to a single file.
  • Equal sizing is ongoing, not one-time. Monitor file sizes after autogrowth. If one file grew larger than the others, resize all files to match.
  • Monitor PAGELATCH waits as a time series. A single cumulative query against sys.dm_os_wait_stats shows the entire uptime profile and is useless for identifying current problems. Snapshot every 30 to 60 seconds and compute deltas. A sudden spike in PAGELATCH_UP as a proportion of total waits is the leading indicator.
  • Watch for workload changes that increase TempDB allocation pressure. RCSI enablement, new temp-table-heavy stored procedures, and increased sort/hash spills all raise allocation page churn. These changes may not trigger alerts individually but compound to produce contention under load.

How Netdata helps

  • Per-second wait statistics collection shows PAGELATCH_UP/EX emerging in real time. Correlating the wait spike with batch requests/sec and user connections confirms whether throughput is affected.
  • TempDB space metrics (user objects, internal objects, version store, free space) distinguish allocation contention from space exhaustion, which present similar symptoms but need different fixes.
  • I/O latency per file confirms that PFS, GAM, and SGAM latch waits are not masking a storage problem. If PAGELATCH is high but PAGEIOLATCH and I/O stall are flat, allocation contention is confirmed.
  • CPU utilization and runnable scheduler backlog help rule out CPU pressure. PAGELATCH contention often presents as low CPU with high wait time, which looks paradoxical without wait-stats context.
  • Anomaly detection on wait-type distributions surfaces the moment PAGELATCH_UP shifts from baseline, even before it becomes the dominant wait.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.