The query below can take a long time to run on systems with thousands of databases like we have. More time is spent running this query than taking log backups.
The query is for returning data about the AG replicas such as the name, group, and role. We understand LiteSpeed runs this query to get details about what replica group that database belongs to and that database’s role in the group, used to populate the 'AlwaysOn Availability Group' option dropdown in the console UI, if the database belongs to an AG.
And we are aware that LS-1033 is fixed in LiteSpeed version 8.9. After this fix, that SELECT statement will run only for databases that are part of an AG group. And after this fix, this query statement will run once per database, not once per backup. That will help.
We run extended stored procedure 'exec master.dbo.xp_backup_database' manually from the SSMS, not the LiteSpeed console. We don't need to have that query run because we don't need to populate options in the LiteSpeed console such as AG dropdown. Even though we are using AG databases, we don't need any of this AG-centric data returned by the query. And we still see that call to the query being made.
Is there any way to opt out of having this query run at all? Decouple that query from the 'exec master.dbo.xp_backup_database'?
Problematic query:
SELECT db.name as [ReplicaDBName], ag.name as [ReplicaGroup], cast(ars.[role] as int) as [ReplicaRole]
FROM master.sys.availability_groups ag
inner join master.sys.availability_replicas ar on ar.group_id = ag.group_id
inner join master.sys.dm_hadr_availability_replica_states ars on ars.replica_id = ar.replica_id
inner join sys.databases db on db.replica_id = ars.replica_id WHERE db.name = 'dbname'
