All About SQL Server Stored Procedures: Temporary Object Performance
Video Summary
In this video, I delve into the nuances of choosing between temporary objects in SQL Server stored procedures, focusing specifically on global temporary tables and table variables. I explore how these objects behave differently from each other, particularly when it comes to performance and recompilation issues. By walking through practical examples using SQL Server 2019 with compatibility level 150, I demonstrate the limitations of table variables—how they lack statistics and can lead to suboptimal execution plans due to their inability to understand the data within them. This sets up a contrast with temporary tables, which, while still having some performance challenges, offer more flexibility in terms of optimizing queries through recompilation hints and proper indexing.
Full Transcript
Erik Darling here with Darling Data. In today’s video we are going to approach the final frontier when it comes to choosing between temporary objects in SQL Server store procedures. Now, very little practical use case for global temp tables, the double hash sign ones. We’ve talked through a lot of differences between temp tables and table variables. This video ties back well to the kind of the video before last, so maybe I recorded these in not like an optimal order, but where I talked about recompilations with temporary objects because something that we’re going to look at in this video ties directly into like, like when I talked about table variables not causing recompiles because table variables don’t have statistics. And I talked about thresholds for temp tables. And I talked about thresholds for temp tables recompiling. Well, there are some things that can happen that mess up my whole spiel that temp tables are generally better for performance when temp tables are small. So we’re going to, what I want to walk you through in this one is how temp tables are sort of like using optimized for unknown hints and table variables are sort of like using optimized for unknown hints. In other words, I want to punch you when you do that. And I talked about thresholds for temp tables recompiling.
And temp tables are sort of like parameter sniffing where I’m like, cool, we have a problem. We have an interesting problem to solve when we do hit problems with them. Okay. So I’m not saying that temp tables are these perfect angels that always work. Just most of the time when you care about performance and you’re joining your temp table off to other tables, that’s what you should be choosing because you at least have a fighting chance of fixing performance problems with temp tables. Whereas with table variables, you are just screwed. So without further ado, okay, I lied. There’s a little further ado. And the little further ado we have to get through is my usual song and dance prior to the informational portion of the video. You can sign up for a channel membership. There’s a link down in the video description. If you like this channel and you say, you want to say good job, Eric with money, you can, you can, of course, do that. If you want to say good job, but not with money, you can do the free stuff up there. And you can even still ask me questions, uh, during for, for my office hours episodes, um, all of the relevant links down in the old video description. Uh, if you need help with SQL Server, uh, there are two important things for you to know about me. One, uh, I’m the best consultant in the world outside of New Zealand. And two, my rates are reasonable. Uh, uh, the other thing that you should probably know is that I’m, this is all the, this is like the stuff that I am best at. Uh, so if you need help with any of these things, this is the face that you want to see when, when your SQL Server is having problems, uh, if you would like, uh, some content for, to learn more about SQL, so SQL Server training content at the beginner, intermediate, and even expert level, well, I’ve got, uh, about 24, 25 hours of it. And you can get all of it for about 150 USD.
When you use the 75% off code floating right there on the screen next to me, it’s quite, quite a deal. Uh, SQL Saturday, New York City, 2025 coming up May the 10th. Boy, oh boy. I just, it just keeps getting closer. Doesn’t it? Like, like the asteroid that’s going to kill us all. Uh, taking place at the Microsoft offices in lovely Times Square, Manhattan. Um, if you’re anywhere in the area, well, you should, you should probably set this Saturday aside to show up and learn more about the thing that you do, you do the other six days a week.
Just work with datas, data’s bases and whatnot and all that other good stuff. But anyway, uh, let’s talk a little bit about, uh, the performance of temporary objects. Now, uh, what I want to do is show you this stuff in the context of SQL Server 2019 operating, operating a compat level 150 to sort of like split, split, split this branch off in the right place. Um, if you’re on SQL Server 2019 or a higher and your database is in compatibility level 150 or higher, there is a new optimizer feature called table variable deferred compilation.
Uh, prior to that, prior to those numbers, like earlier than 2019 and compat levels lower than 150, uh, you don’t get that. Uh, SQL Server just like will default unless you throw a dumb trace flag at it. SQL Server will default to a one row estimate from table variables.
Uh, and you can also throw a recompile hint to queries in those cases in order to get a table level cardinality estimate, not a statistical estimate of like what data lives in the table variable, but just a table level cardinality. How many rows are in the table, not what values are in the table, uh, you can throw a recompile hint at it to get that prior to all this. With table variable deferred, uh, compilation, you get slightly different guesses, but still none of them are statistical.
There is still no statistics object. There is still no histogram. There is still just a table cardinality guess. How many rows are in the table, not what rows are in the table.
So, um, what I want to show you here is that I do have my database in compat level 150. Uh, and I do have, uh, that particular feature enabled and turned on. It is on by default. I have not undefaulted it.
Uh, so we’re going to look at everything in that context. Now I’ve already created this index here, so we don’t need to recreate that. And, um, like if we look at the results of this query and you can ignore some of this stuff over here, this is just stuff I used to build up, uh, some text that was annoying to type out.
Uh, the parent ID, uh, column has the top, uh, five plus, top six, five plus one is what, what they call it in math. Uh, not just six, five plus one. Uh, it’s also what they call it if you’re building rate arrays.
Uh, the top five plus one in here where you’ll see zero has six million rows. And then after that, everything else has just something in the low hundreds of rows. So this is going to make for some fun times for us.
Now, what I want to show you is outside of the store procedure, sort of what happens. And this is not a, this is not a sensitivity issue. This is a table variables do not get statistics and have no idea what’s going on issue.
So if we run this code and we run this query, even with an option recompile hint, uh, SQL Server the first time around will not have a problem because the five rows that were in the table variable just did not have a lot of matches in here. Even though the cardinality estimates are like wildly wrong, uh, they’re not so wrong that this plan suffered from it. Okay.
Uh, if we reverse this in the words of Missy Elliott, and we put in zero, which if you recall zero had like 6 million rows associated with it. And we rerun this query, even with a recompile hint, this will take 10 or 11 seconds. Okay.
Okay. Because SQL Server still chooses the same execution plan, still, still makes the same cardinality estimates, but it does not really do a, actually slightly different cardinality estimates because only one row comes out of this. So the five rows that were in there influenced how many rows that thought would come out of here, which was 1755.
So this does change slightly, but it’s still the same exact plan in plan shape. Right. So this thing all takes just about 10, almost 11 seconds at the very end there.
So this is kind of the root problem with table variables is that SQL Server does not know what rows are in them. It only knows at best how many rows are in them. Right.
Again, not like how much data, not what data. Right. Okay. So with that out of the way, let’s talk about how this works in the context of store procedures. For simplicity, I’m going to use a table value parameter to handle this rather than a table variable inside of the store procedure.
Uh, the behavior here is just about identical in every single way that you would possibly have behavior, uh, within the store procedure, uh, and recompile hint or not, this is all going to go, you know, probably about as you’d expect. So let’s do this. Let’s run this procedure without a recompile hint and without this thing in there.
So if we run this query and we put, uh, the five rows that we care about into the table variable, again, we get that same execution plan. SQL Server makes that 1755. Oops.
Uh, go. No, you stay pink, but you clear out, makes that 1755 row guess. But because so few rows are actually matching for the IDs that we put in there, it’s not a big deal. So if we do the same thing with, uh, parent ID zero and we run this, see, we’re going to see the same behavior.
This is going to take just about 11 seconds again, right? So this thing is spinning and spinning and spinning. And like we get the same, not, we get the same query plan again, the same slightly different guess.
The, the guess is actually going to remain, um, actually, look at that. We got a slightly different table variable deferred cardinality estimate guess there, but, uh, it still remains a bad plan choice because SQL Server is not like guessing great anywhere, right? Getting 6 million rows in these loops is much worse than getting like, you know, 11,000 rows or whatever in the, in those loops.
So let’s come back and let’s throw an option recompile hint into the mix. Because people often credit option recompile with solving all sorts of problems. And you know what? I agree.
Anyone who says, uh, option recon, like be careful with option recompile, watch out for, you’re going to have higher CPU if you use option recompile all the time. Uh, they just like to make things hard. Option recompile solves a lot of problems.
I’m not, I’m not going to lie to you about that. Uh, so let’s try this again. And with the option recompile hint again, you know, the, this five row query, uh, you know, just about all this, everything is the same in here. Nothing really changes everything fine.
But you know, the, the bigger deal is that, uh, you know, the, the, the zero plan, right? When we execute this again with zero option recompile still doesn’t do anything for us. And just to like, come back and show you like this table variable is backed by this table type, which has a clustered primary key on parent ID.
We’re still not getting any stats on this, even with the index in place. And you can validate that in the query plan. If you look at the properties and you look at optimizer stats usage, the only thing that’s going to be in here is for the post table.
Note that there is no optimizer stats usage for the table variable. And you can, you can even further sort of, um, uh, you can even further sort of validate that by trying to update statistics on the table variable. SQL Server will give you this red squiggle.
And this is not just like a parser issue. See, like SQL Server will be like, you just can’t, you can’t do that on television. Right. It seems like you get an error just trying to create the procedure. So there are no statistics on the table variable to update, to try to improve things.
This is, this is where the temp table thing has a direct comparison. All right. So let’s, let’s do this.
Let’s come down here. And let’s create or alter this procedure. This is our, uh, this is our table variable. I guess it’s not really a one row table variable, uh, varying row temple table variable with a, with a temp table where, uh, rather than rely on the tape of the table value table variable or table value parameter. In this case, we’re going to create a temp table, uh, again, with the clustered primary key on it.
And we’re going to insert the contents of the parent IDs table variable or table value parameter in, into there. Okay. And then we’re going to do our work off the temp table here.
All right. So there should be fairly, fairly obvious. Note that I have the same update stats and option recompile things available to us in here. So let’s create or alter this.
And this is, um, this is the sort of issue that I wanted to show you with when you have very small temp tables. Now, if you, if you recall the video from one prior to this, where I talked about recompiles with temp tables versus table variables. One of the things I showed you was an extended event that said, that showed us when, um, uh, things were recompiling because statistics changed with the temp table.
And that the smaller the temp table was like, like you got a lot more changes at first, but then it was small. And then like, when it was bigger, like you sort of needed like more rows going in to trigger the, the stats, the, the statistics, the statistics update change. So if your temp tables are always, well, I’m like fairly large amounts of data, like anything over like a thousand, 10,000 rows or something like that.
You’re going to see a lot of recompilations because of statistics changing with the temp tables. Like that’s just going to happen way more often because you have like way more data going in and out to, to count towards the statistics, to count towards the, like the number of modifications that would trigger the statistics update. So what we have here is a situation where we have a very small temp table, but that’s very small temp table does not have a statistics update triggered on it.
Like when a larger one would have. All right. So let’s like, if we run this first with the five rows, we get the same very fast plan, right?
SQL Server chooses things in here and, um, like we get like, okay performance from this one still. So now because I want like, you know, I’m, I’m, I’m team temp table when it comes to like performance being good. What I want to show you is when that can go wrong and how you can fix it.
Uh, so let’s run this and let’s, let’s see what happens. Where, uh, we have this execution plan now, and this is going to, uh, give us, uh, the cash and reused execution plan for the query that just ran. Right.
This is also going to take just about 11 seconds. Right. But this, at least for us is a solvable problem, right? With the table variable, there was no solvable problem. There was no, there are no statistics to update on the table variable.
And the option recompile hint did not help us discover what rows were in the table variable that we, that would change the execution plan. But we do have some hope here because temp tables do get statistics usage. Notice that now, instead of there being just one thing that opens up, we now have two things under optimizer stats usage.
We are going to have one for the temp table and one for the actual table. So if we zoom in and I move this out of the way of my giant head, uh, here, here are the statistics on the temp table that did not exist for the table variable. Right.
And here are the statistics for the base object, uh, on the, on the post table. Right. So there are statistics here that we can use. The problem is that because this temp table is so small, it cached and reused an execution plan where it still thinks the five rows that were in there before are going to come out of there, which is not a very good guess. Now you have to do two things in order to address this problem.
You don’t need the full scan on this. I’m just choosing to do a full scan because it’s a very small temp table and who cares, right? It’s not going to, not going to take any longer fully, fully scanning five rows versus fully scanning one row.
Guess what? Not a big deal, but we do also need this option recompile hint here because this is what’s going to give us a fresh plan for this query. Just the option recompile hint or just the statistics update is not enough to get us across the finish line for this one.
So let’s run this and now let’s revisit running this in the exact same order, right? We get this execution plan. SQL Server still does an okay job with this one.
Now, when we run this for parent ID, just zero SQL Server will do a different job. Notice that now we get a completely different plan shape. We no longer have a nested loops join and we no longer have the seek plus a key lookup.
We have this query here, goes parallel, takes just about 800 milliseconds. And instead of that, we have a scan down here and this solves our performance issue. Now, you may get very lucky with your temp table usage and you may get something like this that happens where, let’s say that this happens in reverse order, right?
Let’s recompile all this stuff. I’ve taken out the update stats and the option recompile hint here. If we just run this for parent ID zero first, SQL Server is not going to have any problems with this.
SQL Server chooses that same plan for down here. And then if we rerun it for the five rows up here, SQL Server is going to reuse that plan here. So there are going to be cases just like with parameter sensitivity where you get a good plan that works fine for anyone.
And there are going to be cases where you get a plan that does not work so fine depending on the distribution of data. The big difference is that with, again, with temp tables, there are at least things at your disposal that allow you to solve the problem. With table variables, there is absolutely nothing at your disposal to solve the problem aside from using temp tables, right?
You can’t update stats on the table variable because table variables don’t have stats. And you can’t use a recompile hint to get anything better because SQL Server guessing one row and not knowing the contents or SQL Server guessing five rows and not knowing the contents of those rows. Again, the recompile hint with the table variable just gets you how many, not which.
That doesn’t help. So whenever you are dealing with performance issues around table variables, one of the first things that you should be thinking is, should I change this to a temp table? And you should be thinking, yes, I should.
Because even if you run into performance issues with the temp table, you have things at your disposal that you can use to fix them. Now, is this all too much? Is this all something that Microsoft should be handling better or the SQL Server could be doing better?
Potentially. You know, it’s a very difficult thing to split correctly because you have to manage a lot of sort of expectations and behaviors that people have relied on for a very long time. Sure, there are probably some scenarios where this could be better.
Like, but, you know, figuring out a way to do this during query execution or during query planning or optimization or to make this somehow more adaptive would maybe be a little too difficult. Like, you know, one thing that is sort of interesting is, you know, SQL Server 2017 plus has like adaptive joins, but adaptive joins won’t make a call if like there’s not a covering index in place. Like you’ll never see, well, at least as of this writing, like I’ve never seen an adaptive join that was like, you know, like gave you an option between like, you know, hash and loop join, where the loop join portion was like a seek with a lookup.
It was always just like you could like seek or scan, but if there’s a, there’s going to be a lookup, the adaptive join is short circuited. You don’t get one. So is there stuff that could possibly be done to the optimizer that would make this an easier situation?
Probably, but you know, you, there’s, there’s a lot to, there’s just a lot of stuff that would make this really hard to get exactly right. But this is where the, the skills of query tuners is still very valuable and very useful to people who have performance problems. And as always, my rates are reasonable.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video where we are going to talk about, well, I might, I might take a short break from the, the, the store procedure stuff to talk about some of the open windows over here, because they are, they are quite interesting to me anyway. So I might take a short break to look at these things, but after that, we will, we will be getting towards the, the, the very wrapping up end of our store procedure series.
So we’ll see how, we’ll see how things go over the next couple of days. We’ll see how, see how my motivation lines up with reality. Anyway, thank you for watching.
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.