Estou executando abaixo do tsql para encontrar os servidores AlwaysON secundários da réplica primária:
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
A consulta acima leva 12 minutos e 38 segundos para ser executada e retorna 34 linhas . Abaixo estão as estatísticas IO e saída de tempo:
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.
Aqui está o link para o plano de execução real .
Qualquer coisa que pode ser feito para torná-lo mais rápido?
Abaixo é do Plan Explorer
Respondendo a minha própria pergunta que pode ser útil para futuros visitantes.
O comentário de Aaron me fez pensar que, se descarregarmos os dados na tabela temporária, isso aceleraria?
Sim, ele realmente acelerou devido ao grande número de grupos de disponibilidade 53 (eu sei que é muito e temos planos de descarregá-los em breve),
sys.availability_groups
esys.availability_replicas
as seleções estavam demorando consideravelmente.Abaixo o tsql roda em 1 min e 03 seg :-)