The case of scalar UDF inlining, where context is everything

Here’s an interesting story for you this week.

As part of the new Intelligent Query Processing improvements introduced in the upcoming SQL Server 2019, we find a new feature called scalar UDF inlining. This post is not about scalar UDF inlining exactly, but IQP-adjacent if you like.

It works by taking a typical scalar user-defined function (in other words, a user-defined function that returns a single value), and instead of showing a zero cost in the estimated query plan and causing no end of grief in query performance (especially for larger workloads), the optimizer takes the code from inside that UDF and transforms it into “relational expressions” (hey, that’s what the documentation says).

In other words, imagine if the code from a UDF was rewritten as part of the query calling it. There’s a good chance that performance is going to improve because it doesn’t become opaque to the execution engine.

The only requirement to take advantage of this feature is to have the database compatibility level set to 150 (which is SQL Server 2019 default compatibility).

Assuming no edge cases nor regressions, this can result in dramatic performance improvement with zero code changes, because the query optimizer is doing the hard work for you. As with all new optimization features these days, it can be disabled at the query or database level, and for the most part it’s a very useful thing, especially for those environments where you can’t make changes to queries.

Recently at SQLBits, I had a conversation with Pinal Dave about an interesting problem he experienced while preparing for a session demonstration of this new feature.

In that demo, the AdventureWorks sample database was initially set to compatibility level of 140 (SQL Server 2017 default compatibility) to execute a scalar UDF. At this point, the estimated execution plan showed that the UDF was given a cost of 0%, and performance was terrible (the expected behaviour). Then the database compatibility level was switched to 150 (which is all that’s required to enable this new optimization feature), the query was executed again, the UDF was inlined, and performance improved dramatically.

This is where it got interesting. As a test, the compatibility level of the database was set back to 140, but the query plan continued to inline the UDF. Curious. Flushing the plan cache didn’t change the outcome (even though we knew it wasn’t necessary). Had we discovered a bug in a preview version of SQL Server 2019? It was CTP 2.2 after all, and since then (at the time of this writing) CTP 2.5 is already available.

I took over Pinal’s laptop and ran the query again, sure that AdventureWorks was set to level 140, and turned on the Actual Execution Plan option. Sure enough, running it again showed the SELECT operator right at the start was using the version 150 Cardinality Estimator, which was inlining the UDF. How was it possible that AdventureWorks was set to 140, but using the new 150-level improvements?

It was then that Pinal and I looked at each other, looked at the screen, then at each other again. At exactly the same time we realised that the UDF was created in TempDB. By default, that database runs at the same compatibility level as the installed instance. So while AdventureWorks was set to 140, the demonstration made use of a UDF that was created in a different database running at compatibility level 150.

The moral of the story is to not assume anything, and if you think you’ve found a bug in SQL Server — even though it’s a preview release — the chances of that are slim. It’s also a neat trick if you want to take advantage of inlined UDFs, but can’t change the compatibility mode of the user database.

Leave your thoughts in the comments below.

Photo by Emily Morter on Unsplash.