Skip to content

always-on-availability-groups-dynamic-management-views-functions.md

Latest commit

 

History

History
93 lines (68 loc) · 5.36 KB

always-on-availability-groups-dynamic-management-views-functions.md

File metadata and controls

93 lines (68 loc) · 5.36 KB
title Always On availability groups dynamic management views and functions
description Always On availability groups dynamic management views and functions for monitoring availability groups, replicas, and databases.
author rwestMSFT
ms.author randolphwest
ms.date 02/24/2026
ms.service sql
ms.subservice system-objects
ms.topic reference
ai-usage ai-assisted
helpviewer_keywords
dynamic management views [SQL Server], AlwaysOn Availability Groups
dynamic management views [SQL Server], Always On Availability Groups
dev_langs
TSQL

Always On availability groups dynamic management views and functions

[!INCLUDE SQL Server]

This section contains the dynamic management views (DMVs) and functions that are related to [!INCLUDEssHADR].

Availability group monitoring

These DMVs provide information about availability groups and their state.

Dynamic management view Description
sys.dm_hadr_availability_group_states Returns a row for each availability group that owns an availability replica on the local instance. Shows synchronization health and role state.
sys.dm_hadr_ag_threads Returns a row for each thread associated with an availability group. Useful for diagnosing thread-level issues.
sys.dm_hadr_name_id_map Maps availability group names to their unique identifiers (GUIDs).

Availability replica monitoring

These DMVs provide state and health information for availability replicas.

Dynamic management view Description
sys.dm_hadr_availability_replica_states Returns a row for each replica showing synchronization state, role, and operational health.
sys.dm_hadr_availability_replica_cluster_nodes Returns a row for each availability replica mapped to its cluster node name.
sys.dm_hadr_availability_replica_cluster_states Returns cluster state information for each availability replica regardless of where it's hosted.

Database replica monitoring

These DMVs track synchronization and state for databases within availability groups.

Dynamic management view Description
sys.dm_hadr_database_replica_states Returns a row for each database participating in an availability group, showing synchronization state and log progress.
sys.dm_hadr_database_replica_cluster_states Returns cluster-level state information for each database in an availability group.
sys.dm_hadr_db_threads Returns thread information for databases participating in availability groups.

Cluster monitoring

These DMVs provide information about the Windows Server Failover Clustering (WSFC) cluster.

Dynamic management view Description
sys.dm_hadr_cluster Returns one row with the cluster name and quorum information.
sys.dm_hadr_cluster_members Returns a row for each cluster member and its state.
sys.dm_hadr_cluster_networks Returns a row for each cluster network and its subnet configuration.
sys.dm_hadr_instance_node_map Maps SQL Server instances to their hosting WSFC cluster nodes.

Automatic seeding

These DMVs track automatic seeding operations for availability group databases.

Dynamic management view Description
sys.dm_hadr_automatic_seeding Returns status information for automatic seeding operations on the primary replica.
sys.dm_hadr_physical_seeding_stats Returns detailed statistics about physical seeding operations including progress and throughput.

Automatic page repair

These DMVs track automatic page repair operations that fix corrupt pages using data from replicas.

Dynamic management view Description
sys.dm_hadr_auto_page_repair Returns a row for each automatic page repair attempt on any availability database.

Listener monitoring

These DMVs provide information about availability group listeners.

Dynamic management view Description
sys.dm_tcp_listener_states Returns state information for each TCP listener including availability group listeners.

Related content