This resulted in the following outcomes, comparing:
- A base query without the order total
- A query using the new UDF to return the order total
- A final query with the contents of the UDF inlined as a correlated subquery into the main query
|(A) Original Query (without order total)||(B) Query with scalar UDF||(C) Query with correlated subquery|
Inlining Scalar UDFs in SQL Server 2019
SQL Server 2019 introduced improvements that potentially allow scalar UDFs to be automatically inlined into the calling query, effectively doing some of the work for us that we did for query C in the tests above. I say potentially because:
- Not all scalar UDFs can be inlined (we’ll cover some of the common reasons why later on).
- Even if a scalar UDF can be inlined, it doesn’t mean SQL Server will inline it in a given query. And if it does, it still doesn’t mean you’ll end up with the same execution plan and performance as we got with the manual inlining we did.
If you’re using SQL Server 2019 (or later, to those of you in the future), you’ll need to ensure the compatibility level for your database is set to 150 or above.
Do You Even Inline, Bro?
How can you tell if a scalar UDF can be inlined? First, let’s start with some common examples of when they cannot be inlined:
- When it uses time-based functions like GETDATE() and SYSUTCDATETIME()
- When it uses a table variable, table valued parameter, or Common Table Expression (CTE)
- When the scalar UDF is used in an ORDER BY clause
The best way to check is by querying the sys.sql_modules system table which has a bit column (is_inlineable). If that returns 0, then the scalar UDF cannot be inlined.
Using our scenario as an example, the following will check whether we can benefit from automatic inlining in SQL Server 2019 for our UDF:
SELECT * FROM sys.sql_modules WHERE object_id = OBJECT_ID('Sales.CalculateOrderLineTotal')
Great, it returns 1, so it could benefit! Running the query and grabbing the stats for the execution adds D to our previous tests:
|(A) Original Query (without order total)||(B) Query with scalar UDF||(C) Query with correlated subquery||(D) Query with scalar UDF (SQL 2019 auto inlining)|
We’re now running the exact same query as we did when we originally introduced the scalar UDF into the query (B) that resulted in a huge performance hit. This time, however, performance is noticeably better in terms of Duration, CPU, and Reads. It’s still not as good in this instance when compared to the manual approach to the inline scalar UDF in (C), where all metrics are much lower still. But since the only thing we changed was to switch the database to compatibility level 150 in SQL Server 2019, that’s not too shabby for an out-of-the-box improvement.
If we look at the execution plan now for D, we see:
This looks much more similar to the manually inlined approach. Importantly, we do now have much better visibility of the work the scalar UDF is doing, which we didn’t have at the very start prior to SQL Server 2019/compatibility level 150. But there are some differences compared to the manual approach—we now have a parallel execution plan and a Nested Loop (Inner Join) operation instead of a Hash Match (Right Outer Join) operation for example.
These improvements in SQL Server 2019 could yield some performance improvements when you migrate your database—possibly unexpectedly if you didn’t think you had an issue with scalar UDFs before!
We can never take our eye off the ball when it comes to checking the performance of our queries. It is often the case that there is no silver bullet, and the inlining performed by SQL Server 2019 is very much a case in point. However, as shown above, it’s a welcomed improvement.