FROID Rage
To test FROID, which is the codename for Microsoft’s initiative to inline those awful scalar valued function things that people have been griping about for like 20 years, I like to take functions I’ve seen used in real life and adapt them a bit to work in the Stack Overflow database.
The funny thing is that no matter how many times I see the same function doing the same thing in a different way, someone tells me it’s unrealistic.
Doesn’t matter what it does: Touch data. Not touch data. Do simple formatting. Create a CSV list. Parse a CSV list. Pad data. Remove characters. Proper case names.
“I would never use a function for that.”
Okay, Spanky ?
Too Two!
In CTP 2.2, I had a function that ended up with this query plan:
The important detail about it is that it runs for 11 seconds in nested loops hell.
For reader reference: The non-inlined version runs for about 6 seconds and gets an adaptive join plan.
The plan is forced serial with inlining turned off, naturally.
I sent the details over to my BESS FRENS at Microsoft, and it looks like it’s been fixed.
To Three!
In CTP 2.3, when we turn on functioning inlining and do the same thing:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;
No more nested loops hell. Now the function gets an adaptive join plan with parallelism, and finishes immediately.
Thanks, frens.
And 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.