olahallengren
commented
3 weeks ago SELECT @CurrentAvailabilityGroup = [name], @CurrentAvailabilityGroupBackupPreference = UPPER(automated_backup_preference_desc) FROM sys.availability_groups WHERE group_id = @CurrentAvailabilityGroupID
Could you try executing the script in SSMS (with SET STATISTICS TIME ON) on the server with the issue?
We are currently using the latest version of the backup scripts are running several jobs in parallel to do log backups on hundreds of databases per server. RedGate tools are reporting the following query as high cpu consistently:
SELECT @CurrentAvailabilityGroup = [name], @CurrentAvailabilityGroupBackupPreference = UPPER(automated_backup_preference_desc) FROM sys.availability_groups WHERE group_id = @CurrentAvailabilityGroupID
This is the query behind the function sys.fn_hadr_backup_is_preferred_replica. Can this function call be moved out of the database loop so that it is only called once per job per backup execution reducing the cpu hit?
SQL Server version and edition SQL 2016sp3
Version of the script 2 Jan 2022
What command are you executing? Backup log
What output are you getting? Completes successfully but at high cpu cost.