SELECT query is slow in AlwaysOn primary node than secondary node

Copper Contributor

Hi Experts, Could you please help me !!!

 

We have one database hosted on SQL Server 2019 AlwaysOn with database compatibility level 100 (SQL 2008 ). We are running one SQL View Select statement on primary node and it is taking around 9-10 seconds where as in secondary node taking 3-4 seconds. There is no much users or load on the primary node database. 

For testing I have removed database from Alwayson and rerun the same query and it is taking  4 seconds on the primary node then I have added again database into Alwayson then the query time in primary node is again 9-10 seconds. 

 

Could you please guide me how to improve the query performance on the primary node when database in Alwayson.

 

Thanks

Sreenivasa

 

 

2 Replies
Hello,
Is the AG configured as synchronous or asynchronous ? I'll guess synchronous. if that is the case you can try to switch it to verify

Also clean all the caches and perform an update statistics on the involved tables

Regards
Javier
Hi Javier, thanks for the reply. yes i have tried switching and cleaning the caches but the response time is higher in primary. if i remove alwayson then the response is same for both primary and secondary node. Is there any performance degradation in sql server 2019 AlwaysON if we place the database into AG having database compatibility level 110.

Regards,
Sreeni