PAGEIOLATCH_UP Wait Type Explained

PAGEIOLATCH_UP waits manifest when the buffer pool manager cannot immediately grant an Update latch on a page that is undergoing physical I/O. The Update latch mode is an intermediate mode used to prevent deadlocks during a read-then-write pattern. A thread first acquires an UP latch to read the page, and if a modification is needed, it converts the UP latch to an EX (exclusive) latch. The UP latch is compatible with shared (SH) latches but incompatible with other UP and EX latches.

The buffer pool's latch manager enforces these waits when a session requests an Update latch on a page that is either being read from disk or has pending I/O operations. Unlike shared latches (PAGEIOLATCH_SH), Update latches indicate modification intent, making them more contentious and performance-critical.

SQL Server 2016 introduced buffer pool improvements with NUMA-aware memory partitioning, which can reduce latch contention. SQL Server 2022 added intelligent query processing optimizations that can reduce unnecessary page latch requests during large scan operations.

The wait occurs in this sequence: a session requests page modification through the access methods layer, the buffer manager checks if the page exists in memory, finds the page requires I/O or has conflicting latch requests, queues the Update latch request, and waits until the I/O completes and conflicting operations release their latches.

Memory pressure exacerbates PAGEIOLATCH_UP waits because pages get flushed more frequently, increasing the likelihood of requesting latches on pages not in memory. Storage latency directly correlates with wait duration since Update latch requests cannot complete until underlying I/O operations finish.

-- Current PAGEIOLATCH_UP waits and their resource details
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    DB_NAME(CAST(SUBSTRING(r.wait_resource, 1, CHARINDEX(':', r.wait_resource) - 1) AS INT)) AS database_name,
    r.blocking_session_id,
    s.program_name,
    t.text AS current_sql
FROM sys.dm_exec_requests r
INNER 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 = 'PAGEIOLATCH_UP'
ORDER BY r.wait_time DESC;

-- Historical PAGEIOLATCH_UP wait statistics and trends
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST((wait_time_ms - signal_wait_time_ms) AS DECIMAL(12,2)) / waiting_tasks_count AS avg_resource_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'PAGEIOLATCH_UP'
AND waiting_tasks_count > 0;

-- Buffer pool pressure and page life expectancy
SELECT 
    cntr_value AS page_life_expectancy_seconds,
    cntr_value / 60.0 AS page_life_expectancy_minutes
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Page life expectancy'
AND object_name LIKE '%Buffer Manager%'
UNION ALL
SELECT 
    cntr_value AS buffer_cache_hit_ratio_percent,
    NULL
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name LIKE '%Buffer Manager%';

-- I/O stall statistics by database file
SELECT 
    db.name AS database_name,
    f.name AS file_name,
    f.type_desc,
    s.io_stall_read_ms,
    s.io_stall_write_ms,
    s.io_stall,
    s.num_of_reads,
    s.num_of_writes,
    CASE WHEN s.num_of_reads > 0 
         THEN s.io_stall_read_ms / s.num_of_reads 
         ELSE 0 END AS avg_read_stall_ms,
    CASE WHEN s.num_of_writes > 0 
         THEN s.io_stall_write_ms / s.num_of_writes 
         ELSE 0 END AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) s
INNER JOIN sys.master_files f ON s.database_id = f.database_id AND s.file_id = f.file_id
INNER JOIN sys.databases db ON s.database_id = db.database_id
WHERE s.io_stall > 1000
ORDER BY s.io_stall DESC;

-- Index fragmentation that may cause excessive page splits
SELECT 
    DB_NAME(ps.database_id) AS database_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS table_name,
    i.name AS index_name,
    ps.avg_fragmentation_in_percent,
    ps.page_count,
    ps.avg_page_space_used_in_percent,
    ps.record_count,
    i.fill_factor
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 30
AND ps.page_count > 1000
AND ps.database_id = DB_ID()
ORDER BY ps.avg_fragmentation_in_percent DESC;

Rebuild fragmented indexes causing excessive page splits

-- Generate index rebuild commands for highly fragmented indexes
-- Test on development first, as rebuilds require exclusive locks
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'ALTER INDEX ' + QUOTENAME(i.name) + 
    ' ON ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + 
    ' REBUILD WITH (ONLINE = ON, MAXDOP = 4, SORT_IN_TEMPDB = ON);' + CHAR(13)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE ps.avg_fragmentation_in_percent > 40
AND ps.page_count > 1000
AND i.index_id > 0; -- Skip heaps

PRINT @sql;
-- Uncomment to execute: EXEC sp_executesql @sql;

Increase fill factor on frequently splitting indexes

-- Identify and modify fill factor for indexes with high page splits
-- Lower fill factor reduces page splits but increases storage usage
SELECT 
    'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + 
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + 
    ' REBUILD WITH (FILLFACTOR = 85);' AS rebuild_command
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ops 
    ON i.object_id = ops.object_id AND i.index_id = ops.index_id
WHERE ops.leaf_allocation_count > 1000  -- High page allocations indicate splits
AND i.fill_factor = 0 OR i.fill_factor > 90
AND i.index_id > 0;

Enable instant file initialization for data files

-- Check if instant file initialization is enabled
-- Requires 'Perform volume maintenance tasks' privilege for SQL Server service account
SELECT 
    instant_file_initialization_enabled,
    CASE 
        WHEN instant_file_initialization_enabled = 1 
        THEN 'Instant file initialization is enabled'
        ELSE 'Enable instant file initialization on service account'
    END AS recommendation
FROM sys.dm_server_services 
WHERE servicename LIKE 'SQL Server (%';

-- If disabled, grant 'Perform volume maintenance tasks' to SQL Server service account
-- through Windows Local Security Policy or Group Policy

Optimize memory allocation to reduce buffer pool pressure

-- Analyze memory usage and provide recommendations
-- Adjust max server memory based on system specifications
DECLARE @total_memory_mb INT = (
    SELECT total_physical_memory_kb / 1024 
    FROM sys.dm_os_sys_memory
);

DECLARE @current_max_memory_mb INT = (
    SELECT CAST(value AS INT) 
    FROM sys.configurations 
    WHERE name = 'max server memory (MB)'
);

DECLARE @recommended_memory_mb INT = @total_memory_mb - 2048; -- Reserve 2GB for OS

SELECT 
    @total_memory_mb AS total_system_memory_mb,
    @current_max_memory_mb AS current_max_server_memory_mb,
    @recommended_memory_mb AS recommended_max_server_memory_mb,
    CASE 
        WHEN @current_max_memory_mb = 2147483647 THEN 'Max server memory not configured'
        WHEN @current_max_memory_mb > @recommended_memory_mb THEN 'Consider reducing max server memory'
        WHEN @current_max_memory_mb < (@recommended_memory_mb * 0.8) THEN 'Consider increasing max server memory'
        ELSE 'Max server memory appropriately configured'
    END AS recommendation;

-- Uncomment to apply recommended memory setting
-- EXEC sp_configure 'max server memory (MB)', @recommended_memory_mb;
-- RECONFIGURE;

Configure instant file initialization on the SQL Server service account through Windows Local Security Policy to eliminate zero-initialization waits during file growth operations. Monitor page life expectancy and maintain values above 300 seconds for OLTP workloads.

Implement proactive index maintenance with fill factors between 80-90% for tables experiencing frequent inserts and updates. Schedule index maintenance during low-activity windows using online operations where Enterprise Edition is available.

Establish I/O performance baselines with average disk latencies below 15ms for data files and 5ms for log files. Deploy databases on separate physical drives from the operating system and separate data files from transaction log files.

Configure appropriate maximum server memory settings, reserving 2-4GB for the operating system on dedicated database servers. Enable lock pages in memory for the SQL Server service account on systems with sufficient RAM to prevent buffer pool paging.

Monitor PAGEIOLATCH_UP waits continuously through automated alerting when wait times exceed 100ms consistently over 5-minute intervals. Implement query store to identify specific queries causing excessive page modifications and optimize them through index tuning or query rewrites.

Use database file pre-growth strategies, sizing data and log files appropriately at creation time to minimize autogrowth events. Configure autogrowth in MB rather than percentages, with data file growth increments of 256MB-1GB depending on database size.