One of the more compelling features (so far) in SQL Server 2019 (starting with the recently released CTP 2.1) is inlining of scalar user-defined functions. This feature has the potential to significantly improve throughput for queries that use scalar UDFs, which has long been a performance bottleneck for a variety of reasons.

In one of my sessions, Set Me Up: How to Think in Sets, I discuss a variety of performance-inhibiting query constructs, including scalar UDFs. I thought it would be interesting to take the simple scalar function that I use in the demo and see what kind of difference that scalar inlining might make.

First, I restored the CorpDB database that I use in the session to my SQL Server 2019 CTP 2.1 instance and initially set the compatibility level to 140. I also ran script 001 from the demo to create the needed database tables (no need to create the CLR objects for this test). I then ran script 030 to execute the scalar UDF test. In a nutshell, this script

• creates a UDF
• runs a query that calls the UDF about 13,000 times, capturing the time required to do so
• repeated this test five times
• discards the fastest and slowest tests
• reports the average time for the remaining three tests

The UDF definition is

create function dbo.GetLine1ProductId (@OrderId int)
returns int
as
begin
	declare @ProductId int;

	select top 1 @ProductId = od.ProductId
	from dbo.OrderDetail od
	where od.OrderId = @OrderId
	order by od.OrderDetailId;

	return @ProductId;
end

The UDF is called in the traditional fashion.

select oh.OrderId,
	oh.OrderDate,
	oh.CustomerId,
	dbo.GetLine1ProductId(oh.OrderId) Line1ProductId
from dbo.OrderHeader oh
where oh.OrderDate >= '2016-01-01';

At compatibility level 140 (meaning that SQL Server is not able to inline the function), the test takes 373.530 seconds to operate over the 13,147 rows.

Next, I changed the compatibility level to 150 so that inlining is possible. First, I checked to make sure that SQL Server agreed that it could be done.

select o.name, sm.is_inlineable
from CorpDB.sys.sql_modules sm
join CorpDB.sys.objects o on sm.object_id = o.object_id
where o.name = 'GetLine1ProductId';

Since is_inlineable returns with a value of 1, it seems that SQL Server agrees that the function qualifies to be inlined.

At compatibility level 150, this query now takes 3.572 seconds complete.

This represents about a 100-fold performance boost with absolutely no code changes!

The query plan changes are interesting as well. The non-inlined query’s estimated plan shows both the overall query plan as well as the query plan for the UDF. Nothing surprising here. The overall query is dead simple, hiding the details of the scalar function.

The query for the UDF also contains no particular surprises.

With the compatibility level moved to 150 and with inlining enabled, SQL Server is able to roll the UDF operations into a single query plan.

The UDF is represented by the lower branch in this case. This is a dead-simple query in the UDF, but even so, it gets expanded out in a non-trivial manner. I’m still trying to sort out what is happening this branch of the query plan, but I think (though I’m far from sure) that it’s essentially pre-computing the “TOP 1” values for all of the relevant records. (This is similar to what I do in script 022, but instead of using a temp table, the optimizer utilizes an eager spool.)

Regardless, it’s awesome to note that this query goes parallel. One of the many performance implications related to scalar UDFs is that they have traditionally inhibited parallelism, but no longer!

In any case, this is a promising new world for scalar user-defined functions, and am certainly eager to see where things go from here.