Profiling Query Performance In SQL Server With Extended Events The Easy Way
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I always forget that these scripts are out there. We hand-built an EE session the other day but this would have been much easier to get the same information.
Also appreciated the “CTE’s are not your friend” line. 🙂
Nothing is quite as depressing as people forgetting about your scripts.
What if the performance goes mostly to blocking? What are the clues inside the keeper_HumanEvents_query session?
Correlation with blocked_process_report is useless for delays under 5s. Running “waits” event type at the same time could help.
The obvious clue is one that I’ve talked about quite a bit: if you compare CPU and duration, and duration is much higher than CPU, then it’s likely because of blocking. You can validate this by looking at wait stats at the query plan level to see what shows up in there.