LATCH_SH Wait Type Explained

Shared latches protect SQL Server's internal data structures from corruption during concurrent access. When a thread needs read access to structures like database metadata, allocation maps, or system catalog pages, it acquires a shared (SH) latch. Multiple threads can hold shared latches simultaneously, but exclusive operations must wait for all shared latches to release.

The SQLOS scheduler manages latch acquisition through a lightweight synchronization primitive that operates below the lock manager layer. Latches differ from locks because they protect physical structures rather than logical data, are held for microseconds rather than transaction duration, and use different escalation patterns.

Common internal structures protected by shared latches include allocation bitmap pages (PFS, GAM, SGAM), database file headers, metadata pages in system databases, and page directory structures. High LATCH_SH waits typically indicate one of three scenarios: excessive schema lookups causing metadata contention, allocation page hotspots during heavy insert operations, or system database contention from frequent DDL operations.

SQL Server 2016 introduced significant improvements to allocation algorithms that reduced some traditional LATCH_SH bottlenecks. SQL Server 2019 added intelligent query processing features that cache metadata more effectively. SQL Server 2022 further optimized allocation patterns for columnstore and in-memory workloads, reducing allocation page contention.

-- Identify top latch wait classes and specific latch types
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 latch_class LIKE '%ALLOC%' 
   OR latch_class LIKE '%METADATA%'
   OR latch_class LIKE '%DATABASE%'
ORDER BY wait_time_ms DESC;

-- Find current latch waits and blocking patterns
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    t.text AS current_sql,
    r.database_id,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LATCH_SH'
ORDER BY r.wait_time DESC;

-- Analyze allocation page contention by database
SELECT 
    DB_NAME(database_id) AS database_name,
    file_id,
    page_id,
    wait_type,
    wait_duration_ms,
    wait_resource
FROM sys.dm_exec_requests r
WHERE wait_type = 'LATCH_SH' 
  AND wait_resource LIKE '%PAGE%'
ORDER BY wait_duration_ms DESC;

-- Check for metadata access patterns causing contention
SELECT TOP 20
    s.session_id,
    s.login_name,
    s.program_name,
    r.command,
    r.wait_type,
    r.wait_time,
    t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LATCH_SH'
  AND (t.text LIKE '%sys.%' OR t.text LIKE '%INFORMATION_SCHEMA%')
ORDER BY r.wait_time DESC;

-- Monitor allocation unit contention over time
SELECT 
    counter_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%Access Methods%'
  AND (counter_name LIKE '%Page Splits%' 
       OR counter_name LIKE '%Extent Deallocations%'
       OR counter_name LIKE '%Extent Allocations%')
ORDER BY counter_name;

Reduce allocation page contention with multiple data files

-- Add additional data files to distribute allocation load
-- Test in development first, measure allocation patterns before and after
USE [YourDatabase]
GO

-- Check current file configuration
SELECT 
    name, 
    physical_name, 
    size/128 AS size_mb,
    growth/128 AS growth_mb
FROM sys.database_files 
WHERE type = 0; -- Data files only

-- Add files (adjust paths and sizes for your environment)
ALTER DATABASE [YourDatabase] 
ADD FILE (
    NAME = 'YourDatabase_Data2',
    FILENAME = 'C:\Data\YourDatabase_Data2.mdf',
    SIZE = 1024MB,
    FILEGROWTH = 256MB
);
-- Repeat for CPU core count / 4 files, maximum 8 files for most workloads

Enable trace flag for allocation improvements (SQL Server 2016+)

-- Enable global trace flag 1118 to reduce allocation contention
-- Warning: Test thoroughly in development, affects all databases
-- SQL Server 2016+ has this behavior by default for new databases
DBCC TRACEON(1118, -1);

-- Verify trace flag status
DBCC TRACESTATUS(1118);

-- For new databases in SQL Server 2016+, ensure mixed extent allocation is disabled
ALTER DATABASE [YourDatabase] SET MIXED_PAGE_ALLOCATION OFF;

Optimize tempdb configuration for allocation reduction

-- Configure tempdb with proper file count and sizing
-- This script generates commands, review before executing
-- Best practice: One file per CPU core up to 8 files

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 @sql NVARCHAR(MAX) = '';
DECLARE @i INT = 1;

-- Generate ALTER DATABASE commands for tempdb files
WHILE @i <= @file_count
BEGIN
    SET @sql = @sql + 'ALTER DATABASE tempdb MODIFY FILE (
        NAME = tempdev' + CASE WHEN @i = 1 THEN '' ELSE CAST(@i AS VARCHAR) END + ',
        SIZE = 1024MB,
        FILEGROWTH = 256MB);' + CHAR(13);
    SET @i = @i + 1;
END

PRINT @sql;
-- Review generated SQL, then execute manually after restart

Implement metadata caching to reduce schema lookup waits

-- Enable plan cache optimization for metadata-heavy workloads
-- These settings reduce repeated schema lookups
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

-- For SQL Server 2019+, enable intelligent query processing
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;

-- Monitor impact on plan cache and metadata access patterns
SELECT 
    cacheobjtype,
    objtype,
    COUNT(*) AS plan_count,
    SUM(usecounts) AS total_uses
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
ORDER BY total_uses DESC;

Configure multiple data files per filegroup to distribute allocation page load across parallel structures. Size files equally and set identical growth rates to ensure proportional fill algorithms work effectively. For high-transaction databases, implement 4-8 data files regardless of CPU count to minimize allocation bottlenecks.

Set up monitoring for allocation-related performance counters including page splits, extent allocations, and extent deallocations per second. Establish baselines and alert when allocation rates exceed normal patterns. Monitor LATCH_SH waits specifically during peak business hours when allocation contention typically manifests.

Design applications to minimize schema lookups through connection pooling, prepared statements, and cached metadata access patterns. Avoid frequent DDL operations during peak hours, batch schema changes during maintenance windows, and implement application-level metadata caching where appropriate.

Configure tempdb with CPU-count files (maximum 8) sized identically with matching growth rates. Enable instant file initialization and place tempdb files on fast storage with adequate space pre-allocated. This prevents allocation storms that cascade into user database latch contention.