In SQL Server 2019 since Cumulative Update 9, there are two new DMVs related to Availability Groups:
I found out about them from What’s New & Undocumented in SQL Server 2019 Cumulative Update 9.
Apparently, the same information can be found from other existing DMVs, but these new DMVs may return some outdated (cached) information instead of the real-time data returned by the existing DMVs.
The information in
sys.dm_hadr_cached_database_replica_states is similar to the one returned by the following query:
SELECT ag.group_id AS ag_id, ag.name AS ag_name, ar.replica_id, ar.replica_server_name AS replica_name, adc.group_database_id AS ag_db_id, adc.database_name AS ag_db_name, rs.is_local, rs.is_primary_replica, rs.synchronization_state, rs.synchronization_state_desc FROM sys.dm_hadr_database_replica_states rs INNER JOIN sys.availability_groups ag ON ag.group_id = rs.group_id INNER JOIN sys.availability_replicas ar ON ar.replica_id = rs.replica_id INNER JOIN sys.availability_databases_cluster adc ON adc.group_database_id = rs.group_database_id
The information in
sys.dm_hadr_cached_replica_states is almost similar to the one returned by the following query:
SELECT ag.group_id AS ag_id, ag.name AS ag_name, ar.replica_id, ar.replica_server_name AS replica_name, ars.is_local, ar.availability_mode, ag.sequence_number, ars.role, ars.role_desc, ars.synchronization_health, ars.synchronization_health_desc FROM sys.availability_groups ag INNER JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id
However, in my case the new DMV always returns "PARTIALLY_HEALTHY" in the
synchronization_health_desc column for the secondary role instead of "HEALTHY" or "NOT_HEALTHY".
Does anyone know what the purpose of the new DMVs is? When is the information in the new DMV actually updated, considering the word "cached" is in their names?
I believe that the answers to these questions may be more clear after the documentation of SQL Server 2022 will be completed (hopefully, these new DMVs will be documented there). But maybe someone has more information about these DMVs and can shed more light about them even before that happens.