0

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

enter image description here

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.

|improve this question
  • Does the subset execution run parallel or serial? It may be that your query runs better serially, but when you run it against your complete record set it exceeds the cost threshold and therefore runs in parallel. You could try running it serially by setting OPTION (MAXDOP 1) – HandyD Jan 11 at 1:28
  • You are going in wrong direction. You should explore your execution plan,very probably you miss some indexes. Your query is executed in parallel and the workload is not distributed evenly between threads, it seems that your statistics is out of time, anyway there is wrong estimation of cardinality. Please update your question with execution plan, better with actual execution plan if it's possible – sepupic Jan 11 at 7:32
4

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

If you're looking for what these numbers mean then, check out the sp_whoisactive Documentation.

From Adam Machanic's the page on the CXPACKET node identifier, you can see that this line in your output:

(5x: 68/168850/635541ms)CXPACKET:15
(3x: 3/16/32ms)CXPACKET:7
(1x: 10207374ms)CXPACKET:1

Means that there are

  • 5 tasks waiting on CXPACKET in node 15 of the execution plan
  • 3 tasks waiting on CXPACKET in node 7 of the execution plan
  • 5 tasks waiting on CXPACKET in node 1 of the execution plan

The 3 numbers separated by slashes are documented here:

(Nx: MINms/AVGms/MAXms)[wait_type] where Nx is the number of tasks, MINms is the shortest wait duration of the tasks, AVGms is the average wait duration of the tasks, and MAXms is the longest wait duration of the tasks

The output of @get_task_info = 2 is telling you

  • where exactly in the execution plan your query is getting stuck
  • how many tasks are getting stuck there, and
  • how long they're stuck there (min, max, and on average)

Hopefully this provides enough information for you to be able to find which part of your query is getting stuck on these parallelism waits so you can investigate further.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.