CXPACKET wait time hours

Question

CXPACKET wait time hours

SQL Maarten

Valued Member

Points: 59
More actions
March 5, 2018 at 5:51 am

#333838

Hello,
As I understood well CXPACKET wait type below 50% of the instance is considered as not a problem, but an indicator that parallism is happening. In our case I see it arround the 20% of the total wait time. But the thing I don't understand is the wait time in Activity monitor. In the screenshot below you can see the processes belonging to SesID 62 waiting for hours and are suspended.

So they are waiting for one thing to go on. So with the following command:
select * from sys.dm_exec_requests where session_id = '62'
It returned me a wait_type of the type ASYNC_NETWORK_IO. So I guess it's waiting for an external process from another system to do something. But what is it waiting for....? Is it waiting for a commit from the application side? I'm almost sure I need to update the application developer about this. But I want to be sure what's happening here.
Thanks in advance,
Maarten

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Async network io is usually waiting for the app to accept the data.

If the app accepts a row, does something with the row, displays it or asks the user to acknowledge or the like, then repeats with the next row, you'll see high network IO. If the app accepts the entire results and then processes them, you generally don't.

There's probably something really weird about how that app handles query results.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Maarten Valued Member Points: 59 More actions · Answer 2

Thanks for the quick response Gail. So the SQL server fetches the whole result and puts it into a buffer. In the meanwhile the timer goes on for the CXPACKET wait type. That timer stops when the application site signals back to sql?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

No, it's a lot more complex than that.

A couple of the threads are waiting on async network io. They have the async network io wait and are counting time towards that wait because they're waiting for the app to accept results
Other threads within the query are waiting on those threads. They have the cxpacket waits and are counting time towards the cxpacket wait. Probably because there isn't space in the output buffers for their results, not sure, would need way deeper investigation to say for sure.

Doesn't really matter what's happening internally, the rule is that when you have cxpacket wait on some threads in a sessions and another wait on one or more other threads in the same session, it's the second wait that you need to fix

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass