Hey Query, What Were You Waiting On?

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 )
FROM        dbo.Users AS u
    SELECT   TOP ( 1 )
    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:

Meat Lovers

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:

Back Like That

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.

What times what?

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!


Leave a Reply

Your email address will not be published. Required fields are marked *