Surreal Plans
In a parallel plan with an eager index spool, we can observe EXECSYNC waits for the duration of the spool being built.
In a serial plan, we’re not so lucky. There’s no obvious wait that indicates we built an index.
Let’s run a familiar query:
SELECT TOP ( 10 )
u.DisplayName,
u.Reputation,
ca.*
FROM dbo.Users AS u
CROSS APPLY
(
SELECT TOP ( 1 )
p.Score
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
AND p.PostTypeId = 1
ORDER BY p.Score DESC
) AS ca
ORDER BY u.Reputation DESC;
The plan is fully serial:
It’s obvious looking at the actual plan in SSMS 18 what took a long time.
If we’re not so lucky, and we have a cached plan, it would be less obvious:
Look how bad scans are! Ha ha ha!
?
What’s A Wait?
If you wanna figure out the runtime, you have to do some math.
If you take the SOS_SCHEDULER_YIELD waits and multiply them by 4ms, you can get about accurate runtime (12,444).
We’re lucky on my laptop that our query isn’t competing with other queries for processor time, so there’s not a long queue to get back on a CPU on each yield.
It’s nice to be able to see this while we’re watching a query, but if we come across it in the plan cache, or if we were looking at wait stats, what would we make of the problem?
Surely an operator that’s only 13% of the plan cost couldn’t be responsible for all that.
??
But There We Have It
A query that runs for a non-trivial amount of time, emits common wait stats, and doesn’t ask for an index when it’s creating one.
And that index creation is what makes up for 99% of the execution time.
Thanks for reading!
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 performance problems quickly.