0

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

enter image description here

Click to enlarge

enter image description here Thanks !

  • Seems like a long time, and I don't think it's 12 minutes because of the plan (and not sure what you could do to improve that either - it's not like you can change the catalog views you're calling). Is your AG over multiple subnets or cross-domain? Did you check for blocking? What is the wait type? Is your network saturated? – Aaron Bertrand Mar 8 '16 at 20:20
  • @AaronBertrand AG is over multiple subnets but in the same domain - NY-LD-HK. I did check for blocking and there is no blocking - 100% sure. The wait type is HADR_CLUSAPI_CALL. I am not sure how to get info about Is your network saturated?. The thing is that refreshing the Availablity groups node takes quiet a long time as well. There are 53 Availablity Groups ! – Kin Shah Mar 8 '16 at 20:41
  • This sounds like "doctor it hurts when I do this" Doctor: "so don't do this." Honestly, I don't know that you can do anything about this except (a) run the query less often or (b) partition the query and apply filters wherever possible (not sure if all those things support predicate pushdown, but trying not to resolve all 53 groups in a single query might be a good start). You could also use a monitoring tool, you know, so that information is always readily available to you by refreshing in the background instead of while you sit there and wait for 12 minutes. :-) – Aaron Bertrand Mar 8 '16 at 20:59
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

enter image description here

  • This is kind of where I was going with my second comment above, but I wasn't positive a temp table would help (partly because of the workfile/worktable work). I still think a minute is too long but that's just me. +1 – Aaron Bertrand Mar 8 '16 at 23:35
  • Agree but until we offload some replica, I don't want to spend time optimizing it. The sheer number of replicas is slowing down ssms as well. I still have to find out about max worker threads to see if we are not running at capacity. Any scripts or pointers that you can recommend on how to monitor that please ? – Kin Shah Mar 8 '16 at 23:52
  • I can expand this to an answer if you want. The basis of the slowness is that these DMVs are calling in real-time cluster apis and getting information about the AGs. Because information about the AGs are stored as properties, enumerating many of these runs into a scalability issue. That's the basics of WHY it's taking so long. The short answer is stop polling the information so often, it's not needed. Instead, rely on extended events and other internal mechanisms that only fire when an event happens. – Sean says Remove Sara Chipps Mar 9 '16 at 2:38
  • @SeanGallardy Thanks for the info. I am not polling that frequently. The job runs twice a day, but since it is taking considerably long time, I wanted to find out the reason. BTW, the SQL is part of a monitoring script, so would be difficult to have XEvents integrated to it. – Kin Shah Mar 9 '16 at 15:52
  • 1
    @Kin Yep, unfortunately with the higher number of AGs and replicas (the more private properties we need to retrieve through cluster apis) the longer it takes. My guess is if we looked at just the wait_info for this query a good portion if not all would be HADR_CLUSAPI_CALL – Sean says Remove Sara Chipps Mar 9 '16 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.