I am trying to troubleshoot an issue with below things noticed:-

There is this stored proc which gets timed out on some random days while other days or after retry may complete faster. Time out setting 1 hour. When completes, it does under 15 mins.

What we have noticed below for this DB server-

• The database in question is a LS standby readonly database which keeps getting restored every 4 hours from Primary.

• Therefore the SP resides on secondary LS read only database and user query once its accessible.

• After restore is completed certain mornings we see this SP gets timed out with below as TOP 2 waits

  LATCH_EX and CXPACKETS    (wait time  
  showing accumulating upto 8 hours for that 1 hour run,    possibly   
  because MAXDOP is 8)
  

However when we retry the same proc after next cycle of restore of database, same SP just completes under 15 minutes.

When it Completes-

• We do see CXPACKETS but that wait time is very less

• We do not see any LATCH_EX waits

What possibly is going on here and how can i troubleshoot this further? We have already shared the code level tuning which can be done but DEVs are more interested why same SP gets completed in under 15 mins sometime and fails may be one random day in a week

Server level settings:

MAXDOP=8 CTOP=5

Edit- We found within SP the particular statement which tends to be the slowest amongst all and spending 90% of that 1 hour time is insert into # (temptable) after selecting data from multiple tables via job going parallel.

• When the issue is there above statement also waits on LATCH_EX in addition to CXPACKETS

• When running faster CXPACKETS are there but no LATCH_EX waits