All About SQL Server Stored Procedures: Plan Cache Pollution
Video Summary
In this video, I delve into the intricacies of temporary tables and their impact on SQL Server’s plan cache, specifically how they can lead to plan cache pollution or an abundance of query plans. I walk through creating and executing stored procedures that use temp tables in various configurations, demonstrating how these operations can result in multiple entries in the plan cache. By understanding this behavior, you’ll gain valuable knowledge for managing your SQL Server environment, even if it doesn’t directly dictate your choice between temporary tables and table variables. This is part of our ongoing series on SQL Server performance optimization, with future videos focusing on practical performance considerations to help you make informed decisions about these critical components.
Full Transcript
Erik Darling here with Darling Data, and we are going to, in this video, continue our joyous journey into SQL Server Store procedures, and we’re going to talk about how temp tables can cause plan cache pollution, or lots of query plans. The thing that I, two things that I want to say about this up front, one, this doesn’t affect query store, because query store doesn’t care about these things, and two, that I don’t care about the plan cache. I used to love the plan cache. I used to do a lot of work on SP Blitzcache to, like, get in there and find stuff and dissect that XML and, like, really, like, analyze plans. And then the more I did that and the more I used the plan cache, the more I was like, wow, this plan cache is three hours old. What am I going to talk about? What am I going to talk to you about? What, you want to know, like, why things were bad yesterday or a week ago? Get a monitoring tool, bucko. Like, I just, I just have a very hard time finding much utility in it all in the plan cache, aside from when, perhaps, query store settings are not capturing certain query plans. Like, with query store, you can set it to capture all, which means everything, which means everything, which is too much for most people, or you can set it to auto, so that some internal mechanisms figure out what plans belong in the plan cache. And then there’s, like, 2019, I think, introduced all sorts of, like, query store capture policy. So you can set specific things on, like, execution, CPU duration, things like that, to figure out which queries you’re going to allow to end up in there. So I do want this to be, this, I want you to file this under, like, SQL jeopardy, like, good knowledge to have, but maybe not knowledge that’s going to be important for you understanding when to use a different type of temporary object. This is not an excuse to use table variables. Please don’t take it as such, because I will come to your home and smack you until you cry, and no one in your family respects you anymore.
So, with that out of the way, if you would like to avoid that situation, you can get a membership to the channel. I don’t know if I’m allowed to do that. I think that might be extortion, or racketeering, or one of those, one of those RICO predicates. Anyway, there’s a link in the video description for you to do that. If perhaps someone else has extorted you, or racketeered you, or whatever, and you just have no more money, your pockets are turned inside out, you can feel free to like and comment and subscribe, or else. And you can ask me questions, and you can ask me questions privately that I will answer publicly on my office hours episodes of the Darling Data Dandy Hour, or whatever. I don’t know.
If you need help with SQL Server, and you want someone to threaten SQL Server into subservience and performance yeses, I am a consultant, and I consult on all matters related to SQL Server performance, and more. Health checks, hands-on tuning, responding to performance emergencies, and tuning your developers, actually. I will tune your developers, so that you don’t have performance emergencies anymore. You can avoid those in the future. You can finally sleep through the night. No more pagers going off, or whatever happens.
I don’t know. Maybe it’s too soon for that one. Anyway, if you would like to get some SQL Server training from me to you for the rest of your life, for about $150, you can go to training.erikdarling.com, where you will see the full expanse of my hand. And you can use that discount code. And again, there’s a link down in the video description, so you can get all that stuff. SQL Saturday, New York City 2025.
You can come to me. You can come see me in person. You can see this Adidas shirt in person. Maybe it’s not this specific one, but an Adidas shirt. I’ll be there serving lunch, smoking cigarettes, maybe getting drunk out back. Who knows? But anyway, come to the event. It’ll be a great time. With that out of the way, let’s talk about these plan cache shenanigans with temporary objects.
Now, what I’m going to do is set up a couple of store procedures and run them in a few different windows, and then run a query that looks at the plan cache. So the first one, and there’s an alternate version of this one down here. We’re going to talk about that in a minute. This first one is called a spid. And what this thing does is creates a table called a spid and inserts a value into it.
And then we have another procedure down here called no spid, which creates a table, inserts a value into it, and then executes this store procedure. This, like, a spid, right? So this store procedure executes a store procedure above it. There’s an alternate version of this where I rename the temp table to match the name of the procedure.
One thing that I find is very, very useful to avoid these types of problems is to give your temp tables very unique names. Do not just name them all T or P or A or C or D or A1 or T1 or something. I have, of course, been guilty of that in the past, so I’m not, like, busting you down about it, because I’ve done it too.
But the longer you live, the longer you learn, the more you realize unique names for temporary objects that are descriptive of their task are often a good thing. So I’ll show you that second, though. And the second way I want to show you this is with a slightly different setup, where we have this not internal store procedure, which sort of is, I guess, kind of weird.
But this is just going to insert values into a temp table, but the temp table is going to be created down here. But I’ll show you that in a second. And I have this one equals select one here just to prevent simple parameterization.
I forget why I stuck that on there, to be honest with you. I don’t think it’s necessary for the purposes of this demo. But then we have this store procedure up here called internal, which creates a temp table called internal, selects a count from it, and then executes the store procedure not internal that we just looked at above. Quite frankly, I do believe I named those backwards.
But anyway, let’s make sure that we have all these in place as, well, I mean, I was going to say as God intended, but it’s pretty much how I intend. But for all intents and purposes here, I guess I am God. We’re also going to clear out the plan cache.
I know I just did that, but I like to make extra sure. And then what I’m going to do is I’m going to run this, both of these store procedures in this window. I’m going to run both of these store procedures in this window.
And I’m going to run both of these store procedures in this window. So I run these store procedures three times across three different spids. Now, we’re going to look in the plan cache, and we’re going to use a very specific query that does a little bit of an extra thing, where we’re going to cross-apply to sys.dm exec plan attributes.
And we are going to look for the attribute optional spid. Okay? And we’re going to see the values for that up here.
Okay? Attribute and value for optional spid there. And looking at the results, what we’re going to see is two references to internal and one reference to no spid with a value all of zero. So, like, if you just have a temp table in a store procedure and you call that store procedure, SQL Server doesn’t have to do anything interesting with it.
So, as soon as you reference that temp table with a store procedure that gets called by the main store procedure, or you call another store procedure that creates a temp table with the same name, SQL Server has to figure out some way of differentiating things. Because it’s the plan. It’s the plan cache, and the plan cache is full of goblins.
And what we’re going to see down here is three different plan cache entries, each for the sub store procedures. Okay? And each one for optional spid is going to have the value of the session ID that called it.
So, we have three from session 69. I didn’t do that on purpose, I swear to you. I couldn’t possibly have.
That’s going to be that first window that we executed stuff from. Then we have three with 74. That’s going to be the second window. And three with 75, and that is the third window.
So, again, file this under things that are good to know, not things that should dictate how you choose between temp tables and table variables. Before we go, I want to show you one additional piece of good news. And that is that if we run that first demo, right?
But we create a different version of no spid where we give the temp table that gets created in here a more unique name. So, rather than this thing being named a spid like it is in this one, we’re going to name this no spid, which matches the name of the procedure. Then we’re going to run this.
We’re going to clear out the plan cache and run this again. And we’re going to run just no spid here. Oh, wait. You know what? I have to do that down here first, don’t I? I do. We’re going to run no spid here first.
We’re going to run no spid here second. And we’re going to run no spid here third. And now, when we look at the plan cache, both of these are going to have a value of zero, right? So, unique temp table names do help reduce this problem.
But it doesn’t matter if you have a store procedure that references a temp table from another store procedure. Okay? So, if you have this outer procedure, you create a temp table, and then inside your other store procedures, you do stuff with that temp table because that’s perfectly valid.
It’s all in scope. Then you will end up with the optional spid thing. If you end up with the optional spid thing because you created temp tables with duplicate names across store procedures, an easy way of fixing it is to give your temp tables more unique names.
Again, this is not a good reason to pick table variables which don’t cause this outcome. I’m not going to say cause this problem. I’m not going to say cause this issue because I don’t consider it much of a problem or an issue given how crappy the situation most plan caches are in generally.
I suppose you could somewhat improve the situation of your plan cache by following my advice. But, you know, whatever. It’s the plan cache.
Use query store anyway. But this is something that is good to know about because you might be one of those people who still has that awful folder full of scripts from, like, dates going back to, like, 2002 that mined the plan cache for certain things.
And you might run them and you might see lots of query plans for procedures that have temp tables in them. And you might wonder why and say, dear God, I thought every query just got one plan. What is happening?
Microsoft has betrayed me. How could I possibly overcome this betrayal? Well, there’s ways. There’s ways.
Here’s your ways. Give your temp tables unique names. That’s the easy way to do it. But that doesn’t help if you are referencing a temp table from one store procedure in another store procedure. It doesn’t matter how unique the name is because you’re just calling back to that first one anyway.
Maybe multiple procedures and queries will do that. I don’t know. It’s all wild. Anyway, thank you for watching. I hope you enjoyed yourselves.
I know you learned something. But you learned something that is just good knowledge. This is not knowledge that you will use to dictate use of temporary objects. In the next video, we’re going to talk about performance.
And that is going to be what you will use to dictate your choice between temporary tables and table variables. This is not what we’ll do. The previous video on recompiles probably isn’t what’s going to do it.
The next video on performance, that’s what’s going to do it. All right. Cool.
Thank you for watching. Thank you for watching and fully comprehending everything that I say. I know that reading comprehension is somewhat difficult. But hopefully listening comprehension is much easier because I speak in a clear, precise, and authoritative tone.
All right. The dad you never had. All right.
Well, 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.