What A Difference A SQL Server CTP Makes: Improvements To Scalar UDF Inlining

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:

SQL Server Query Plan
Tell Moses to get the baseball bat.

The important detail about it is that it runs for 11 seconds in nested loops hell.

SQL Server Query Plan
Watch Out Now

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.

SQL Server Query Plan
You’re cool.

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;
SQL Server Query Plan
Fad Gadget

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.



One thought on “What A Difference A SQL Server CTP Makes: Improvements To Scalar UDF Inlining

Comments are closed.