PAGELATCH_SH Wait Type Explained

The buffer pool manager uses page latches to protect the physical integrity of data pages in memory. When SQL Server needs to read a page, it first acquires a shared (SH) latch to prevent other threads from modifying the page structure while reads occur. Unlike locks which protect logical consistency, latches protect physical page structures from corruption during concurrent access.

PAGELATCH_SH waits accumulate when the scheduler cannot immediately grant a shared latch because other threads already hold incompatible latches on the same page. The most common scenario is contention on frequently accessed pages like the root page of a clustered index, small dimension tables, or system metadata pages. Multiple SELECT operations can hold shared latches simultaneously, but they must wait if an exclusive (EX) or update (UP) latch is held by another thread.

The latch manager in SQL Server operates at the page level within the buffer pool. When a page experiences high concurrency, the latch wait queue grows as threads stack up waiting for access. Unlike lock waits which can be resolved through isolation level changes, latch waits are fundamental to SQL Server's page protection mechanism and cannot be bypassed.

SQL Server 2016 introduced significant improvements to latch contention through the addition of non-uniform memory access (NUMA) optimizations. SQL Server 2019 further enhanced this with intelligent query processing features that can reduce hot page access patterns. SQL Server 2022 added parameter sensitive plan optimization which can help distribute access patterns more evenly.

-- Identify which pages are experiencing the most PAGELATCH_SH waits
SELECT TOP 10
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'PAGELATCH_SH'
ORDER BY wait_time_ms DESC;

-- Find current PAGELATCH_SH waits with specific page information
SELECT 
    session_id,
    wait_type,
    wait_time,
    wait_resource,
    blocking_session_id,
    command,
    DB_NAME(database_id) as database_name
FROM sys.dm_exec_requests r
WHERE wait_type = 'PAGELATCH_SH'
ORDER BY wait_time DESC;

-- Identify the most accessed pages causing latch contention
SELECT TOP 20
    database_id,
    file_id,
    page_id,
    page_type_desc,
    allocation_unit_type_desc,
    COUNT(*) as access_count
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_db_page_info(r.database_id, 
    CAST(SUBSTRING(r.wait_resource, CHARINDEX(':', r.wait_resource) + 1, 
    CHARINDEX(':', r.wait_resource, CHARINDEX(':', r.wait_resource) + 1) - CHARINDEX(':', r.wait_resource) - 1) AS INT),
    CAST(SUBSTRING(r.wait_resource, CHARINDEX(':', r.wait_resource, CHARINDEX(':', r.wait_resource) + 1) + 1, 8000) AS BIGINT), 'DETAILED') p
WHERE r.wait_type = 'PAGELATCH_SH'
GROUP BY database_id, file_id, page_id, page_type_desc, allocation_unit_type_desc
ORDER BY access_count DESC;

-- Check for small tables causing hot page access
SELECT 
    t.name as table_name,
    s.name as schema_name,
    p.rows,
    p.data_compression_desc,
    i.name as index_name,
    i.type_desc
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.partitions p ON t.object_id = p.object_id
INNER JOIN sys.indexes i ON t.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.rows < 10000 AND p.rows > 0
ORDER BY p.rows ASC;

Implement covering indexes to reduce page access frequency

-- Create covering indexes for frequently accessed small tables
-- Replace table_name and adjust columns based on your workload analysis
CREATE NONCLUSTERED INDEX IX_table_name_covering 
ON schema_name.table_name (primary_key_column)
INCLUDE (frequently_selected_column1, frequently_selected_column2, frequently_selected_column3)
WITH (ONLINE = ON, MAXDOP = 4);

This reduces the need to access the clustered index pages by providing all required data in the nonclustered index. Test thoroughly in development as covering indexes increase storage requirements and maintenance overhead.

Partition large tables experiencing root page contention

-- Create partition function and scheme for tables with hot root pages
CREATE PARTITION FUNCTION pf_date_range (datetime2)
AS RANGE RIGHT FOR VALUES 
('2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01');

CREATE PARTITION SCHEME ps_date_range 
AS PARTITION pf_date_range 
TO (fg_2024_01, fg_2024_02, fg_2024_03, fg_2024_04, fg_current);

-- Migrate clustered index onto the new partition scheme
-- WARNING: This requires significant downtime and planning. DROP_EXISTING is a
-- CREATE INDEX option (not ALTER INDEX REBUILD) — you must re-CREATE the index
-- to move it to a different partition scheme/filegroup.
CREATE UNIQUE CLUSTERED INDEX PK_your_table
    ON schema_name.your_table (date_column, pk_column)
    WITH (DROP_EXISTING = ON, ONLINE = ON)
    ON ps_date_range(date_column);

Partitioning distributes access across multiple root pages, reducing contention. This is a major structural change requiring extensive testing and maintenance window planning.

Enable page compression for small lookup tables

-- Estimate the savings BEFORE applying page compression using the built-in sproc
-- (the compression size columns come from sp_estimate_data_compression_savings,
-- not from sys.dm_db_index_operational_stats).
EXEC sp_estimate_data_compression_savings
    @schema_name      = N'schema_name',
    @object_name      = N'small_lookup_table',
    @index_id         = NULL,
    @partition_number = NULL,
    @data_compression = N'PAGE';

-- Enable page compression on small frequently accessed tables
-- This can reduce the number of pages and improve buffer pool efficiency
ALTER TABLE schema_name.small_lookup_table 
REBUILD WITH (DATA_COMPRESSION = PAGE);

Page compression reduces the physical footprint of small tables, potentially fitting more rows per page and reducing overall page access. Monitor CPU impact in production environments.

Optimize query patterns to reduce sequential access

-- Create filtered indexes for common WHERE clause patterns
-- This example assumes a status-based filtering pattern
CREATE NONCLUSTERED INDEX IX_table_name_active_status
ON schema_name.table_name (status_column, date_column)
WHERE status_column = 'ACTIVE'
WITH (ONLINE = ON);

-- Update statistics with higher sampling rate for small tables
UPDATE STATISTICS schema_name.small_table WITH SAMPLE 100 PERCENT;

Filtered indexes can eliminate the need to scan through inactive or irrelevant data, reducing page access patterns that cause contention.

Configure SQL Server with appropriate MAXDOP settings to prevent excessive parallelism on small table operations. Set MAXDOP to the number of physical cores per NUMA node, typically 8 or less. Use Resource Governor to limit parallelism for queries accessing small lookup tables.

Implement proper indexing strategies for frequently accessed small tables. Create covering indexes that include all columns referenced in common query patterns. Consider columnstore indexes for read-heavy analytical workloads on tables with high concurrency.

Monitor buffer pool efficiency through Performance Monitor counters, particularly Page Life Expectancy and Buffer Cache Hit Ratio. Ensure adequate memory allocation to keep frequently accessed pages in memory. Consider enabling indirect checkpoints to reduce checkpoint-related latch contention.

Design application data access patterns to avoid sequential processing of small lookup tables. Implement caching layers at the application tier for reference data that changes infrequently. Use table variables or temporary tables for repeated access to small result sets within stored procedures.

Establish baseline measurements for PAGELATCH_SH waits during normal operations. Set up alerts when wait times exceed 2x the baseline for sustained periods. Include page latch analysis in regular performance health checks, focusing on wait time trends rather than absolute numbers.

Consider database design patterns that minimize hot page access. Use GUIDs or hash-based partitioning for high-insert scenarios. Implement read-only database replicas for reporting workloads that access reference tables heavily.