question

AndrewAn-4245 avatar image
0 Votes"
AndrewAn-4245 asked AndrewAn-4245 edited

Alwayson fn_hadr_backup_is_preferred_replica takes a long time to return results

Our 2 nodes full sync mode Alwayson cluster has 20 databases which allocated into 4 different AG groups (9, 6, 3, 2). We set backup preference as on 'preferred secondary'. Find out that fn_hadr_backup_is_preferred_replica function takes 10 - 14 seconds to return results for each databases which causing backup to take a long time with HADR_CLUSAPI_CALL wait. It seems no matter which one of 4 backup methods for Alwayson, you needs to relied on fn_hadr_backup_is_preferred_replica to return its backup preference. How to fix this problem since the fn_hadr_backup_is_preferred_replica is a system function which hard to modify.
we use SQL 2016 Enterprise, SP2 on latest CU.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndrewAn-4245 avatar image
0 Votes"
AndrewAn-4245 answered AndrewAn-4245 edited

Hi Thanks for replying. I had followed this instructions from this Ms article. After running the Cluster Validation, Although I got all passes on cluster validation. However the problem is still persists

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi AndrewAn-4245,

Welcome to Microsoft Q&A.

Find out that fn_hadr_backup_is_preferred_replica function takes 10 - 14 seconds to return results for each databases which causing backup to take a long time with HADR_CLUSAPI_CALL wait.

Whenever Always On calls into the WFCS for some work, it sets HADR_CLUSAPI_CALL wait type.
You may need to investigate the WFCS in more depth. Run Cluster Validation (but do it when SQL Server is not online), check your network adapters, cables, DNS resolution, correctness of IP addresses and subnets, disk resources.
Please refer to this article which might help.

Best Regards,
Amelia


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.