HADR_COMPRESSED_CACHE_SYNC Wait Type Explained

SQL Server maintains a compressed log block cache to optimize Always On log shipping performance. When the primary replica processes transactions, it compresses log blocks once and stores them in this cache rather than compressing the same blocks multiple times for each secondary replica. The HADR_COMPRESSED_CACHE_SYNC wait occurs when threads compete for exclusive access to read from or write to this cache structure.

The compression cache uses a lightweight internal synchronization mechanism that can become a bottleneck under specific conditions: high transaction throughput with multiple secondary replicas, mixed synchronous and asynchronous replicas with different compression requirements, or when log blocks are being evicted and recreated frequently due to memory pressure.

In SQL Server 2016, Microsoft improved the cache efficiency by implementing better hash distribution and reducing lock granularity. SQL Server 2019 introduced further optimizations with parallel log compression capabilities, but paradoxically this can increase cache contention under certain workloads. SQL Server 2022 added adaptive compression algorithms that dynamically adjust based on replica lag, reducing some scenarios where this wait occurs.

The wait manifests differently based on availability group topology. Single secondary configurations rarely experience significant waits, while configurations with 3+ secondaries, especially with mixed synchronization modes, create more cache pressure. The scheduler assignment for Always On worker threads also influences contention patterns, particularly on NUMA systems where cross-node memory access amplifies the issue.

-- Check current Always On replica configuration and lag
SELECT 
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ars.role_desc,
    ars.synchronization_state_desc,
    ars.log_send_queue_size,
    ars.log_send_rate,
    ars.redo_queue_size
FROM sys.availability_replicas ar
JOIN sys.dm_hadr_availability_replica_states ars 
    ON ar.replica_id = ars.replica_id;

-- Analyze wait statistics for HADR compression waits
SELECT 
    ws.wait_type,
    ws.waiting_tasks_count,
    ws.wait_time_ms,
    ws.max_wait_time_ms,
    ws.signal_wait_time_ms,
    CAST(100.0 * ws.wait_time_ms / SUM(ws.wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct_of_waits
FROM sys.dm_os_wait_stats ws
WHERE ws.wait_type LIKE 'HADR%COMPRESS%'
    OR ws.wait_type = 'HADR_COMPRESSED_CACHE_SYNC'
ORDER BY ws.wait_time_ms DESC;

-- Monitor current Always On worker threads and cache pressure
SELECT 
    s.session_id,
    r.command,
    r.status,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.cpu_time,
    s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.program_name LIKE 'AlwaysOn%'
    OR r.command LIKE '%HADR%';

-- Check memory pressure on buffer pool affecting cache efficiency
SELECT 
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
    AND counter_name IN ('Buffer cache hit ratio', 'Page life expectancy', 'Lazy writes/sec')
UNION ALL
SELECT 
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Availability Replica%'
    AND counter_name LIKE '%Bytes Sent%';

-- Examine transaction log generation rate and patterns
SELECT 
    db.name AS database_name,
    ls.log_since_last_log_backup_mb,
    ls.log_backup_frequency_min,
    ls.log_generation_rate_mb_sec
FROM sys.databases db
CROSS APPLY (
    SELECT 
        (cntr_value/1024.0) AS log_since_last_log_backup_mb,
        DATEDIFF(MINUTE, backup_start_date, GETDATE()) AS log_backup_frequency_min,
        CASE WHEN DATEDIFF(SECOND, backup_start_date, GETDATE()) > 0
            THEN (cntr_value/1024.0) / DATEDIFF(SECOND, backup_start_date, GETDATE())
            ELSE 0 END AS log_generation_rate_mb_sec
    FROM sys.dm_os_performance_counters pc
    CROSS JOIN (SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset 
                WHERE database_name = db.name AND type = 'L' 
                ORDER BY backup_start_date DESC) bs
    WHERE pc.object_name LIKE '%Databases%'
        AND pc.counter_name = 'Log File(s) Size (KB)'
        AND pc.instance_name = db.name
) ls
WHERE db.database_id IN (SELECT database_id FROM sys.dm_hadr_availability_replica_states);

Reduce secondary replica count temporarily

-- Remove non-essential readable secondaries during peak load
-- WARNING: Test failover scenarios before removing replicas
ALTER AVAILABILITY GROUP [YourAGName]
REMOVE REPLICA ON N'SecondaryServer3';
-- Monitor wait reduction, re-add replica during off-peak hours

This script reduces cache contention by decreasing the number of replicas competing for compressed log blocks. Test disaster recovery procedures before implementation as this reduces redundancy.

Adjust log backup frequency

-- Increase log backup frequency to reduce cache pressure
-- Create more frequent log backup job (every 2-5 minutes instead of 15+)
BACKUP LOG [YourDatabase] 
TO DISK = N'\\BackupShare\YourDatabase_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.trn'
WITH COMPRESSION, CHECKSUM, INIT;
-- Implement via SQL Agent job with 2-minute intervals

More frequent log backups reduce the volume of log records cached for compression, decreasing memory pressure on the compression cache. Monitor backup storage growth and adjust retention policies accordingly.

Optimize Always On worker thread configuration

-- Adjust max worker threads if server has high core count
-- Calculate optimal value: (CPU cores * 2) + additional headroom
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max worker threads', 1024; -- Adjust based on core count
RECONFIGURE;
-- Restart SQL Server service to apply threading changes

Increasing worker threads can reduce thread starvation that exacerbates cache contention. Calculate based on physical cores, not logical processors. Monitor thread pool health after changes.

Configure Always On availability groups with replica topology awareness. Limit readable secondaries to essential requirements, particularly in high-transaction environments. Position synchronous replicas geographically close to minimize network latency that can amplify cache pressure.

Implement aggressive log backup schedules (2-5 minute intervals) to reduce log volume cached for compression. Monitor transaction log growth patterns and optimize application batch sizes to avoid large single transactions that create compression hotspots.

Size buffer pool memory appropriately to prevent cache eviction pressure that forces recompression of log blocks. On NUMA systems, ensure Always On worker threads have proper NUMA node affinity to reduce cross-node memory access overhead.

Establish baseline monitoring for HADR compression waits using sys.dm_os_wait_stats. Alert when wait ratios exceed 5% of total waits or when max_wait_time_ms consistently exceeds 100ms. Correlation with replica lag metrics provides early warning of developing issues.

Consider SQL Server 2022's adaptive compression features for new implementations, as they dynamically adjust compression strategies based on replica performance characteristics. For existing systems, evaluate upgrade benefits against current wait patterns.