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?
Best Answer
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