PAGELATCH_EX Wait Type Explained

PAGELATCH_EX waits happen when the Buffer Pool Manager cannot grant exclusive page latches to threads attempting to modify pages already cached in the buffer pool. Unlike PAGEIOLATCH waits, these pages are already in memory but contended by multiple concurrent operations.

The most frequent scenario involves last-page insert contention on tables with ascending clustered indexes (IDENTITY, datetime, GUID with NEWSEQUENTIALID). When multiple sessions insert simultaneously, they target the same rightmost page in the B-tree structure. Each session requests an EX latch on this page to modify it, creating a convoy effect where threads queue waiting for latch release.

SQL Server's latch manager maintains a hash table of latch structures in memory. When contention occurs, threads enter a FIFO queue associated with the specific page's latch structure. The Buffer Pool Manager must serialize access because page modifications require exclusive control to maintain ACID properties and prevent torn page writes.

Secondary scenarios include small "hot" tables where random inserts concentrate on a limited number of pages, and heap tables with non-clustered indexes experiencing concurrent INSERT/UPDATE operations on the same physical pages.

SQL Server 2016 introduced improved latch-free page allocation algorithms that reduced some forms of allocation page contention. SQL Server 2019 enhanced buffer pool scalability with optimized latch structures. SQL Server 2022 added Intelligent Query Processing features that can influence query plans to reduce hotspot formation.

-- Identify specific pages experiencing PAGELATCH_EX contention
SELECT 
    resource_description,
    COUNT(*) as wait_count,
    SUM(wait_duration_ms) as total_wait_ms
FROM sys.dm_os_waiting_tasks 
WHERE wait_type = 'PAGELATCH_EX'
GROUP BY resource_description
ORDER BY total_wait_ms DESC;

-- Find tables with highest page latch contention
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) as schema_name,
    OBJECT_NAME(p.object_id) as table_name,
    i.name as index_name,
    i.type_desc as index_type,
    COUNT(*) as latch_waits
FROM sys.dm_os_waiting_tasks w
    CROSS APPLY (
        SELECT CAST(RIGHT(resource_description, LEN(resource_description) - CHARINDEX(':', resource_description, 6)) AS INT) as page_id,
               CAST(SUBSTRING(resource_description, 1, CHARINDEX(':', resource_description) - 1) AS INT) as database_id,
               CAST(SUBSTRING(resource_description, CHARINDEX(':', resource_description) + 1, 
                     CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) - CHARINDEX(':', resource_description) - 1) AS INT) as file_id
    ) r
    JOIN sys.dm_db_page_info(r.database_id, r.file_id, r.page_id, 'LIMITED') p ON 1=1
    JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE w.wait_type = 'PAGELATCH_EX'
GROUP BY p.object_id, i.name, i.type_desc
ORDER BY latch_waits DESC;

-- Analyze insert patterns for last-page contention
SELECT 
    OBJECT_SCHEMA_NAME(s.object_id) as schema_name,
    OBJECT_NAME(s.object_id) as table_name,
    i.name as index_name,
    s.leaf_allocation_count,
    s.leaf_page_merge_count,
    s.range_scan_count,
    s.singleton_lookup_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
    JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.leaf_allocation_count > 1000 -- High allocation suggests insert activity
ORDER BY s.leaf_allocation_count DESC;

-- Monitor current PAGELATCH_EX waits in real-time
SELECT 
    w.session_id,
    w.wait_type,
    w.wait_duration_ms,
    w.resource_description,
    w.blocking_session_id,
    t.text as sql_text
FROM sys.dm_os_waiting_tasks w
    LEFT JOIN sys.dm_exec_requests r ON r.session_id = w.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE w.wait_type = 'PAGELATCH_EX'
ORDER BY w.wait_duration_ms DESC;

-- Historical analysis of PAGELATCH_EX impact
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_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'PAGELATCH_EX';

Enable trace flag 1118 and optimize tempdb configuration for allocation contention:

-- Configure multiple tempdb data files to reduce allocation contention
-- TEST IN DEV FIRST: This requires SQL Server restart
USE master;
GO

-- Check current tempdb configuration
SELECT 
    name,
    physical_name,
    size * 8 / 1024 as size_mb,
    growth
FROM sys.master_files 
WHERE database_id = 2;

-- Add additional tempdb data files (adjust paths and sizes)
-- Rule: Number of files = CPU cores up to 8, then add by 4s if needed
-- ALTER DATABASE tempdb ADD FILE (
--     NAME = 'tempdev2',
--     FILENAME = 'C:\TempDB\tempdev2.mdf',
--     SIZE = 1024MB,
--     FILEGROWTH = 256MB
-- );

-- Enable trace flag 1118 to use uniform extents (SQL 2016+ default)
-- DBCC TRACEON(1118, -1);

Implement table partitioning to distribute insert load:

-- Create partition function and scheme for hot table
-- WARNING: Test thoroughly in development, requires table rebuild
USE [YourDatabase];
GO

-- Example for date-based partitioning to reduce last-page contention
CREATE PARTITION FUNCTION pf_DateRange (DATETIME2)
AS RANGE RIGHT FOR VALUES (
    '2024-01-01', '2024-02-01', '2024-03-01', 
    '2024-04-01', '2024-05-01', '2024-06-01'
);

CREATE PARTITION SCHEME ps_DateRange
AS PARTITION pf_DateRange
ALL TO ([PRIMARY]); -- Or distribute across multiple filegroups

-- Rebuild clustered index on partition scheme
-- ALTER TABLE [YourHotTable] 
-- DROP CONSTRAINT [PK_YourHotTable];
-- 
-- ALTER TABLE [YourHotTable] 
-- ADD CONSTRAINT [PK_YourHotTable] 
-- PRIMARY KEY CLUSTERED ([ID], [DateColumn])
-- ON ps_DateRange([DateColumn]);

Add non-sequential GUID clustering for high-concurrency inserts:

-- Replace IDENTITY or sequential keys with NEWID() for random distribution
-- CAUTION: This fragments indexes but eliminates last-page contention
-- Only use when insert concurrency is more critical than query performance

-- Example table modification (requires careful planning)
/*
ALTER TABLE [YourHotTable] 
ADD [RowGuid] UNIQUEIDENTIFIER NOT NULL 
DEFAULT (NEWID());

-- Create new clustered index on GUID
CREATE UNIQUE CLUSTERED INDEX [CIX_YourHotTable_RowGuid] 
ON [YourHotTable] ([RowGuid]);

-- Drop old identity-based clustered index
-- ALTER TABLE [YourHotTable] DROP CONSTRAINT [PK_YourHotTable];
*/

Implement hash partitioning for small hot tables:

-- Create computed column with hash function for even distribution
-- This works well for queue tables and small lookup tables
ALTER TABLE [YourSmallHotTable]
ADD [HashValue] AS (ABS(CHECKSUM([SomeColumn])) % 16) PERSISTED;

-- Create partition function based on hash values
CREATE PARTITION FUNCTION pf_HashPartition (INT)
AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14);

CREATE PARTITION SCHEME ps_HashPartition
AS PARTITION pf_HashPartition
ALL TO ([PRIMARY]);

-- Rebuild table on partition scheme
-- Planning required: This is a major structural change

Configure tempdb with multiple equally-sized data files matching CPU core count up to 8 files, then add in increments of 4. Set initial size to prevent autogrowth during peak operations. Enable instant file initialization for faster allocation.

Avoid clustering on ascending keys (IDENTITY, GETDATE(), NEWSEQUENTIALID()) for high-concurrency insert workloads. Use NEWID() for random distribution or implement custom sequencing with multiple ranges.

Implement table partitioning based on insert patterns. Date-based partitioning works well for time-series data, while hash partitioning suits random access patterns. Distribute partitions across multiple filegroups on separate storage to parallelize I/O.

Monitor page allocation patterns using sys.dm_db_index_operational_stats. Set alerts when leaf_allocation_count exceeds normal thresholds for specific tables. Track PAGELATCH_EX wait statistics with automated monitoring.

Design queue table architectures with multiple smaller tables or use Service Broker for high-throughput scenarios. Implement read-committed snapshot isolation (RCSI) to reduce reader-writer conflicts that exacerbate latch contention.

Consider columnstore indexes for insert-heavy analytical workloads, as they use different locking patterns and compress data more efficiently than traditional B-tree structures.