I’m Begging You, Here
Ever since SSMS started collecting wait stats in query plans, I’ve been mad about a couple things that get filtered out:
- Lock waits
- CXCONSUMER waits
Lock waits are particularly annoying. Imagine (I know, this might be difficult) that you have a friend who is puzzled by why a query is sometimes slow.
They send you an actual plan for when it’s fast, and an actual plan for when it’s slow. You compare them in every which way, and everything except duration is identical.
It’d be a whole lot easier to answer them if LCK waits were collected, but hey. Let’s just make them jump through another hoop to figure out what’s going on.
CXCONSUMER has a similar problem — and here’s the thing — if people are going through the trouble of collecting this information, give’em what they ask for. Don’t just give them what you think is a good idea.
Highly Affected People
Let’s look at a query where parallelism all gets skewed to one thread.
SELECT u.Id, u.DisplayName, u.Reputation, u.CreationDate, ca.* FROM dbo.Users AS u OUTER APPLY ( SELECT *, DENSE_RANK() OVER( PARTITION BY vs.Id ORDER BY vs.Id DESC ) AS whatever FROM dbo.VotesSkewed AS vs WHERE vs.UserId = u.Id AND vs.VoteTypeId BETWEEN 1 AND 4 ) AS ca WHERE ca.whatever = 0;
It runs for 42 seconds!
But the only wait collected is SOS_SCHEDULER_YIELD. For 392 ms.
If we watch the server’s wait stats while the query runs, we see a totally different story.
EXEC sp_BlitzFirst @Seconds = 50, @ExpertMode = 1;
We had four waits on CXCONSUMER that all lasted nearly 11 seconds. Which of course happens because all the rows end up on one thread.
Sure, that’s easy enough to spot if you go looking, but having CXCONSUMER waits collected would make it a whole lot easier to know what to look for.
That is surely a significant enough wait to include. And don’t tell me it’s not actionable, because I spend a good chunk of time fixing problems like this.
Thanks for reading!