-1

Environment

  • SQL 2016 Standard Edition
  • Mem: 128GB
  • Max DOP = 0 (default)
  • Cost Threshold for Parallelism = 5

I'm trying to diagnose a performance problem using wait stats. I have a procedure which runs fast most of the time, but on occasion it times out. When it times out, it appears the query just takes too long to execute. The actual execution plan remains the same but the execution at different intervals are way too slow.

When the query runs slow and I check using sp_WhoIsActive I see ACCESS_METHODS_DATASET_PARENT (000001973F30B478).

When I run waiting tasks DMV I see the following.waitingtasks

I'm not sure whats causing this at random intervals. When the query runs slow and I get all these waits, I've noticed it eventually runs it and drops the plan from cache and adds the same plan back again. No differences, as it's exactly the same plan.

Does anyone have any suggestions as to how I go about troubleshooting this further?

Top 3 Waits using sqlskills waitstats script.

enter image description here

Thanks

3
  • 2
    Do you see any blocking when it's running slow? Can you please add the actual execution plan for both when it's slow and fast? Does the procedure take in any parameters?
    – J.D.
    May 13 at 11:46
  • Hi, Yes the procedure takes in 2 params. The execution plan for both when it runs slow and fast are exactly identical. I even tried to put statement level option recompile and optimize for and test but it doesn't make a lot of difference. In regards to blocking nope no blocking at all as other queries come in and get executed in and out. I suspected due to high cspacket and latch_ex that its paralellisim most likely but having problems trying to nail it down to root cause. Odd thing is that its fast most of time but other times slow and see a build up of the latches as shown above. Thanks
    – KAl
    May 13 at 23:09
  • Kind of hard to say the execution plans are identical unless you did a byte, string, or XML level comparison on both plans. But I don't necessarily doubt it either. Regardless having the plan to analyze makes discussing, troubleshooting, and advising on what your issues are easier. Barring that, if the plans aren't identical, then parameter sniffing is my first thought. Alternatively, maybe you're getting an uneven distribution of the work in a plan that's over-parallelized, and using a query hint to set MAXDOP to a lower amount may show improvement there. Hard to say without seeing the plan
    – J.D.
    May 14 at 3:28

0

Your Answer

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

Browse other questions tagged or ask your own question.