Dynamic management views and system catalog views (Always On Availability Groups)
Applies to: 
SQL Server
This topic shows you some of the common queries on the Always On dynamic management views (DMV) that you can use to monitor and troubleshoot availability groups.
Tip
In the Always On Dashboard, you can easily configure the GUI to display many of the DMVs for the availability replicas and availability databases by right-clicking the respective table header and selecting the DMV you wish to display or hide.
For more information on the availability group DMVs, see Always On Availability Groups dynamic management views and functions (Transact-SQL). For more information on the availability groups catalog views, see Always On Availability Groups catalog views (Transact-SQL).
Check the WSFC cluster node configuration
The following Transact-SQL (T-SQL) query retrieves the status of all the nodes in the current Windows Server Failover Clustering (WSFC) cluster.
use master
go
select * from sys.dm_hadr_cluster_members
go
This result set reports the status of each member node of the current WSFC cluster. If the quorum is defined as Node and File Share Majority, even the file share is reported. You can see the status of each node, including the voting weight of each node (the number_of_quorum_votes value).
Explore the cluster network
The following query retrieves the network configuration of the current WSFC cluster.
select * from sys.dm_hadr_cluster_networks
The result set contains one row for each network adapter in the WSFC cluster. For example, in a two-node cluster that contains two network adapters on each node, this query returns four rows.
Explore the availability groups
The following query retrieves information about an availability group.
select primary_replica, primary_recovery_health_desc, synchronization_health_desc from sys.dm_hadr_availability_group_states
go
select * from sys.availability_groups
go
select * from sys.availability_groups_cluster
go
The DMVs sys.dm_hadr_availability_group_states (Transact-SQL), sys.availability_groups (Transact-SQL), and sys.availability_groups_cluster all return information about the availability groups in the current WSFC cluster. In fact, sys.availability_groups (Transact-SQL), and sys.availability_groups_cluster seem to return identical information.
However, sys.availability_groups_cluster reports availability group metadata stored in the WSFC Cluster, whereas sys.availability_groups (Transact-SQL) reports availability group metadata that is cached in the SQL Server process space. Furthermore, these two DMVs report configuration information whereas sys.dm_hadr_availability_group_states (Transact-SQL) reports the current health statuses of the availability groups.
Important
This nomenclature carries forward with the DMVs that document availability replicas and availability databases.
Explore the availability replicas
The following query retrieves information about the availability replicas defined in your availability groups.
select replica_id, role_desc, connected_state_desc, synchronization_health_desc from sys.dm_hadr_availability_replica_states
go
select replica_server_name, replica_id, availability_mode_desc, endpoint_url from sys.availability_replicas
go
select replica_server_name, join_state_desc from sys.dm_hadr_availability_replica_cluster_states
go
Similar to the availability group DMVs, you find three DMVs that report on availability replicas. sys.dm_hadr_availability_replica_states reports state information about the availability replicas that is locally cached in SQL Server, and sys.dm_hadr_availability_replica_cluster_states reports state information about the availability replicas from the WSFC cluster. Finally, sys.availability_replicas reports configuration data on the availability replicas, which are cached locally in SQL Server.
Explore availability replica health
The following query retrieves current health information about the availability replicas.
select replica_id, role_desc, recovery_health_desc, synchronization_health_desc from sys.dm_hadr_availability_replica_states
go
Compare the query results on the primary replica and on the secondary replica and note that on the secondary replica, health information is reported only for that replica and not for any other replica in the availability group.
Explore the availability databases
The following query retrieves information about the availability replicas defined in your availability group. You can observe the change in the query results before and after you suspend data movement on an availability database.
select * from sys.availability_databases_cluster
go
select group_database_id, database_name, is_failover_ready from sys.dm_hadr_database_replica_cluster_states
go
select database_id, synchronization_state_desc, synchronization_health_desc, last_hardened_lsn, redo_queue_size, log_send_queue_size from sys.dm_hadr_database_replica_states
go
Here again, three Always On DMVs report on availability databases. sys.availability_databases_cluster reports configuration information about availability databases from the WSFC cluster. sys.dm_hadr_database_replica_cluster_states reports state information about the database replicas, which are locally cached in SQL Server. It contains some important state information, such as the availability replica's failover readiness. Finally, sys.dm_hadr_database_replica_states is a very verbose result set which reports identity and state information on each availability database, such as LSN progress information for the logs of the primary and secondary database replicas.
Explore availability database health
The following query retrieves information about the health of each availability databases on the replicas. You can observe the change in the query results before and after you suspend data movement on an availability database.
select dc.database_name, dr.database_id, dr.synchronization_state_desc,
dr.suspend_reason_desc, dr.synchronization_health_desc
from sys.dm_hadr_database_replica_states dr join sys.availability_databases_cluster dc
on dr.group_database_id=dc.group_database_id
where is_local=1
go