Plan Cache Pollution From Temporary Objects In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into an interesting aspect of SQL Server stored procedures involving temp tables. Specifically, I explore how creating a temp table in one stored procedure and using it within another can lead to unexpected behavior in the plan cache and query store. By walking through a detailed example with two stored procedures—one that creates a temp table and calls another that uses it—I demonstrate how this setup results in multiple compilations and recompiles, even when using the `KEEPFIXEDPLAN` hint. I also highlight the differences between traditional plan caching and Query Store, showing that while the optional spid issue affects the plan cache, it does not impact Query Store, leading to a cleaner execution history. This video aims to provide insights for database administrators and developers who need to optimize their stored procedures and understand how SQL Server manages query plans in complex scenarios.
Full Transcript
Erik Darling here with Darling Data Enterprise Edition. That’d be nice, right? Everyone should be Erik Darling for 15 minutes just to experience how bizarre it is. Have to talk about these things. Today’s video is actually sort of a reader mailbag comment. It wasn’t really a question. But it was a comment that I identified with because it’s something that I’ve run into. And it was, I’ve actually had two questions about it because there’ll be a link to the blog post in the show notes. And a while back, I sort of co-authored a blog post with Mr. Joe Obish about how this certain use of temp tables in stored procedures, like when you have a stored procedures, like when you have a stored procedure that creates a temp table and then calls another stored procedure where that temp table also gets used, not like you can share temp tables between stored procedures, how that can cause weird plant cache pollution. And there was also another sort of like reader comment slash concern about if that would affect query store. And we’re going to look at that today. So, I’ve already got my index created. I’ve already successfully used a stack overflow database. And these are the two stored procedures in question. Now, we’re going to look at these in one slightly different way towards the end of the video. But for now, this is good enough. Good enough for government work, as they say. So, in this stored procedure, this is the inner stored procedure, because this is the stored procedure that gets executed inside of an outer stored procedure. And this may look familiar because I use this in a recent video to do some other stuff. But in this one, we just select from a temp table. I’m just going to use a couple different variations on this query, one with no hint whatsoever, and the other with the option, keep fixed plan hint. Now, this is the outer procedure, where we’re going to create a temp table, we’re going to insert data into that temp table, and then execute the inner stored procedure to finish things off. We’re going to look at this from two different, two different, well, actually, like four different ways. We’re going to look at this from the point of view of compiles, recompiles, and of course, how it affects the plan cache. So we’re going to look at the plan cache information in here. And so what I’m going to do is to start things off, I’m going to make sure that query store and the plan cache have been cleared out. We don’t need this just yet, we’re going to do this towards, well, after we do some other things, just got to make sure that, make sure that, make sure that I have my semicolons in there. Can’t go a day without semicolons. And then we’re going to explore a blog post from 2019 written by the most beautiful man at Microsoft, Joe Sack, about reduced compilations for workloads using temporary tables, where some stuff happens. We’re going to click on the link in a little bit, don’t worry.
So what I need to do in order to start proving that I know anything about SQL Server to you is fire off a couple of different instances of my store procedure, along with these funny loopy things. So much like in the last video where I talked about how recompile, keep plan, keep fixed plan, and other things of that nature are compiled and recompiled and plan get reused in different ways amongst them. This is just a loop that goes through, executes this door procedure, increments some stuff, and eventually finishes and returns some results. And we just want to get a good, healthy number of executions. And for this particular demo, I’m executing this from three different windows, because what I want to do is get three different spids, three different session IDs, all using this stuff together.
All right, that’s the goal here, because that’s where we see stuff start to get way out wiggy weird. Now, this is going to be different from if we had just done all the work within a single store procedure, because that spid is actually going to get cached along with the execution plans. So if we come over to look at the compile situation for this store procedure, we’ll see a total of three compiles.
And it’s a little bit easier to just click on these things. So this is the store, this is the statement that does not have a query hint on it. And you’ll see this one looks a little bit different, because this one actually has some compile CPU stuff alongside it.
This one also shows three compiles, but there’s like no compile time or anything associated with it, all this line here. And this is going to be the one that has that keep fixed plan hint. Now, what the recompile situation looks like is a little bit different.
So because statistics changed on the temp table, this statement without that keep fixed plan hint recompiled 123 times. So we have 123 recompiles plus three compiles, right? Keep that straight in your head a little bit.
And let’s come over here and let’s look at the plan cache. What we’re going to see is something pretty close to what we saw last time, except a little bit different. It’s a teeny tiny bit different.
Because all those different spids were executing the store procedure at the same time from different connections. And SQL Server was caching those temp tables. If we look over here, we’re going to see three different spids show up in this optional spid attribute.
Not throw up in. That would be gross and smelly and like a New York subway. I mean, it could be either the New York subway, the sandwich chain or New York subway, the mode of transportation.
Either one, you get what you get. So if we look at really this statement here, right? This one in the middle.
Oops. Scroll bars. Just mutiny. Mutiny today across all technologies. Scroll bars included. We have 126 executions of the get average score by year outer, which essentially just creates a temp table and does the insert.
Right? And so there’s 126 of those. And that number adds up.
If you remember, if you come back to these windows, we have the 123 recompiles and the 3 compiles. Right? So that adds up to 126.
So every time this statement ran across those three spids, it either compiled an initial plan or recompiled. Right? So we had the initial three runs where it was like compile a plan and then 123 where it was like recompile.
Pretty crazy. Now, I guess the point that I’m making with this is that even though we get good plan reuse across the three statements that have the keep fixed plan hint. That’s these three right here.
I wonder if we just make this a little bit bigger. These three down the bottom have that keep fixed plan hint. So they don’t recompile constantly, but they do initially all compile a different execution plan. Right?
Because this optional spid value makes new plan cache entries. And if we look at these spids, 68, 79, 71, that’s going to match up with what we have down at the bottom for session IDs. I’m trying to figure out a good way to get.
There’s 68 right there. You can see it right next to the shoulder. If we do it, if we zoom in on this one, come over here. Where are you, baby?
Where are you? Why are you running away from me? There’s spid 79. And this should be spid 81. If we zoom in correctly over here. Sorry, 71.
Right? So we see that even with the keep fixed plan hint, we do get that optional spid cache. And we do get an initial plan compilation, but we still get the good plan reuse after that. Right?
So even with keep fixed plan, we don’t get the same, well, I guess we do get the same behavior in that we don’t constantly recompile after we compile a plan. But the optional spid cached along with that does create three different plan cache entries. This does not happen in Query Store, though.
And if we look at entries in Query Store for the get average score by year inner procedure, and only that. All right, if we look at this, we’re going to see, come over here a little bit, we’re going to see just 126 executions each. So by the time SQL Server gets to sticking stuff in Query Store, all the optional spid stuff is pulled out.
So the optional spid issue with the plan cache doesn’t transfer to Query Store. Query Store just shows 126 executions of each. Of course, you know, the one with the keep fixed plan hint is going to have just the three compiles.
And the one that doesn’t have the keep fixed plan hint is going to show the three compiles plus 126 recompiles. Sorry, 123 recompiles to add up to 126. But just looking at this, right, like we just get the, we just see the 126 total executions in there.
Where this changes just a tiny, itty bitty little bit. And we should probably, probably click on this post to, oh, Microsoft Edge, what’s new? I don’t know.
Just look at what’s new in Chrome. You’ll see about the same thing. So here we are. Again, the most beautiful man at Microsoft, Joe Sack. Back in 2019, which is a blog post about one of the early CTP releases of SQL Server 2019, 2.3. And in his pattern, the temp table is created here, but then the insert is done here.
Now, well, this does help with the recompiles. You still have to compile a different entry. So if I were to move the insert statement from the outer procedure and put that in the inner procedure, we would still see that insert with the optional spid.
We would see three entries for that. And I suppose I can just go and show you that so you don’t call me a liar because, you know, for some reason a lot of people think I just make stuff up, which, you know, I wish that I had the moxie to just make stuff up about SQL Server like so many other people seem to do in their various posts and other things, just make things up entirely or paste things in from chat GPT.
And it’s all just sad and lonely. All right. So we’re going to rerun this.
And what I should probably do just to make absolutely positively extra extra sure is reset query store and the plan cache. And again, we’re going to do the same thing where we go, but up, but up. And then we’re going to run these three.
And we’re just going to twiddle our thumbs until this finishes. Now, I do think that, you know, SQL Server 2019 did help with the recompile issue, but the optional spid thing does still contribute to the, like, extra plans being compiled thing, which isn’t great because the plan cache is such, like, a terrible, noisy, ephemeral place anyway that stuff like this can really add up.
And stuff like this can really make the plan cache a pretty bad place to look for performance problems. There are certain performance problems. I mean, not even performance problems. There are certain, let’s just call them workload oddities, that will only show up in the plan cache because you can’t, this stuff doesn’t affect query store.
Right? Like, by the time you get to query store, the option, like, see, query store is like optional spid. Who?
What? Just get, get, go away, go away from me. Why are you here? I don’t need an optional spid. Plans don’t get used from here. Get, get, get it away from me. So if we look at this, we have the, well, now we have 126 total compiles of the, the hintless plan. And we have something a little bit interesting in here, right, where now we have an additional statement has entered the fray.
And we have the three compiles here, right? So the, the, the, the insert statement didn’t recompile, but it never recompiled. We just see the text for it in this now because we moved it into the inner procedure.
So we still have the three compiles of the insert because if we go and look at the, we go and look at the plan cache now, that insert statement is going to be included in the, what do you call it? The inner store procedure.
So these three lines with the insert into filtered posts, these all get, I mean, again, they still get the three separate entries for the three separate spids, but they do get good plan reuse within that. So there’s 42 executions across all these, whereas these have, you know, 42 executions, but 43 plan generation numbers. Because we do this as a new plan almost every single time with the stats changes.
And if we come over here and we look at, let’s just narrow it down to this chunk with the, sorry, the inserts and the select statement. So we’re just like this span of like six rows. You know, we’re still going to see the optional spids now cached with the insert query now too.
This only happens with queries from the inner store procedure, not with the outer store procedure. So in a weird way, it might even be better. It might be better if we kept this the original way where the insert is done in the outer procedure, because then you only get the one plan cache for it.
You don’t get three separate plans cached for it with the optional spid put in there, right? So depending on how, like, how big of an issue this might be for you, you might want to consider moving your inserts into whatever outer store procedure calls there are, and moving your temp table stuff into just the queries from, like, not the inserts into the inner store procedure.
Now, this is one of those funny things where, like, I’ve long believed that query store should take over responsibility from the plan cache in many ways. Because in a lot of ways, it’s doing that more and more. Because in query store, you can force execution plans.
In 2022, you can apply query hints. Like, there’s all sorts of, like, feedback mechanisms within query store that don’t exist in the plan cache. And I really think that the query store should take over more responsibility from the plan cache, because the plan cache is just such an awful, again, very just unpredictable place.
It almost seems like a retrograde way of doing things now that we have query store enabled, you know, by default with SQL Server 2022. Now, query store does need help in a few different places. You know, the GUI is a disaster.
And a lot of the cleanup stuff gets real screwy. Like, with a few different clients now, I’ve had to run the SP flush query store to disk procedure on, like, an hourly schedule, so that query store doesn’t fill up because cleanup only happens, like, once a day or something.
And, like, it just gets too big and no new plans get in. It goes into read-write or it goes into an error state. There’s a lot of stuff with query store that needs help before it can really take the championship belt from the plan cache with these things.
But I think with the amount of responsibility that query store has now for directing query execution, both from the plan forcing, adding hints to queries, like, different intelligent query processing feedback mechanisms, that really we’re at the point now where query store should be taking more and more ownership of things that the plan cache used to take care of.
When you add into that, the query store is much better at managing historical data, and you have a much better track record, and you can see query regressions and, you know, all sorts of other stuff, there’s almost no sense in relying on the plan cache for much anymore, at least from what I can tell because the plan cache is just full of spiders and ghosts and ghouls and gremlins that I just don’t, I don’t like messing with it.
I avoid looking at the plan cache as much as possible when I have something better like query store or a third-party monitoring tool that I can rely on rather than having to, like, run it and just be like, oh, well, I mean, 99% of the plans were created in the last hour.
Do you care about, like, did anything bad happen in the last hour? Do you care about any, were there any performances in the last hour? No.
Okay, well, we’re out of luck. Like, the plan cache is just so often useless that I just kind of stopped looking at it. Unless I need to explore sort of weird issues like the optional SPID thing, and, like, why, you know, different queries might end up with different execution plans and how temp tables get used and shared and all that other stuff.
So there are times when it’s, you know, you do need to get some information there, but even getting that information is hard unless you’re constantly monitoring it because there’s so much churn and flush with the plan cache. So, anyway, if I had to summarize this video, it is that, well, it is an interesting and novel method of sharing data between store procedures to use temp tables to do that.
It can certainly have some interesting side effects on the plan cache. As much as possible, I would say that you should keep temp table creation population and, like, you know, select logic contained within one store procedure as you can.
If you have to, like, share temporary table data from one store procedure to another, just be a little bit careful about how you do that because the more logic you put inside the inner store procedure and the more concurrency there is with that store procedure, the more plans you’re going to see popping up and doing that stuff.
Of course, like we talked about, that, you know, doesn’t really affect Query Store because by the time everything gets to Query Store, everything is, like, the optional spit stuff is all parsed out anyway.
It doesn’t matter. So, anyway, a fun Monday video. Got to, it’s a good one for me because I got to share some stuff. I got to do some demos.
I got to rant a little bit about Query Store and the plan cache. And really, I don’t find those things fulfilling. It’s just, it’s nice to get them off my chest because who else would listen? I can’t, I can’t tell my wife about this.
I can’t, like, you know, head downstairs and be like, honey, you wouldn’t believe the plan cache. You wouldn’t believe what the plan cache does. You share temp tables between store procedures. You’d immediately zone out.
Like, yeah, okay. Go right to the phone on that. So, anyway, thank you for watching. I hope you enjoyed something. I hope you learned something.
Enjoyed yourselves? Learned something? If you like this video, there’s a thumbs up button that’s the nice thing to push for me. So I feel better.
And I also like getting comments. Topical comments. Thought-provoking comments. Good questions about what we’re talking about. If you like this sort of SQL Server content, you can subscribe to the channel.
Almost 3,500 people have done that so far, which, you know, maybe if I did a little bit more ASMR, my numbers would be better. But from what I can tell from previous recordings where there were perhaps some audio issues where there was a little accidental body noise ASMR, y’all don’t want to hear that.
So, you know, probably going to avoid that. So, anyway, thank you for watching. And like and subscribe. And temp tables.
Yeah. Temp tables.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.