What is causing waittype CXSYNC_PORT and what to do about it?

Veelen van, Klaas 11

Hi,

I'm running a query against Azure SQLDb which 'stands still' for 2 hours now, apparently not doing anything. No CPU-usage (from anyone), no DataIO, no LogIO. sys.dm_exec_requests tells me my query is suspended with wait type CXSYNC_PORT. What can be the reason and what to do about it? Never seen this before, no hits on google (except for anyone else asking the same questions some 12 hrs ago).

Regards,
Klaas

Anurag Sharma 17,586 Reputation points

2020-08-12T11:39:28.353+00:00

Hi @VeelenvanKlaas-2013, sorry for the trouble you are having. As rightly pointed out by you that there is no documentation available currently to understand the nature of the issue.

We have reached out to product team on the same issue and asked for their inputs. We will keep you updated as soon as possible.
Steve Rajkovich 16 Reputation points

2020-08-12T12:34:34.463+00:00

Same here. Showing CXSYNC_PORT waits on SQL Managed Instance. I find it troubling that no documentation has been created before these Azure changes. Any information that can be shared here by the product team would be extremely helpful.
Veelen van, Klaas 11 Reputation points

2020-08-12T19:06:46.54+00:00

Just for your information: just a few minutes ago my query 'unfreezed' and my life query statistics show its processing the next step. Someone at MS who pulled the right switch?
Mike Ubezzi 2,776 Reputation points

2020-08-12T19:22:25.023+00:00

Hi @VeelenvanKlaas-2013 - Than you for providing an update on your issue. I am checking internally to check root cause but it would helpful to understand which region your service(s) are deployed to? Thank you for the additional detail. Regards, Mike
Veelen van, Klaas 11 Reputation points

2020-08-12T19:54:00.873+00:00

We're running in Western-Europe.
Veelen van, Klaas 11 Reputation points

2020-08-12T20:14:07.537+00:00

Business Critical, Gen5, 12vCores, no elastic pool, allocated 3TB, max 3TB, used 2,38Tb.
Veelen van, Klaas 11 Reputation points

2020-08-13T11:32:39.223+00:00

Yeah, you were the one aksing the same question some 12 hrs earlier than I did. Any findings or progress on your side?
Right now, I'm seeing the same waittype although the query is active, hard-working. Live query stats are showing progress. Most hahs-matches on large sets.
Steve Rajkovich 16 Reputation points

2020-08-13T12:30:22.737+00:00

As an interested party with similar issue, we are running in North Central US. I have seen some mitigation of this CXSYNC_PORT, but it's still present. I am not sure if KlassvanVeelen's issue and mine are exactly the same, but some explanation of this wait may help me look for other information.
Steve Rajkovich 16 Reputation points

2020-08-13T13:20:40.997+00:00

Not too much. Like you, I found a procedure running fast in milliseconds but our application kept getting timeouts. But I also found some poor running queries and these queries could be getting exposed with something changed or amiss with Azure SQL MI. Usually along with the CXSYNC_PORT I find the CXCONSUMER or CXSYNC_CONSUMER waits. The CXCONSUMER in itself I am not overly concerned but likely a symptom of other waits or inefficiencies. I figure bad parallelism is involved and yes large sets. For example, I refactored a couple queries that the application was submitting via an ORM into a function and that eliminated a couple queries from this issue as I keep looking clean up old queries. I also noticed this issue seemed to start Monday Aug 10th and appeared be mitigated on Wednesday Aug 12th. I know we have some queries that can be improved, but things still ran "ok". This CXSYNC_PORT is either something new or bubbling up with some infrastructure change?
Veelen van, Klaas 11 Reputation points

2020-08-13T15:01:27.347+00:00

Same feeling here. We're still in de developing process, but I've been working on this same query couple weeks ago and all was fine. This week I picked it up again and got in trouble.
Also, it was worse than it is now earlier this week. That's the good news ;-) Still not happy though.
Veelen van, Klaas 11 Reputation points

2020-08-17T07:44:29.013+00:00

Hi Mike, Any news yet? Should I better raise a support call?
Navtej Singh Saini 4,221 Reputation points Microsoft Employee

2020-08-17T19:45:38.547+00:00

@Veelen van, Klaas

We apologize for the delay regarding this. We are working with PG to get more information about this and have followed up as well.

If you have a support plan, please do raise an support issue and share the ticket number.

Thanks
Navtej S

1 answer

Navtej Singh Saini 4,221 Reputation points Microsoft Employee

2020-08-17T20:51:15.923+00:00

@Veelen van, Klaas /All

As part of a fix CXPACKET waits were further broken down into CXSYNC_CONSUMER and CXSYNC_PORT (and data transfer waits still reported as CXPACKET) as to distinguish between different wait times for correct diagnose of the problem.

Basically, CXPACKET is divided into 3: CXPACKET, CXSYNC_PORT, CXSYNC_CONSUMER. CXPACKET is used for data transfer sync, while CXSYNC_* are used for other synchronizations.
CXSYNC_PORT is used for synchronizing opening/closing of exchange port between consuming thread and producing thread. Long wait here may indicate server load and lack of available threads. Plans containing sort may contribute this wait type because complete sorting may occur before port is synchronized.

Hope this helps. Please get back to us if any other question.

Thanks
Navtej S
Please sign in to rate this answer.

2 people found this answer helpful.
Veelen van, Klaas 11 Reputation points

2020-08-18T10:02:31.167+00:00

Thanks for your clarification. It gives some insight, although I still experience worse performance than before which can't be explained by just a more granular waittype codesystem. Any clue of the underlying problem you were trying to investigate? Could that problem be my issue as well?

Steve Rajkovich 16 Reputation points

2020-08-18T12:46:15.51+00:00

This make sense as to what CXSCYNC_PORT is about as I figured this relates to some parallelism within the queries execution plans. This helps. However, what it does not explain is WHY did this wait suddenly start occurring when previously the same queries were executing without this issue before. I don't discount data or statistic changes, but the issue crossed multiple queries and multiple data tables and then two days later, the issue mitigated itself (mostly). If SQL MI has had some release changes regarding the undocumented wait or parallel processing, it would nice to know the potential impact.

Charlotte Watson 21 Reputation points

2020-08-18T16:34:16.857+00:00

I am seeing performance issues whenever the CSXYNC_PORT wait type exists. At the same time we received this alert:
We're sorry your SQL database is unavailable at this time. Currently, Azure shows the downtime for your SQL database resource at a one-minute granularity. The actual downtime might be less than one minute. An unexpected problem is preventing us from restoring access to your SQL database or SQL data warehouse.

What can we do to mitigate this wait type?

Charlotte Watson 21 Reputation points

2020-08-18T16:35:27.897+00:00

I am seeing performance issues whenever the CSXYNC_PORT wait type exists. At the same time we received this alert:
We're sorry your SQL database is unavailable at this time. Currently, Azure shows the downtime for your SQL database resource at a one-minute granularity. The actual downtime might be less than one minute. An unexpected problem is preventing us from restoring access to your SQL database or SQL data warehouse.

What can we do to mitigate this wait type?

Steve Rajkovich 16 Reputation points

2020-08-18T20:25:00.273+00:00

Yeah, I think there is more to this than just a diagnostic breakdown. "As part of a fix" sounds like some engine changes. We didn't get connection issues, but query timeouts. Our instance also restarted Aug 14th approx 5 days after seeing issues. I accept it's Cloud and Azure, but I am hoping for more transparent information so we can try to adjust things where possible.

Steve Rajkovich 16 Reputation points

2020-08-18T20:26:58.157+00:00

I am not sure what environment you are on, either Azure SQL, Azure SQL MI or a VM hosting SQL Server. Mine is SQL MI. What queries once worked ok appears to be a little or a lot worse now. Besides something in Azure being mitigated on its own, a few things helped in my case due to older SQL databases/code, basically more tuning:

1) Refactor your biggest, poor performing queries to reduce parallelism, sorts, amount of data your pulling.
2) Recreate poor performing ad hoc queries such as from ORMs or other sources as procedures or functions. Leverage better SQL per #1 above.
3) Find where you need new or different indexes
4) Change or up-tick your Cost Threshold for Parallelism say to 25.

Not sure about Azure SQL impact.
Sign in to comment

Share via

What is causing waittype CXSYNC_PORT and what to do about it?

1 answer