Why Operator Times Are Wrong For Some MSTVFs In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the peculiarities of query plan times for multi-statement table valued functions (MTVFs) and why they can appear so strange. While I generally advise against using MTVFs due to their frequent performance issues, I explore two specific examples in detail. By examining three different queries that call these functions, we uncover how disabling interleaved execution is crucial for getting accurate timing information. The video highlights the discrepancies between actual runtime and the misleading query plan times, offering insights into why MTVFs can be problematic and when they might not provide the performance benefits one hopes for.
Full Transcript
Erik Darling here with Darling Data. And if I look a little bit different in this video, it’s because I’m really happy. So, you and I, we’re gonna get happy together. We’re gonna get real psyched on all this. You and me. So, in this video, we’re gonna talk about why query plan times for some multi-statement table valued functions look really weird. Now, I don’t want you to think that I contain, that I condone, not contain, I contain, I contain multi-statement table valued functions. Multitudes of them. Multitudes of multi-statement table valued functions. No, I don’t want you to think that I condone the use of multi-statement table valued functions because in general, they’re, in my experience, they are either like a complete performance albatrosses tied to the next of your queries or they’re just whatever. I don’t think I’ve run into a situation in my, in my, in my, my history where switching to a multi-statement table valued function made things dramatically faster. I suppose there’s some, there’s some chance of that happening if you like, you know, were splitting up a larger, more complicated query and you decided to use one to like, to like, materialize some intermediate results but even, even that’s a bit of a, of a, of a, of a far-fetched scenario. That’s a pretty big gamble because of the many limitations that table variables have in SQL Server. And if you have questions about that, watch the rest of my channel videos because I talk about it a lot. All right? So, do that. Do, do, do a little bit of diligence. All right? A little bit of diligence is due from you.
You, you won’t, you won’t, you won’t, you won’t be one diligence. So, uh, we’re going to look at two different multi-statement table valued functions. Uh, and we’re going to look at three different queries calling two different multi-statement table valued functions. That sounds about right. So, this is the first one. It’s called score stats. Uh, it accepts a user ID integer. It inserts some stuff into a table variable. Well, actually, let’s, let’s zoom in here a little bit. This is what makes a multi-statement table valued function. A multi-statement table valued function and not an inline table valued function is that we are returning a table variable. Right? This is our return clause at out table stuff. Uh, and I make everything with schema binding because I hope that, and hope and pray that someday I find, uh, a, a situation where schema binding makes things better or faster.
In the meantime, I dislike making anyone trying to change tables or columns experience some pain. Be honest about that. A little poke, a little poke on you. So, that’s the first function. The second function does something rather similar, uh, also with schema binding. Uh, we return a table, uh, it does an insert into the table and, uh, then returns the table down at the bottom.
I think I neglected to show that in the first one, that this is the final return statement returns our table variable. I know, calm down. Calm down. Please. It’s, it’s, it’s all too much. It’s all too much. So, these are the three queries that we’re going to look at. Uh, we have the first query, which, uh, calls the first multi-statement table valued function.
We looked at, uh, score stats. We have another multi-statement. Well, we have another query that calls the other multi-statement table valued function that we looked at called vote stats. And then we have, um, we have a third iteration of that query, uh, where I have specifically disabled, uh, interleaved execution. And the reason I’ve done this is because it used to be that if I ran these two queries back to back, if I ran this one, then the re-ran the same query, uh, I would get accurate execution times shown in the query plan, uh, on the second run, when it was, uh, you working with a cache plan.
We’ll talk about why in a second, but I don’t get that anymore. So now I have to use the disable hint to show you what’s going on. So for the first query and the first query plan, uh, we get accurate and honest times. Uh, this runs for 6.38 seconds. If we come over here and we click on the properties, like good little property clickers, and we look at the query time stats, we get, uh, the right timing on those.
Uh, this function is not, at least, uh, I don’t think it was, uh, this function is not eligible for interleaved execution. Uh, there’s, we’ll see in the other functions, there’ll be a message up in here that’ll say, oh yeah, you’re cool with that. You can, you can get me some functiony goodness. You’re a nice hip happening person.
Uh, but this one, uh, we don’t, we don’t have that. Uh, but anyway, this multi-statement table value function does return correct and accurate, uh, query timings. Uh, the second one does not. Uh, so the second one, the query that doesn’t have the hint, uh, where this thing says it ran for one second, is an absolute lie. And if we scroll down to the second query where I’ve disabled, um, the interleaved execution, it’s, it’s run for 45 seconds.
This query did too, and we can, we can verify that this query ran for 45 seconds. Cause we, if we go to the properties here and we look at the query time stats, we can see, uh, a complete and utter nonsense lie for CPU time. And then 42 point 42 and a half seconds of elapsed time, right?
So the CPU time is way screwed on this and the, but the elapsed time is probably more accurate. Now, coming back to what I was talking about with the interleaved execution. If we go in here and we look, uh, but this part of the query plan, uh, we’ll see this contains interleaved execution candidates is true.
How nice we have, we have found a truth in the world. Uh, and that is, that is just excellent for us. And then if we look at the properties of, uh, the table valued function call up here, notice the, notice this query plan, this query plan are a little different.
Uh, this one is just like, you know, blah, blah, nested loops, table valued function. This one is like table valued function sequence. And then we have a table scan on the, the, from the, coming from the table variable from, from within the function here.
But if we look at, um, if we look at the properties of the table valued function call from the, this part of the plan, you will see, uh, this handy dandy little thing that says, is interleaved executed true? True. True. It is true. It is not false. Uh, I think when it’s false, that, that node just isn’t there, which is crappy.
It’s like, I don’t know. Just make things, makes things confusing for people. So I don’t want, what I don’t want you to think is that, um, uh, disabling interleaved execution is what made this, this call, this call slow.
These calls both run for just about the same amount of time. There is, there is a slight difference in the parallel versus non-parallel plan. What interleaved execution does is, and the reason why this thing doesn’t show accurate cardinality is because what interleaved execution does is it, it, during like query compilation, it populates the table variable to come up with a cardinality estimate.
So like, it sort of like happens in these like two phases where like, like that happens and then the rest of the query runs using that, uh, using that estimate. So like, this query gets a better estimate and actually uses a parallel plan. So like, this one should be, this, that’s why this one is like a few seconds faster.
But these things both run like we, like we’ve seen because, because when I click on this and I go and I look at the query time stats, uh, this runs for 42 and a half seconds and this runs for about 45 seconds. So the parallelism helps a little bit for this plan because we got a better act. We got a better cardinality estimate, uh, coming from the function.
Uh, but in this one, like, so for this one, you know, we got, uh, a 30 row guess and we got one Oh eight, six, five, eight, six of 30. Uh, and this one up here, uh, we, well, I don’t know. I don’t know if that’s really better.
One Oh eight, six, five, eight, six of four, three, seven, eight. So, so I don’t know. Uh, sure. The parallel plans a little bit better. Um, I don’t, I don’t know if three seconds is really that great of a difference here.
Uh, I, I, if I, if you, if you or your boss or whoever were paying me to tune this query, I wouldn’t be like, Oh, just make the multi-statement table value function parallel. Cause three seconds, that’s as good as we can do. Uh, we, we would, we would do much better than that.
You and I, you would look at these query plans. We would ponder the majesty of interleaved execution. And then we would click quickly throw the whole thing in the garbage and rewrite this in a different way. Cause, uh, there’s, there’s really like, I love when Microsoft does this.
It’s so cute where they’re like, Ooh, we have this thing. That’s going to fix this crappy feature we have. And then it, it’s just so mediocre. It just does so little.
And you’re like, okay, wow. You’re real. You didn’t really move the needle there. Uh, it didn’t, didn’t work. Didn’t really do anything. But anyway, I don’t know. Uh, that’s, that’s enough of that.
Thank you. I love you. You’re, you’re wonderful, beautiful people. Uh, I, I appreciate you watching my videos. Uh, I appreciate everyone who, who, who throws me likes and who throws me comments, whether, no matter what the comment is.
Uh, and I also appreciate, uh, the, the nearly 4,000 data darlings out there in YouTube land who, who, who have subscribed to this channel and, and, and, and find it necessary to be notified, uh, when I, when I do these things.
So that’s all there. Uh, I’m going to prepare a different demo and probably record something else. So, uh, uh, I, yeah, thanks. Thank you for watching and I’ll see you in, in, in another video next time.
Goodbye.
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.