SQL Server
Services
Redefined.

Surfaces replication synchronization health, log-send queue bottlenecks, and redo queue lag threatening RPO on secondary replicas.

-- Inspect Always On AG replica synchronization state and redo lag
SELECT ar.replica_server_name, adc.database_name, drs.synchronization_state_desc,
       drs.synchronization_health_desc, drs.log_send_queue_size, drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_databases_cluster adc ON drs.group_database_id = adc.group_database_id;

Isolates top system wait types and pinpoints active PAGELATCH_UP waits on TempDB (database_id 2) PFS and SGAM pages.

-- Detect top wait types impacting instance throughput
SELECT TOP 10 wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK', 'BROKER_TASK_STOP', 'WAITFOR', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION')
ORDER BY wait_time_ms DESC;

-- Identify active TempDB (DB ID 2) allocation page latch contention
SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%';