I have a huge query in SQL Server 2016 – when I run the query for a subset of records, it completes in 15 minutes. When I extrapolate it to total number of records, it is supposed to be complete in 1 hour. But it is not complete even after 3 hours. When I checked
master.sys.sysprocesses there is a CXPACKET wait with 10293331 waittime
SELECT waittime, waittype, lastwaittype, open_tran, cpu, physical_io, [sid], loginame, [sql_handle] FROM master.sys.sysprocesses WHERE SPID=58
Note: I did exec
sp_Who2 to get the specific SPID=58
@get_task_info = 2 – if a query has gone parallel and you’re troubleshooting CXPACKET waits, you can figure out what each task in the query is waiting on.
I executed sp_WhoIsActive with @get_task_info = 2.
wait_info gives the following:
(5x: 68/168850/635541ms)CXPACKET:15, (3x: 3/16/32ms)CXPACKET:7, (1x: 10207374ms)CXPACKET:1
How to determine what each task is waiting on from this information?
This question is more about finding a "way" to figure out the waiting resources (using sp_WhoIsActive or similar) than actually fixing this current performance issue by adding MAXDOP or analyzing execution plan.