PAGEIOLATCH_SH Wait Type Explained

PAGEIOLATCH_SH waits happen when a worker thread requests a shared latch on a page in the buffer pool, but that page is currently being read from disk by another I/O operation. The buffer manager places the requesting thread in a wait state until the physical read completes and the shared latch becomes available.

The shared latch protects page consistency during read operations. Multiple threads can hold shared latches simultaneously on the same page, but no exclusive operations can proceed. When a page isn't found in the buffer pool (cache miss), SQL Server initiates an asynchronous I/O request to read the page from disk. Other threads requesting the same page must wait for this I/O to complete.

SQL Server 2016 introduced significant buffer pool improvements with support for non-uniform memory access (NUMA) awareness in buffer pool scanning. SQL Server 2019 enhanced this with accelerated database recovery (ADR), which reduces some I/O pressure during recovery operations but doesn't directly impact PAGEIOLATCH_SH behavior.

The wait accumulates when disk subsystem response time is slow (typically >15ms), when buffer pool hit ratios are low (forcing frequent physical reads), or when index seeks become scans due to poor query plans. Memory pressure exacerbates the problem by reducing buffer pool efficiency and increasing page evictions.

Unlike PAGEIOLATCH_EX waits (which indicate write operations), PAGEIOLATCH_SH specifically points to read I/O bottlenecks. The wait time directly correlates with storage latency plus any queuing delays in the storage stack.

-- Check current PAGEIOLATCH_SH waits and identify most impacted databases
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    DB_NAME(r.database_id) as database_name,
    r.command,
    s.program_name,
    s.login_name
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.wait_type = 'PAGEIOLATCH_SH'
ORDER BY r.wait_time DESC;

-- Analyze historical wait statistics to identify I/O pressure patterns
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(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct_total_waits
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'PAGEIOLATCH_SH'
    OR wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_time_ms DESC;

-- Identify specific database files experiencing high I/O waits
SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.io_stall_read_ms,
    CASE WHEN vfs.num_of_reads = 0 
         THEN 0 
         ELSE vfs.io_stall_read_ms / vfs.num_of_reads 
    END AS avg_read_latency_ms,
    vfs.num_of_bytes_read / 1048576 AS mb_read
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
INNER JOIN sys.master_files mf ON vfs.database_id = mf.database_id 
    AND vfs.file_id = mf.file_id
WHERE vfs.num_of_reads > 0
ORDER BY avg_read_latency_ms DESC;

-- Check buffer pool hit ratio and page life expectancy by NUMA node
SELECT 
    object_name,
    counter_name,
    instance_name,
    cntr_value,
    CASE 
        WHEN counter_name = 'Buffer cache hit ratio' THEN cntr_value / 100.0
        WHEN counter_name = 'Page life expectancy' THEN cntr_value
    END AS formatted_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
    AND (counter_name = 'Buffer cache hit ratio' 
         OR counter_name = 'Page life expectancy'
         OR counter_name = 'Buffer cache hit ratio base')
ORDER BY counter_name, instance_name;

-- Identify queries causing excessive logical reads leading to physical I/O
SELECT TOP 10
    qs.sql_handle,
    qs.total_logical_reads,
    qs.total_physical_reads,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_physical_reads > 0
ORDER BY qs.total_logical_reads DESC;

Increase Maximum Server Memory (if insufficient)

-- Check current memory configuration and usage
SELECT 
    total_physical_memory_kb / 1024 AS total_physical_memory_mb,
    available_physical_memory_kb / 1024 AS available_physical_memory_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- Set max server memory to appropriate value (leave 2-4GB for OS)
EXEC sp_configure 'max server memory (MB)', 6144; -- Adjust based on available RAM
RECONFIGURE;

Test in development first. Monitor system after implementation. Incorrect memory settings can cause OS instability.

Force Query Plan Recompilation for Problematic Queries

-- Clear plan cache for specific database experiencing high PAGEIOLATCH_SH
DECLARE @db_name VARCHAR(128) = 'YourDatabaseName';
DECLARE @sql NVARCHAR(MAX) = 'USE [' + @db_name + ']; DBCC FREEPROCCACHE;';
EXEC sp_executesql @sql;

-- Alternative: Clear cache for specific query plan
-- DBCC FREEPROCCACHE (plan_handle_from_previous_query);

This forces plan recompilation and may temporarily increase CPU usage. Execute during maintenance windows when possible.

Update Statistics on Tables with High Physical Reads

-- Update statistics on all tables in current database
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';

-- Or target specific high-read tables identified from diagnostic queries
UPDATE STATISTICS [schema].[high_read_table] WITH FULLSCAN;

Full scan statistics updates are I/O intensive. Schedule during low-activity periods. Consider sampling for very large tables.

Rebuild Fragmented Indexes Causing Excessive I/O

-- Identify and rebuild highly fragmented indexes
SELECT 
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30 
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Rebuild the most fragmented indexes
ALTER INDEX [index_name] ON [schema].[table_name] REBUILD WITH (ONLINE = ON);

Online rebuilds require Enterprise Edition. Use REORGANIZE for Standard Edition. Monitor transaction log growth during rebuilds.

Configure appropriate maximum server memory settings leaving sufficient RAM for the operating system (typically 2-4GB minimum). Monitor buffer pool hit ratios consistently above 95% and page life expectancy above 300 seconds for OLTP workloads.

Implement proactive index maintenance schedules using Ola Hallengren's maintenance solution or similar. Rebuild indexes with fragmentation >30% and reorganize those with fragmentation between 10-30%. Schedule during low-activity windows to minimize user impact.

Establish baseline I/O performance metrics for all database files. Monitor average read latency staying below 15ms for data files and 5ms for log files. Configure disk queue length alerts when sustained above 2 per physical disk.

Deploy query performance monitoring to identify inefficient queries generating excessive logical reads. Implement query hints, index tuning, or query rewrites for problematic statements. Use Query Store in SQL Server 2016+ to track plan regression automatically.

Consider storage subsystem improvements including faster SSD storage, increased IOPS capacity, or storage caching solutions when consistent high PAGEIOLATCH_SH waits persist despite query and index optimization. Modern NVMe storage typically resolves most I/O latency issues.