PAGELATCH_UP Wait Type Explained

SQL Server uses page latches to protect buffer pool pages during modification operations. When a session needs to update a page, it must acquire a PAGELATCH_UP (update mode latch) before modifying the buffer. The latch protects the physical integrity of the page in memory while preventing other sessions from reading inconsistent data.

PAGELATCH_UP waits spike during allocation-intensive workloads because multiple sessions compete for the same system pages. In tempdb, Page Free Space (PFS) pages track space availability across data pages. Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) pages manage extent allocations. When sessions simultaneously request new pages, they serialize on these critical allocation structures.

SQL Server 2016 introduced multiple tempdb data files by default and improved GAM/SGAM contention handling. SQL Server 2019 added memory-optimized tempdb metadata, reducing system table contention. SQL Server 2022 enhanced buffer pool scalability, but fundamental allocation page contention patterns remain unchanged.

The buffer manager coordinates with the lock manager to ensure page latches don't conflict with logical locks. However, latches operate at the physical buffer level and block regardless of transaction isolation levels. High PAGELATCH_UP waits often indicate insufficient tempdb files, undersized tempdb initial sizes, or workloads creating excessive temporary objects.

-- Identify current PAGELATCH_UP waits and blocking sessions
SELECT 
    s.session_id,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'PAGELATCH_UP'
ORDER BY r.wait_time DESC;

-- Analyze historical wait statistics for PAGELATCH_UP patterns
SELECT 
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'PAGELATCH_UP';

-- Check tempdb allocation contention on system pages
SELECT 
    session_id,
    wait_type,
    wait_duration_ms,
    resource_description,
    CASE 
        WHEN resource_description LIKE '%PFS%' THEN 'Page Free Space contention'
        WHEN resource_description LIKE '%GAM%' THEN 'Global Allocation Map contention'  
        WHEN resource_description LIKE '%SGAM%' THEN 'Shared GAM contention'
        ELSE 'User page contention'
    END AS contention_type
FROM sys.dm_os_waiting_tasks 
WHERE wait_type = 'PAGELATCH_UP'
AND resource_description LIKE '%tempdb%';

-- Examine tempdb file configuration for allocation bottlenecks
SELECT 
    name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    growth,
    is_percent_growth
FROM sys.master_files 
WHERE database_id = DB_ID('tempdb')
AND type = 0  -- Data files only
ORDER BY file_id;

Add additional tempdb data files to reduce allocation contention:

-- Calculate recommended tempdb file count (typically CPU count up to 8)
DECLARE @cpu_count int = (SELECT cpu_count FROM sys.dm_os_sys_info);
DECLARE @target_files int = CASE WHEN @cpu_count <= 8 THEN @cpu_count ELSE 8 END;

-- Check current tempdb data file count
DECLARE @current_files int = (
    SELECT COUNT(*) 
    FROM sys.master_files 
    WHERE database_id = DB_ID('tempdb') AND type = 0
);

-- Add files if needed (modify paths for your environment)
IF @current_files < @target_files
BEGIN
    DECLARE @file_num int = @current_files + 1;
    DECLARE @sql nvarchar(max);
    
    WHILE @file_num <= @target_files
    BEGIN
        SET @sql = 'ALTER DATABASE tempdb 
            ADD FILE (
                NAME = ''tempdev' + CAST(@file_num AS varchar(10)) + ''',
                FILENAME = ''C:\TempDB\tempdev' + CAST(@file_num AS varchar(10)) + '.mdf'',
                SIZE = 1024MB,
                FILEGROWTH = 256MB
            )';
        
        PRINT @sql;  -- Review before executing
        -- EXEC sp_executesql @sql;  -- Uncomment to execute
        SET @file_num = @file_num + 1;
    END
END

Resize existing tempdb files to equal sizes:

-- Standardize tempdb data file sizes to reduce allocation skew
WITH tempdb_files AS (
    SELECT 
        file_id,
        name,
        size * 8 / 1024 AS current_size_mb
    FROM sys.master_files 
    WHERE database_id = DB_ID('tempdb') AND type = 0
),
max_size AS (
    SELECT MAX(current_size_mb) AS target_size_mb 
    FROM tempdb_files
)
SELECT 
    'ALTER DATABASE tempdb MODIFY FILE (NAME = ''' + tf.name + 
    ''', SIZE = ' + CAST(ms.target_size_mb AS varchar(20)) + 'MB);' AS resize_command
FROM tempdb_files tf
CROSS JOIN max_size ms
WHERE tf.current_size_mb < ms.target_size_mb;

-- Execute the generated commands after review
-- Requires SQL Server restart to take full effect

Enable trace flag 1117 and 1118 for older SQL Server versions:

-- For SQL Server versions prior to 2016
-- TF 1117: All files in filegroup grow together
-- TF 1118: Reduce mixed extent allocations
-- These are enabled by default in SQL Server 2016+

-- Check current trace flag status
DBCC TRACESTATUS(1117, 1118);

-- Enable globally (requires restart)
-- Add to SQL Server startup parameters: -T1117 -T1118

-- Or enable for current session (temporary)
-- DBCC TRACEON(1117, 1118, -1);

Configure tempdb with multiple equal-sized data files from installation, typically matching CPU core count up to 8 files. Size initial files appropriately (4GB+ for busy systems) with reasonable growth increments (256MB-1GB) to minimize allocation events. Place tempdb files on fast storage with sufficient space to handle peak workloads without autogrowth.

Monitor PAGELATCH_UP waits through automated alerts when wait times exceed baseline thresholds. Implement query optimization to reduce temporary object creation, particularly table variables and temp tables in high-frequency procedures. Consider memory-optimized table variables for SQL Server 2014+ to bypass tempdb allocation entirely.

Partition large tables to reduce page-level contention on user databases. Avoid single-threaded batch operations that serialize on the same pages. Use NOLOCK hints judiciously for reporting queries to reduce latch pressure, understanding the implications of dirty reads.

Review application patterns that create allocation hotspots. Batch operations should use appropriate MAXDOP settings to balance parallelism benefits against increased tempdb pressure. Consider off-peak processing for allocation-intensive maintenance operations like index rebuilds on large tables.