I am on a server with major blocking issues. Not deadlocks, just plain blocks. We know that it is the server's primary bottleneck. However, they do not know what the block leaders are, so I do not know which queries to tune. I could install more monitoring, but I've noticed that they have Query Store enabled. Is there any information in Query Store that can be used to find block leaders?
1 Answer
Unfortunately not. I’ve often said that Query Store should capture deadlocks and blocking, but there’s nothing currently in there for it.
The best you can do (for 2017+) is find queries that were blocked by looking at their wait stats for associated lock waits.
Lead blockers of course don’t register lock waits, so it’s somewhat useless. The best you can still do is capture the blocked process report and hope that it’s accurate.
answered Nov 23 at 21:10
Erik Reasonable Rates DarlingErik Reasonable Rates Darling
44.1k14 gold badges140 silver badges514 bronze badges
-
It does, but won't tell you which queries were involved. sp_HumanEventsBlockViewer will break down the full blocked process report XML from an extended event set up to capture it. sp_HealthParser will look at the limited blocked process report information in the system health extended event. Commented Nov 23 at 21:35