LATCH_EX Wait Type Explained

Exclusive latches protect SQL Server's internal data structures from concurrent modification. Unlike locks which protect user data, latches are lightweight synchronization primitives that guard critical system structures for microsecond durations. The SQLOS scheduler grants LATCH_EX when a worker thread needs exclusive access to modify structures like PFS (Page Free Space) pages, GAM (Global Allocation Map) pages, SGAM (Shared Global Allocation Map) pages, or database file headers.

LATCH_EX contention typically manifests in three scenarios. First, tempdb allocation storms occur when multiple sessions simultaneously request new pages, creating bottlenecks on allocation bitmaps. This was partially addressed in SQL Server 2016 with multiple tempdb data files and proportional fill algorithms, then further improved in SQL Server 2019 with optimized tempdb metadata memory allocation.

Second, database growth operations trigger LATCH_EX waits when auto-growth events serialize access to file headers and allocation structures. Multiple concurrent growth operations on the same filegroup create cascading waits as each operation must exclusively lock allocation metadata.

Third, statistics updates and schema modifications generate LATCH_EX contention on system catalog pages. Concurrent automatic statistics updates on heavily modified tables can amplify this contention pattern.

Buffer pool scan operations in SQL Server 2016 and later versions use more granular latching strategies compared to earlier versions, but checkpoint processes and lazy writer operations still require exclusive latches on buffer descriptors during page eviction cycles.

-- Identify current LATCH_EX waits by resource
SELECT 
    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
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LATCH_EX'

-- Find current sessions waiting on LATCH_EX
SELECT 
    s.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    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 = 'LATCH_EX'

-- Examine latch statistics by latch class
SELECT 
    latch_class,
    waiting_requests_count,
    wait_time_ms,
    max_wait_time_ms,
    wait_time_ms / NULLIF(waiting_requests_count, 0) AS avg_wait_ms
FROM sys.dm_os_latch_stats
WHERE waiting_requests_count > 0
ORDER BY wait_time_ms DESC

-- Check tempdb allocation contention specifically  
SELECT 
    database_id,
    file_id,
    page_type_desc,
    page_level,
    avg_page_space_used_in_percent,
    record_count,
    ghost_record_count
FROM sys.dm_db_index_physical_stats(2, NULL, NULL, NULL, 'DETAILED')
WHERE database_id = 2 AND page_type_desc IN ('PFS_PAGE', 'GAM_PAGE', 'SGAM_PAGE')

-- Monitor database file growth events from the default trace
SELECT 
    te.name AS event_name,
    t.DatabaseName AS database_name,
    t.FileName AS file_name,
    t.IntegerData * 8 / 1024 AS growth_mb,
    t.Duration / 1000 AS duration_ms,
    t.StartTime AS start_time
FROM sys.fn_trace_gettable(
    (SELECT REVERSE(SUBSTRING(REVERSE(path), 
        CHARINDEX('\', REVERSE(path)), 260)) + 'log.trc'
     FROM sys.traces WHERE is_default = 1), DEFAULT) t
INNER JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE t.EventClass IN (92, 93) -- Data File Auto Grow, Log File Auto Grow
AND t.StartTime > DATEADD(HOUR, -4, GETDATE())
ORDER BY t.StartTime DESC;

Add Multiple TempDB Data Files Creates additional tempdb data files to reduce allocation contention. Each CPU core should have one tempdb data file up to 8 files maximum.

-- Add tempdb data files (run during maintenance window)
USE master
GO
-- Calculate optimal file count (CPU cores, max 8)
DECLARE @cpu_count INT = (SELECT cpu_count FROM sys.dm_os_sys_info)
DECLARE @file_count INT = CASE WHEN @cpu_count > 8 THEN 8 ELSE @cpu_count END
DECLARE @counter INT = 2 -- Start from file 2 (file 1 already exists)

WHILE @counter <= @file_count
BEGIN
    DECLARE @sql NVARCHAR(500) = 
        'ALTER DATABASE tempdb ADD FILE (
            NAME = ''tempdev' + CAST(@counter AS VARCHAR(2)) + ''',
            FILENAME = ''C:\YourTempDBPath\tempdb' + CAST(@counter AS VARCHAR(2)) + '.mdf'',
            SIZE = 1024MB,
            FILEGROWTH = 512MB
        )'
    EXEC sp_executesql @sql
    SET @counter = @counter + 1
END

Configure Proportional Fill and Growth Settings Sets consistent initial sizes and growth increments across all tempdb files to ensure proportional fill algorithm works effectively.

-- Resize all tempdb files to same initial size
USE master
GO
DECLARE @size_mb INT = 4096 -- 4GB initial size
DECLARE @growth_mb INT = 512 -- 512MB growth increment

-- Get all tempdb data files
DECLARE file_cursor CURSOR FOR
SELECT file_id, name FROM sys.master_files 
WHERE database_id = 2 AND type = 0

DECLARE @file_id INT, @file_name SYSNAME, @sql NVARCHAR(500)

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @file_id, @file_name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER DATABASE tempdb MODIFY FILE (
        NAME = ''' + @file_name + ''',
        SIZE = ' + CAST(@size_mb AS VARCHAR(10)) + 'MB,
        FILEGROWTH = ' + CAST(@growth_mb AS VARCHAR(10)) + 'MB
    )'
    EXEC sp_executesql @sql
    FETCH NEXT FROM file_cursor INTO @file_id, @file_name
END

CLOSE file_cursor
DEALLOCATE file_cursor

Pre-size Database Files to Prevent Auto-Growth Eliminates auto-growth events that cause LATCH_EX contention on allocation structures.

-- Pre-grow database files based on growth patterns
-- Replace 'YourDatabase' with actual database name
USE master
GO
DECLARE @db_name SYSNAME = 'YourDatabase'
DECLARE @data_size_mb INT = 10240 -- 10GB
DECLARE @log_size_mb INT = 2048   -- 2GB

DECLARE @sql NVARCHAR(1000) = 
    'ALTER DATABASE [' + @db_name + '] MODIFY FILE (
        NAME = ''' + @db_name + ''',
        SIZE = ' + CAST(@data_size_mb AS VARCHAR(10)) + 'MB,
        FILEGROWTH = 1024MB
    )'
EXEC sp_executesql @sql

SET @sql = 
    'ALTER DATABASE [' + @db_name + '] MODIFY FILE (
        NAME = ''' + @db_name + '_Log'',
        SIZE = ' + CAST(@log_size_mb AS VARCHAR(10)) + 'MB,
        FILEGROWTH = 512MB
    )'
EXEC sp_executesql @sql

Configure tempdb with one data file per CPU core (maximum 8 files) sized equally with identical growth settings. Place tempdb files on fast storage with sufficient free space to prevent auto-growth events. Monitor tempdb allocation patterns using sys.dm_db_file_space_usage and sys.dm_db_session_space_usage.

Pre-size user databases based on growth projections to eliminate auto-growth contention. Set reasonable growth increments (fixed MB rather than percentages) and monitor using database growth Extended Events. Configure database auto-growth alerts at 80% capacity thresholds.

Implement statistics update maintenance windows for large tables to prevent concurrent statistics modifications. Use trace flag 2371 in SQL Server 2016+ to enable dynamic statistics update thresholds on large tables, reducing frequency of automatic updates.

Monitor LATCH_EX waits using Performance Monitor counters and query store wait statistics. Establish baseline measurements and alert when average wait times exceed 20ms or total wait time increases by 200% week-over-week. Configure Extended Events sessions to capture latch timeout events for detailed analysis.

Place high-concurrency databases on separate storage volumes to isolate I/O patterns. Consider partitioning strategies for tables with heavy insert activity to distribute allocation operations across multiple filegroups.