LATCH_EX and CX_PACKETS for a stored proc seen when timing out

Question

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

Answer 1

CXPACKET wait is most likely due to Index Scan operator(s) in the execution plan of the SP, reading a lot of rows from certain table(s). Index Scan may go parallel and some threads are waiting for other threads to complete, hence the CXPACKET wait type.

What may happen is that when SP completes (under 15 mins), execution plan is different and reading less rows or contains less Index Scans that read massive amount of rows. When SP times out and you see CXPACKET 8 hours, execution plan may contain more Index Scan operators that are reading massive amount of rows

More information I can share when you answer questions that I posted in comments

In any case, you've got to capture execution plans of the stored procedure, see what's the difference between execution plan when it succeeds vs when it times out.

Resolution would be to identify problem parts (statements) in SP which cause reading massive amount of rows with Index Scan(s) and rewrite them to avoid it

Alternatively you can increase the timeout setting, or get a better hardware