CXSYNC_PORT wait type in Azure Sql Database

user_1941025 0
Viewed 2941

I'm facing this issue intermittently now, where the query (called from stored Procedure) goes for CXSYNC_PORT wait type and continues to remain in that for longer time (sometimes 8hours in stretch). I had to kill the process and then rerun the procedure. This procedure is called every 2-hours from ADF pipeline.

What's the reason for this behavior and how do I fix the issue?

azure-sql-server azure-sql-database
2 Answers

I searched a lot and there is not Microsoft documents talk about the wait type: CXSYNC_PORT. Others have asked the same question but still with no more details.

Most suggestions are that ask the same problem in more forums. Or ask professional engineer for help, and they will deal with your problem separately and confidentially.

Ask Azure support for details help: https://docs.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request

And here's the same question which Microsoft engineer gave more details about the issue:

  • 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 waits 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.

Please ref this link What is causing wait type CXSYNC_PORT and what to do about it? to get more useful messages. But for now, there isn't an exact solution.

user_105492810

use query hint OPTION(MAXDOP 1) This will run your long running query in a single thread and you won't get the CX type waits. In my experience this can make a massive 10-20X decrease in execution time and will free up CPU for other tasks as there will be no context switching and thread coordination activity.

user_172861770
Related