February 14, 2024 at 1:29 pm
Hi
I have a job that calls a stored proc. Every since we updated to SQL2022 (running in 140 compatibility mode) I've noticed that this and other similar procs produce a CX_PORT wait. They do this every time they run and if I check who is active there are no deltas records being moved at may points in the query - they always finish tho. So I added the OPTION(MAXDOP 1) hint in many places in the statement and after I do that there are no more CX waits and the deltas move correctly - problem is now that the statement takes 10 times longer to complete. Which surprised me I thought once I got the waits to go away the statement would finish much faster.
I have my max dop setting at the server at 8 (and 8 for secondary H/A). When I set the max DOP at the server level I followed the MS guidelines but wondering if I need to go from 8 to 4 as a test?
Cost threshold for Parallism is set at 30
What is the most likely culprit here? I have a handful of procs all producing the CX waits since we updated to SQL2022. I also checked the available threads and I have over 785 available - so I'm good there (I think).
February 14, 2024 at 3:52 pm
If your SQL server has more than one processor and your application is not purely OLTP, CX waits are good.
CXSYNC_PORT is a new wait type in 2022, it was happening before in previous versions of SQL, it just wasn't recorded.
Is there an actual performance problem you are experiencing or are you just trying to solve the waits?
March 22, 2024 at 4:36 pm
CXSYNC_PORT - WAIT - will this cause the CPU usage on the server to go high? I see number of the same SPIDs running simultaneously and CPU touching 100% on the server.
No performance issues are experienced by the user, the application runs fast and no slows, only seeing intermittently high CPU alerts.
Any thoughts? thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply