1

We are experiencing intermittent SQL Server issues, where from time to time, it would become unresponsive for a few minutes, causing an outage of a system, driving crazy many users.

I started using sp_Blitz and related tools to find hints.

But I got the following results, which looked very bad, "slow data reads", and alarming wait stats of all sorts (160 seconds waiting ?!?!)

enter image description here

  • Could it be a hard drive issue ?
  • Based on the image, what should be my top 3 priorities ?
  • Could you give me hints that could help relieving the pain while I look into it more ?

I've been here which seem related: sp_BlitzFirst - Wait Stats Interpretation / sp_BlitzFirst - File Stats Interpretation

  • Just now I changed MAXDOP from 0 to 8 (we have 20 cores cpus)
  • Next: Will tap the Cost Thresh for Paralellism

BLITZ version date is 4/8/2022

Thanks so much 🙏

Improve this question
1
  • 1
    i recommend using perfmon to capture more information; i usually use 30 second increments. i'd capture stats for logical disks(reads/s, writes/s, s/read, s/write, read bytes/s, write bytes/s and current disk queue length), SQL Server wait stats and if SQL Server Enterprise Edition also resource pool stats and workload group stats. having high waits and high cpu strongly indicates overthreading in addition to potential underlying disk issues.
    – sqL_handLe
    May 22, 2022 at 17:12

1 Answer 1

Reset to default
1

You need to collect more data, but from my experience, leaving max degree of parallelism setting left at the default of 0 can and will cause the behavior you're seeing on systems with higher core counts.

One thing I will say is dig through the ERRORLOG when you see this behavior going forward. While the max degree of parallelism setting can cause this, it's not the only thing that can. I/O issues and Non-Yielding Schedulers issues are just a couple of things that can also cause this behavior, but they will be logged to the ERRORLOG when they occur, so always ALWAYS check there when you experience issues.

Finally, the other point of the answer is if you feel this is related to a drive issue, you can better identify that with proper perfmon tracing. David Klee has posted an article on how to configure a perfmon trace from start to finish so that if you run into this situation again, you can go to the report and actually see if I/O is an issue or not.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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