Hello , I would like to run a stored procedure on a secondary replica in an AlwaysOn Availability Group to offload read-only workload from the primary. However, this procedure currently suffers from parameter sniffing, and we sometimes need to recompile it to restore acceptable performance.
I would like to know:
What are the risks of running this stored procedure on the secondary replica, given that it may require recompilation?
Is it possible to recompile the procedure on the secondary replica, considering that it is read-only?
How can we detect performance regressions, especially since Query Store is read-only on secondary replicas in SQL Server 2022?