Menu
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?
499
CXPACKET stands for Class eXchange Packet. This wait type occurs when the query or workload is running in parallel, with more than one thread being used by SQL Server database engine to execute the query.
CXPACKET and CXCONSUMER are wait types that indicate that work isn't equally balanced. When you see these wait stats on your server, you'll know that SQL Server is running queries in parallel, but not doing a great job of distributing them across available processors.
There are two main categories for the SQL Server Wait Statistics; the Single Wait type, where the query is waiting for CPU resource availability and the Resource Wait type, where the query is waiting for a specific resource availability, such as I/O or Memory resources.
The Real Way to Reduce CXPACKET Waits Set MAXDOP per Microsoft's recommendations in KB 2806535. Set Cost Threshold for Parallelism in a way that lets your small queries stay single-threaded, but still gets more students involved when there's a really big project.
Exchange Iterator synchronization is tracked separately in the CXSYNC_PORT and CXSYNC_CONSUMER wait types. Threads consuming rows are tracked separately in the CXCONSUMER wait type. Occurs with parallel query plans when waiting to open, close, and synchronize Exchange Iterator ports between producer and consumer threads.
For example, if a query plan has a long sort operation, CXSYNC_PORT waits may be higher because the sort must complete before the Exchange Iterator port can be synchronized. Occurs with parallel query plans when waiting to reach an Exchange Iterator synchronization point among all consumer threads.
June 8, 2016. The SQL Server CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET term came from Class Exchange Packet, and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process.
Note: In Azure SQL Database and Azure SQL Managed Instance, CXPACKET only refers to waiting on threads producing rows. Exchange Iterator synchronization is tracked separately in the CXSYNC_PORT and CXSYNC_CONSUMER wait types.
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:
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.
107
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
