CXSYNC wait type appearing on Queries that didnt normally have it and TimeOuts occurr

Question

In Azure SQL Managed Instnace: Queries that are complex but run in a timely way normally ... sometimes start getting this wait: CXSYNC_PORT and run until our App timeout threshold of 30sec. This has happened for various databases on the same Managed Instance. The workaround is to hint the query with a Query Store hint of MAXDOP 1 - to avoid parallelism (and I presume the extra threads that it needs to do the sorts and what-not that use parallelism). So are the Managed Instances getting starved for threads? Most of the time with the same workload, we are not seeing this problem...but then it arises. We dare not remove the hints to those queries as the problem might pop up again in our Production Apps. Please help.

Answer 1

Hi @John Heimiller,
Welcome to the Microsoft Q&A and thank you for posting your questions here.

The issue you're facing where normally fast queries start getting delayed and hit a CXSYNC_PORT wait likely means that something is blocking or slowing down the parallel processing of those queries at times.

Here are some troubleshooting steps that might help.

Check System Resources:

• Use Azure Metrics to monitor CPU, memory, and I/O usage. High usage could be causing delays.
• Check active queries using sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_query_stats to identify resource-heavy queries.
• Check thread usage with sys.dm_os_threads to see if the system is running out of threads during peak times.

Optimize Problematic Queries:

• Look for expensive operations (e.g., large joins, sorts, aggregations).
• Simplify the queries, improve indexes, or partition large tables to improve performance.

Adjust Parallelism:

• Instead of using MAXDOP 1 (which disables parallelism), set MAXDOP to a value like 2, 4, or 8 based on your server’s capacity.
• Ensure the Cost Threshold for Parallelism is set high enough to avoid unnecessary parallelism for simple queries.

Scale Up the Instance:

• If resource limits are the cause, scale up by increasing vCores or adding more memory.
• If the problem persists, consider upgrading to a higher-performance tier for more resources.

For more information, please refer to these links:

https://learn.microsoft.com/en-us/azure/azure-sql/database/configure-max-degree-of-parallelism?view=azuresql

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.