I have a SQL merge query that has been working fine in production without any issues for last 3+ months but suddenly, it is getting stuck.
I also checked with infrastructure team to confirm no changes were made to the existing DB server.
After some research, I ran this query below to get more details and it seems the merge got suspended with wait_type CXPACKET
or CXCONSUMER
.
<code>SELECT wt.session_id,
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id
WHERE es.is_user_process = 1
and wait_type != 'CXCONSUMER'
<code>SELECT wt.session_id,
ot.task_state,
wt.wait_type,
wt.wait_duration_ms,
wt.blocking_session_id,
wt.resource_description,
--es.[host_name],
es.[program_name]
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id
WHERE es.is_user_process = 1
and es.session_id = 84
and wait_type != 'CXCONSUMER'
</code>
SELECT wt.session_id,
ot.task_state,
wt.wait_type,
wt.wait_duration_ms,
wt.blocking_session_id,
wt.resource_description,
--es.[host_name],
es.[program_name]
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id
WHERE es.is_user_process = 1
and es.session_id = 84
and wait_type != 'CXCONSUMER'
<code>| session_id | task_state | wait_type | wait_duration_ms | blocking_session_id | resource_description | program_name |
|------------|------------|-----------|------------------|---------------------|----------------------|--------------|
| 84 | SUSPENDED | CXPACKET | 616 | 84 | exchangeEvent id=Pipe223d33ce580 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=5 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 4129 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=4 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 839 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=1 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 837 | 84 | exchangeEvent id=Pipe223d33ce180 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=2 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 4128 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=3 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
<code>| session_id | task_state | wait_type | wait_duration_ms | blocking_session_id | resource_description | program_name |
|------------|------------|-----------|------------------|---------------------|----------------------|--------------|
| 84 | SUSPENDED | CXPACKET | 616 | 84 | exchangeEvent id=Pipe223d33ce580 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=5 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 4129 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=4 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 839 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=1 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 837 | 84 | exchangeEvent id=Pipe223d33ce180 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=2 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 4128 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=3 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
</code>
| session_id | task_state | wait_type | wait_duration_ms | blocking_session_id | resource_description | program_name |
|------------|------------|-----------|------------------|---------------------|----------------------|--------------|
| 84 | SUSPENDED | CXPACKET | 616 | 84 | exchangeEvent id=Pipe223d33ce580 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=5 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 4129 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=4 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 839 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=1 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 11701531 | 84 | exchangeEvent id=Port20e1b04a200 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=5 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen | Python |
| 84 | SUSPENDED | CXPACKET | 837 | 84 | exchangeEvent id=Pipe223d33ce180 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=2 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
| 84 | SUSPENDED | CXPACKET | 4128 | 84 | exchangeEvent id=Pipe223d33ce280 WaitType=e_waitPipeNewRow waiterType=Producer nodeId=20 tid=3 ownerActivity=receivedData waiterActivity=tryToSendData merging=false spilling=false waitingToClose=false | Python |
Note that I filtered out CXCONSUMER wait types because I read that CXPACKET should be the main focus. I read up on the resource description and still unclear what it all means.
I’m trying to troubleshoot why it would suddenly start getting suspended and stuck for hours when before, the merge would only take 5-10 mins.
Can anyone please advise me on what the issue is and what steps I should take to fix?
I didn’t include my merge query as it’s typical insert, updates and deletes but let me know if I should include in post. Thank you.