New DMVs related to Availability Groups in SQL Server 2019 CU9

Question

In SQL Server 2019 since Cumulative Update 9, there are two new DMVs related to Availability Groups:

• sys.dm_hadr_cached_replica_states
• sys.dm_hadr_cached_database_replica_states

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.

Answer 1

Does anyone know what the purpose of the new DMVs is?

It's really around as cached data so that the DMV can be hit often without incurring certain performance hits (think orchestration operations and polling). It wasn't really made for external use and should probably be hidden in the box product versions.