AlwaysON Availability group query very slow

Question

I am running below tsql to find the secondary AlwaysON servers from the primary replica :

set transaction isolation level read uncommitted 
set statistics time, io on
SELECT  
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off

The above query takes 12 mins and 38 secs to run and returns 34 rows. Below is the statistics IO and time output:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 10 ms, elapsed time = 10 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(34 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysguidrefs'. Scan count 0, logical reads 212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1266 ms,  elapsed time = 747852 ms.

Here is the link to actual execution plan.

Anything that can be done to make it faster ?

Below is from Plan Explorer

Click to enlarge

Click to enlarge

Thanks !

Answer 1

Answering my own question that might be helpful to future visitors ..

Aaron's comment made me thinking if we dump the data into temp table would it speed up ?

Yes it indeed speed up as due to large number of Availablity groups 53 (I know thats a lot and we have plans to offload them soon), sys.availability_groups and sys.availability_replicas selects were taking considerably long time.

Below tsql runs in 1 min and 03 secs :-)

set transaction isolation level read uncommitted 
set statistics time, io on

--- get data into temp tables 

select * into #availability_groups from sys.availability_groups
select * into #availability_replicas from sys.availability_replicas

SELECT  
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM #availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN #availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off