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
From How to Use sp_WhoIsActive to Find Slow SQL Server Queries
@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.
The 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?
UPDATE
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.