>> Scalar UDF Inlining is
a new capability available in SQL Server 2019.
Tune in to this episode of Data Exposed MVP Edition to learn more.
[MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed MVP Edition.
Today I'm joined by Kathi Kellenberger.
Welcome, Kathi. Why don't you tell us
a little bit about yourself and what you do?
>> Thanks, Anna. I'm really happy to be here.
I am currently the Editor of Simple Talk
at Redgate and also a DevOps Advocate.
>> Awesome. Well, we're happy to have you on the show.
Today you're going to talk about
something I don't know a ton about,
so I'm looking forward to learning as we go through this as well.
Now, I think we're going to talk about Scalar UDFs
and what they are and
how they've changed through our implementations.
But I was hoping you could just start and give
our viewers 100 level like,
what are scalar UDFs and what are they used for?
>> UDF stands for user-defined function.
A lot of shops will like to use UDFs to hide
code complexity and make their queries look
prettier and the scalar part means that one value is returned.
So this might be a function that a team comes up with
just so they can use it inside a select list or an order by,
so that's what they are.
>> Awesome. Thanks, Kathi, that really helps.
Now, from a performance standpoint,
are there any issues with leveraging these types of functions?
>> Unfortunately, yes.
While using functions in this manner,
in regular code is a great idea,
sometimes it's not so great for SQL Server.
It's really been one of
the red flags when I was doing consulting work,
I remember one time got a query to go
from like a half-hour down to a few seconds just
by eliminating the scalar UDFs
and just having the straight code there.
So yeah, there are a lot of issues.
>> Over time, and I think maybe in either 2017 or 2019,
we did add some new capabilities.
Can you tell us a little bit about those?
>> Yes. In 2019 as part of the Intelligent Query Processing,
Microsoft added this new feature called UDF Scalar Inlining,
that does what I was doing for my consulting customers,
it just puts that formula or query or whatever is
inside that function straight into the select list.
You don't see that,
you still get the benefit of having
that complexity hidden, but the performance can be better.
>> Awesome, cool. I think you have some examples,
can you show us how this works and how
we can implement it and maybe some of the challenges?
>> Yeah, I would love to.
So Anna, we talked a little bit about
the performance problems that
you can see with user-defined functions,
and here I've got an example.
Let's say we're running SQL Server 2017,
which I'm simulating by setting the compatibility level,
and I'm running a query against a couple of big tables.
These are from Adam Mechanic,
couple of big tables for AdventureWorks.
Inside here, I have this formula that could be more complex,
this one's not too complicated,
but when I run it,
this query should just run in just a few seconds.
Let's see, probably about five or six,
I'm going to come back to that if it doesn't stop soon.
There it goes, 12 seconds.
Typically, I've seen it,
five seconds, eight seconds.
So let's say that we use this formula
all the time and it would be really great to have this,
have a function instead of a dozen queries
or 100 queries or whatever.
I'm going to create this function,
it just basically returns the answer,
it's got the same formula that you saw before,
and I go in and decide to
change my code to take advantage of this new function.
Now, I'm not going to run this because it
took over five minutes to run when I tested it earlier today,
so that's a lot of issues.
What's really cool is that some of these functions,
especially a simple one like I just showed you,
if you are running SQL Server 2019,
notice I'm still using that UDF here,
it should run much, much quicker.
I don't know if it's, there we go, so this time seven seconds.
It's really cool for very simple functions like that.
>> Awesome. It seems like you didn't have to change any code,
you just upgraded the compatibility level
and you were much faster.
>> Exactly. That was a really simple function.
There are some other types of functions that folks might have,
so here's one that has a query inside it.
We pass in a productID in a year and it
returns the number of units sold for that year.
That's a little more complicated and actually,
instead of doing this in a function,
you could just have this in your query.
But again, a lot of folks don't want to
repeat code and they want to make their queries simpler.
So if I run this in this function, I just
created in a query and
it ran pretty quick because this is a small table.
If I am doing some troubleshooting,
let's say there was a large number of rows,
I might want to look at the execution plan.
In 2017, the execution plan is going to look something like this,
where everything that's happening
inside the function is inside this black box,
compute scalar operator, and you really can't see it.
Now, I noticed that in Azure Data Studio,
you do get some more information,
I don't have that here,
which is useful, you still have the problems.
This is what it looks like whenever you zoom in a little bit.
Whenever you're using 2019 and this feature,
we can actually see that we're touching these tables where before,
with 2017, we could
only see that we were touching the product table.
So this is really misleading
and it's hard to really know what you're
working with whenever you're query tuning or just troubleshooting.
One thing that I like to show is a extended event.
What this extended event is doing is capturing
the statements that are running inside that UDF.
Notice that I've got 997 rows affected.
Basically, I've got two lines for every time that function ran.
What's happening is it's calling that function and it's querying
those tables once for every row in the outer query.
That's part of the reason they're so slow.
It'd be a lot faster if you just wrote a join.
But again, make sure writing your query is harder.
Other things you can do in functions,
you can do tons of things in functions.
Some of the other,
I've shown you a formula,
I've shown you a query,
but here's one that's got an if statement.
That's something you can do in any function.
Luckily, this, you can also inline.
Did I say at the beginning what this feature was?
SQL Server Inlining.
Basically, what's going on is the optimizer
is taking that code and stuffing it in a select list.
You don't really see that.
It's happening under the covers.
For one like this,
it actually can be inlined.
The simpler, the better though,
I think, at this point.
If you have a while loop,
sorry, that is not going to work.
If you think about that,
how would you put a while loop in a select list?
You couldn't do it. Here's one table variables.
You cannot have a table variable.
I mean, you can, but it's not going to inline.
You can also nest.
Here I've got one where I'm nesting.
I'm calling another one that's not inlineable.
Basically, what would happen is the optimizer would just add
this function to that expression in the select list.
There's a lot of different things,
and if you go out to the documentation,
you'll see a bunch of requirements for this to actually work.
Really, the simpler, the better.
Clearly, you can run to find out if your function is inlineable.
So that very first one I showed you that just had the select list,
yes, it is inlineable,
and I showed you that it was.
If I add an if block, still is.
Once I add a loop, no.
Then, dusting shows that it is.
So you've got to really be careful because
under the covers, it's not going to be.
Here's one with a table variable.
This is really helpful.
But there could be situations where you
think that it's going to meet the requirements,
but it really doesn't.
One thing you can do to find out is take a look
at the XML of the execution plan.
If you see a section like this,
that means that it was not inline.
The optimizer said, "Sorry,
this is too complicated or it doesn't meet the requirements."
>> This is really interesting and really powerful got yous
for me to be aware of as I go through
these or viewers go through using these.
Just to recap, what you're saying is that if I use these types,
like the table or the while loop,
it will still run and say that it's okay,
but in the back-end,
it's not going to be using that inlining capability.
Is that what you're saying?
>> Yeah, exactly. You're not going to get
that performance benefit that you might be
expecting by upgrading in those situations.
>> Awesome. These are some awesome tips of how you can
check even though you think it's going to be okay,
you can double-check that it is inlineable.
That's really cool. I've never seen that.
>> Another thing I wanted to show
you is that you can control when it's used.
There might be situations
where inlining is not what you want to do.
I have an example of that.
You've seen me turn the functionality on and off
throughout this demo just by changing the compatibility level.
That actually will enable or disable all of the IQP features.
If you don't want any of them or you
accidentally have it at
the wrong compatibility level, you don't get them.
If you decided you just don't want this particular feature,
but you want the others,
you can use this alter database scope command
to turn it on or off, just that command.
But you might do some testing and find out that you
have a few queries that it causes a problem.
I've got an example.
Here's a query or a function
that calls this table that doesn't exist.
It checks first to see if the tables there.
If it is, let's get the count from there.
If not, we're going to get it from this other table.
Either the query or the function does compile, does run,
and if I'm running in 2019 and I have everything turned on,
it works, but I get an error message.
This doesn't light up red.
If it was doing this in Management Studio, it would light up red.
That may be a problem for you.
What you can do is use a query head to
disable it for just that particular query.
Here, it ran perfectly and it did not produce that error.
That's really all I wanted to show you.
I have a couple of resources I think that you'll
make available to our viewers.
There's just one more point I want to bring up.
That at this time,
at least last week,
it was not, this feature was not available in Azure SQL Database.
It's one of the very few times that something shows up
on-premises before the Cloud.
Usually, it's the other way. That's it.
>> Awesome. Thanks, Kathi.
This has been really,
really interesting and informative.
Not only did you show us what's scalar UDFs are,
but how they can be improved with
really no code changes by us
with this new feature called inlining.
You've also showed us some of the got yous and how we can track if
inlining is going to apply or
when inlining might not actually make sense.
I just want to thank you so much
for being on the show today, Kathi.
>> Thank you so much for having me.
It's been a lot of fun.
>> It's been fun, and we'll put some links to your references,
as well as Simple Talk in the description.
We encourage our viewers to go check that out.
Viewers, if you liked this episode,
please like this episode,
leave us a comment of what you're doing with scalar UDF inlining,
and subscribe to our channel.
We'll see you next time on Data Exposed.
[MUSIC]