This Won’t Interest You At All

wtfudf

Video Summary

In this video, I’m diving into a rather mundane topic—specifically, the behavior of scalar-valued functions in SQL Server across different compatibility levels. I walk through a simple query and then nest it within a function to illustrate how performance can vary significantly depending on the version of SQL Server you’re using. The video covers the differences between compatibility levels 140 and 150, highlighting how materialization of branches can impact execution time. If your name isn’t Forrest McDaniel, you might find this content quite dull; however, I appreciate your patience if you stuck around!

Full Transcript

I’m recording a very boring video for my friend Forrest. If your friend, if your name isn’t Forrest McDaniel, you will probably find this video very, very dull and uninteresting. You should probably just stop watching right here. I won’t take it personally if the watch time on this thing plummets because your name isn’t Forrest McDaniel and you would be terribly uninterested in this very boring material. Promise. So, without further ado, here’s some very boring material. I have this query. Alright, I declare a couple variables and I set each variable equal to something. Let’s say I’m going to do some stuff with them later. If I run this query with the query plans turned on and I look at the query plan for it, I have one seek into the comments table because I’m a top lad and I created an index that my query could seek into. Then I have one stream aggregate where both of these expressions are calculated. Actually, there are three things that calculate there, but, uh, so because, uh, you know, uh, I don’t know why there are three off the top of my head, but they’re, but the two that I calculated are in there, I promise. They’re, they’re both there. So, what I want to do is put that in a function and when I put that into a function, like, I’m going to call that comment score for some reason. I don’t know why. I’ll put that into a function and inside the function I’m going to declare those things, uh, set them, you know, declare my, uh, my internal variables, set them equal to stuff.

And then I’m going to return, uh, the comment count times the comment score. Good stuff. So, uh, the first thing that I need to show you is that in compatibility level 140, uh, if I run these queries, uh, they’re going to run pretty quickly because even though that’s a scalar valued function, it’s not running over a ton of rows. It’s not doing a ton of work. And I have proper indexes in place for my function. When we look at the query plan, like we know about scalar values, functions, it’s not going to tell us what the function did. It’s going to completely lie to us. If you’re, if you’re like, like creeped out by this, then you should just go watch my, my plan cache liars videos. Anyway, moving right along. If I run this and we get the estimated plan and we see what the function is doing, the function is doing exactly what the query did. We have one seek, we have one stream aggregate, even for the larger query that, uh, that hits more rows. We have one seek and one stream aggregate. The difference between these two is this is just where one user. I’m just getting that for one user. And the down here, I’m getting, uh, I’m getting the comments where for every user with a reputation over a hundred thousand where things start to get weird is in compat level 150. When we turn on scale, our UDF and lining. Now what I’m going to do is run both of these.

So the flow is about 10 milliseconds before not the end of the world, but it, it, it, it, it’s, it’s, it’s noticeable there. It’s even more noticeable in this query where before when it took about a second. Now we’re looking at it taking about a second and a half because each one of these branches with this stream aggregate is materialized. So this top branch takes 475 and this bottom branch takes 7.726 milliseconds. So about a half a second, a little bit closer to a second. And you can see the final tally on this plan is about a second and a half. The first one was should have been around a second or so. This gets even crappier. If we drop the index that I created on comments, I’m just going to get the estimated plan here because, uh, I don’t want this. I don’t want this video to drag on forever and ever.

But if we look at the estimated plan now and we see that we are indeed missing that index that I created earlier. Um, Oh, you know what? It doesn’t show up my, well, that’s my fault. It doesn’t show up in the estimated plan. It only shows up in the actual plan. So what I got to do now is, uh, run this and let’s go over here. Let’s do this. Let’s do this. Any who is active. All right. Yeah. Yeah. One. Run that.

And look at the execution plan and we will see that SQL Server has chosen to do two index spools, uh, one for each branch in there. Now I know that I could get around this by doing the math from the function all in one go. Like I could just, you know, up here, I could just say set, total comment score equals count big times sum. I know, I get it, but I think this is kind of a missed opportunity to fold some expressions in and do everything all in one go, because if you if you have a bunch of these they’re all gonna kind of add up. And I see a lot of scalar value functions that do a lot of variable assignment like this. If it’s not all in one line then you’re looking at having to rewrite the function to prevent all of those branches from expanding. Anyway, I’m gonna go get brunch now, or take a shower and then go get brunch. I’m still sort of in my PJs, but yeah. Again, totally boring stuff. If your name isn’t Forrest McDaniel you’re probably gonna not enjoy a single second of that. Totally uninteresting. But thank you for not watching, and I will not see you in the next video because you didn’t see this because your name isn’t Forrest.

Isn’t that funny? Isn’t it funny how that works out? You listened to me, didn’t you? You listened to me for once. Thank you. I appreciate it. Sweetie. thank you.

amp police

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.