Somewhat Solved
This bit of confusion is largely solved in SQL Server 2019 under compatibility level 150, when FROID (scalar udf inlining) kicks in.
But, you know, we’re a ways off from 2019 dropping, being adopted, and compat level 150 being the operating norm.
So here goes!
Functional Querying
I’ve got a scalar valued function. What it does is unimportant, but I’m calling it in a query like this:
SELECT u.DisplayName,
dbo.TotalScore(u.Id) AS TotalScore --<functione
FROM dbo.Users AS u
WHERE u.Reputation >= 200000
ORDER BY u.Id;
When I run this in SSMS, it’s obvious to us what’s going on.
But if I’m watching what’s happening on a server using sp_WhoIsActive, what’s going on might not be obvious.
I’m doing all this with just my query running to show how confusing things can get.
First Confusion: Query Text
This doesn’t look at all like the text of our query. We can guess that it’s the function running in the select list since we know what we’re doing, but, you know…
We can bring some clarity by running sp_WhoIsActive like this:
sp_WhoIsActive @get_plans = 1,
@get_outer_command = 1;
The outer command parameter will show us the query calling the function, which’ll look more familiar.
Second Confusion: Phantom Parallelism
We’re hitting more of those harmless, silly little CXCONSUMER waits.
But how? Our query plan is serial!
This part is a little less obvious, but if we get an estimated plan for our query, or track down the query plan for the function, it becomes more obvious.
The query plan for the function is parallel — a cute ~nuance~ about scalar udfs is that they only prevent the query calling them from going parallel.
The function itself can go parallel. So that’s… nice.
I guess.
They Walked Inlined
In compat level 150, things are more clear.
The inner and outer text are the same. There’s more of that CXCONSUMER, though. Hoowee.
But at least now we have a query plan that matches the parallel waits, right?
In the next post, we’re gonna talk more about those wait stats, though.
Thanks for reading!