CXSYNC_PORT wait type in Azure Sql Database

Question

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?

Microspore

Answer 1

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://learn.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.

Teenyweeny

Answer 2

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.

Tavis

Answer 3

If you will reduce CX waits by using single threaded query (MAXDOP1) you may also increase response time of you're query that then may lock others queries for a longer time. You can try the option(maxdop 1) at query level, it is no expensive to test but you can also :

• increase the cost threshold for parallelism (instance level) from 5 to 25 or even 50
• reduce the instance or database MAXDOP from 0 to 1/4 of your CPU
• try to rewrite the query
• try to see if a new index cannot help
• update statistics on tables concerned by the query

Chimb