CXPACKET wait on SQL Server

Question

Recently I am experiencing performance issue on a SQL Server database and I have two queries that show me the wait as shown below, one of the databases on this server is a member of Always On replica, but the load is on the other database.

The first query is my own query which collects the waits stat during 10 seconds and calculates the difference and the second list if from Pauls's query: https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Regarding the differences, I'm wondering should I have to ignore CXPACKET and Redo_Thred_Pending_Work wait and work on the root cause of PageIOLatch_SH wait (maybe poor indexing). MAXDOP value is 0 and the server has 36 cores, any thoughts?

Answer 1

It's very hard to comment unless we know the nature of performance issue you are having which could be because of various factors.

Also waits you have captured are of 2 different means. One collected for the waits witnessed during the time you calculated for specific queries run. While other from Paul Randal is the aggregated one since last restart pointing CXpackets which does not necessarily indicate issue with it. That it collected wait stats for all the process that would have run since last restart. So it depends if you are looking to troubleshoot overall server performance or just those queries whose waits were captured.

Also depending upon the version of your sql server there is better way of interpreting CXpackets waits. Post sql 2016 SP2 you now have cxconsumer as well and hence can distinguish between good and bad CXpacket wait stats. Read here for more on this by Aaron Bertrand.

Also I see you have MAXDOP 0 which should be tested with best magical number depending upon logical processors to be tweaked. Per my experience and support from MS the best magical number that works for most of our numbers is between 4 to 8 where logical processors is greater than 8. You can also see dbatools to look for a number recommended in your environment.

In addition to MAXDOP don't forget to check on cost threshold of parallelism for the server. 5 the default does not work well.

However you should also look other factors like indexing strategies finding opportunity to tune them thus helping in gaining performance

Answer 2

I'm basically echoing @KASQLDBA's answer, but trying to emphatically convince you to make adjustments to max degree of parallelism and cost threshold of parallelism as your first step.

MAXDOP value is 0 and the server has 36 cores, any thoughts?

Do NOT leave this at 0. Adjust this to a more reasonable level. You can follow Microsoft's recommendations and set it to 8 or the the max number of logical processors per NUMA, whichever is less. Alternatively, as @KASQLDBA stated in the answer above, try out dbatools.io Test-DbaMaxDop command to get a recommendation without having to do all the leg work.

The issue with leaving this setting at 0 is that any parallel plan will use all of the cores on the server. Left at the default, enough parallel plans can actually starve your server of CPU resources and cause overall system slowness. I've seen this be so bad that the entire server will briefly freeze to end users and cause all manner of commotion. The CXPacket waits you're seeing are likely indicating this setting is misconfigured.

As stated at the beginning of this answer, also be sure to adjust cost threshold of parallelism. Erik Darling has a great article outlining what this setting is, how it works, and what that number means, and recommends you start by setting it to 50 and adjusting from there.