4

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?

enter image description here

  • Look how CPU threads are used. If you have a lot of simultaneous queries you might just limit MAXDOP. I'd concentrate on latches first. Look at the query plan. Also "Async_Network_IO" might be caused by huge output. If so, is it really necessary? – Slava Murygin Jan 24 at 1:39
  • Your question is quite broad for two reasons. 1.) We don't have your script so we don't know what you are querying. 2.) Paul's script queries the wait stats since the last purge of cached query plans/stats, so the numbers will include index rebuilds, stats updates and lots more. The results are quite meaningless. You might want to consider using Paul Randal's other script form this blog post Capturing IO latencies for a period of time which will be more meaningful. Upload the info and we will give it a go. – hot2use Jan 24 at 6:55
  • 1
    Your problem of course is NOT parallelism, but IO. PageIOLatch_SH is your real problem, but I cannot say if it's poor indexing or not because you did not even say if it's OLTP server or DWH. For DWH large scans are necessary, and you can help your server by adding more RAM. If it's OLTP you should catch top IO queries with the plans and examine them. Can you also update your question with PLE(page life expectancy) please – sepupic Jan 24 at 7:05
3

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.