- 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
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.