Monitoring Always On Availability Groups in SQL Server
In this post, I’ll explain some commonly used internal SQL Server objects and queries for monitoring the health and performance of Always-On Availability Groups.
Always On Health and Status Overview
The following query gives a detailed overview of the health and synchronization status of availability groups:
SELECT ag.name AS [AG Name],
ar.replica_server_name,
ar.availability_mode_desc,
adc.database_name,
drs.is_local,
drs.is_primary_replica,
drs.synchronization_state_desc,
drs.is_commit_participant,
drs.synchronization_health_desc,
drs.recovery_lsn,
drs.truncation_lsn,
drs.last_sent_lsn,
drs.last_sent_time,
drs.last_received_lsn,
drs.last_received_time,
drs.last_hardened_lsn,
drs.last_hardened_time,
drs.last_redone_lsn,
drs.last_redone_time,
drs.log_send_queue_size,
drs.log_send_rate,
drs.redo_queue_size,
drs.redo_rate,
drs.filestream_send_rate,
drs.end_of_log_lsn,
drs.last_commit_lsn,
drs.last_commit_time,
drs.database_state_desc
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, adc.database_name
OPTION (RECOMPILE);
Show Availability Groups Where This Server Is Secondary
SELECT Groups.name AS AGname,
primary_replica
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups
ON States.group_id = Groups.group_id
WHERE primary_replica != @SERVERNAME;
Show Databases in AG Where This Server Is Primary
SELECT Groups.name AS AGname,
AGD.database_name
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups
ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGD
ON Groups.group_id = AGD.group_id
WHERE primary_replica = @SERVERNAME
ORDER BY AGname, database_name;
Replica and Availability Group Status
WITH AGStatus AS (
SELECT name AS AGname,
replica_server_name,
CASE WHEN (primary_replica = replica_server_name) THEN 1 ELSE 0 END AS IsPrimaryServer,
secondary_role_allow_connections_desc AS ReadableSecondary,
availability_mode AS Synchronous,
failover_mode_desc
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas
ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States
ON Groups.group_id = States.group_id
)
SELECT AGname,
Replica_server_name,
IsPrimaryServer,
Synchronous,
ReadableSecondary,
Failover_mode_desc
FROM AGStatus
ORDER BY AGname, IsPrimaryServer DESC;
All AG Availability Group Status
SELECT AG.name AS AvailabilityGroupName,
AR.replica_server_name AS AvailabilityReplicaServerName,
dbcs.database_name AS AvailabilityDatabaseName,
ISNULL(dbcs.is_failover_ready, 0) AS IsFailoverReady,
ISNULL(arstates.role_desc, 3) AS ReplicaRole,
AR.availability_mode_desc AS AvailabilityMode
ORDER BY AvailabilityReplicaServerName, AvailabilityDatabaseName;
(Note: Long query shortened for brevity—see full script for details.)
Performance and Estimation Queries
SELECT AG.name AS AvailabilityGroupName,
AR.replica_server_name AS AvailabilityReplicaServerName,
dbcs.database_name AS AvailabilityDatabaseName
ORDER BY AvailabilityDatabaseName;
AG Log Reads
EXEC sp_readerrorlog 0, 1, 'The state of the local availability replica', '';
EXEC sp_readerrorlog 0, 1, 'RESOLVING';
Internal SQL Server Objects for Always On
SELECT * FROM sys.dm_hadr_cluster_members;
SELECT * FROM sys.dm_hadr_cluster;
SELECT * FROM sys.dm_hadr_cluster_networks;
SELECT * FROM sys.dm_hadr_auto_page_repair;
...
SELECT * FROM sys.availability_groups;
SQL Server Extended Events for Always On
SELECT xml_data.value('(event/@name)[1]', 'varchar(max)') AS Name,
...
FROM (
SELECT object_name, CONVERT(xml, event_data) AS xml_data
FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', NULL, NULL, NULL)
) AS XEventData
ORDER BY Time;
Stored Procedure: Calculate RPO
CREATE PROCEDURE uspCalculateRPO (
@group_id UNIQUEIDENTIFIER,
@replica_id UNIQUEIDENTIFIER,
@group_database_id UNIQUEIDENTIFIER
)
AS
BEGIN
...
END
Stored Procedure: Calculate RTO
CREATE PROCEDURE uspCalculateRTO (@secondary_database_name NVARCHAR(MAX))
AS
BEGIN
...
END
