Forum Discussion

tschary's avatar
tschary
Copper Contributor
Aug 26, 2024

SELECT query is slow in AlwaysOn primary node than secondary node

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

Sort By
  • Javier_Villegas's avatar
    Javier_Villegas
    MVP
    Aug 26, 2024
    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
    • tschary's avatar
      tschary
      Copper Contributor
      Aug 27, 2024
      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

Resources