Query Store Query Plan Confusion (Paramaters and Variables)
Video Summary
In this video, I dive into a peculiar issue involving query store and local variables in SQL Server. I explore how using local variables can lead to confusion when examining query plans and query store data, as the optimizer might make it look like parameters were used even though they weren’t. This behavior can be particularly frustrating when trying to diagnose performance issues or understand execution plans. Throughout the video, I demonstrate this with examples of both local variable usage and parameterized dynamic SQL, highlighting how the absence of a parameter list in query store for local variables can make it challenging to identify the actual cause of certain plan behaviors.
Full Transcript
Erik Darling here with Darling Data. You may be wondering what we care about today. I am too. I am deeply curious about what things we will find to care about in this video. It’s going to be interesting. Anyway, if you’re ready for the big reveal, I’m going to talk a little bit about query store, query plan confusion. Now, the issue that I’m going to show you here is one that has been irking me as of late, and it is a situation where when you use local variables in a query, query plan kind of makes it look like they were supplied as parameters. And you might get very, very confused as to why your query plan and query store looks like it used a parameter but has no compile time parameter value. So we’re, tyle wanted.p so, it was24t also t free my efforts to continue bringing you this hard-hitting, cutting-edge SQL content, you can click that link in the video description right there, and you can sign up for his membership for as few as $4 a month.
If you have spent all your money on other hard-hitting, cutting-edge SQL Server content, well, I guess I’m just going to have to do better next time. You can do other things to support the endeavors of this channel, like liking, like commenting, and like subscribing, just like that.
If you want to ask questions privately that I will answer publicly, you can go to the Office Hours link, which is also in the video description, and dump the question in there, and I will answer it at some point in the future.
Usually I do those videos once I have five answerable questions, and those usually get done, I don’t know, I try to do them once a week. Depends on you, though.
I can’t answer questions if you don’t submit them, so you should submit them. If you need help with SQL Server beyond what you think just a common YouTube video or blog post or even a well-thought-out Office Hours question might provide, well, I am the best consultant in three out of four hemispheres of the world, so you can hire me at a reasonable rate, mind you, to do all sorts of good things for your SQL Server.
If you would like some training content at an also incredibly reasonable rate, you can get all 24 hours of mine for about $150. again, the fully assembled URL to attain that level of enlightenment, that nirvana, is also in the video description.
Click away, my friends, click away. We have SQL Saturday, New York City, May the 10th of 2025. For those of you in the American area, well, come on by.
It’ll be a hoot. Isn’t everyone looking for a reason to come to New York City? Anyway, with that out of the way, let’s look at this junk.
All right, so I’ve got two queries here, obviously. I’ve got this query up at the very tippity top, which uses a local variable, that is, in which we declare a variable.
We set it equal to a value, and then we use it in our where clause. This is a local variable thing. I should have done that a little bit differently.
Let’s do another take on that. Let’s say that underline didn’t go so well. Let’s see if we can get a steady hand on this one. It’s not very easy doing this with a mouse, having a steady hand.
So we have declared a variable up here. We have used it in our where clause down here, and I have put a helpful little comment right here that says local variable demo. Down below, I have switched, I have changed the game entirely.
In this one, we use dynamic SQL, parameterized dynamic SQL, where we still have a declared variable, but this time we make some dynamic SQL, and we feed that dynamic SQL a parameter.
And this is, you can see, a very helpful comment here, parameter demo, because the vote type ID in this one, we’re going to go for the steady hand on this.
Oh, boy, that’s not going well. Jeez. For a guy who’s already had four drinks today, you’d think this would be going better, but anyway. Within, in this, in this instance, we have, I mean, we have still declared several local variables, three local variables, but in this case, we have passed our local variable as a parameter to the dynamic SQL block, and we have used the lovely talented SP execute SQL to execute our parameterized dynamic SQL with the value for that vote type ID.
So let’s run these, and we’re going to look at the results, and the results are not going to be all that interesting or spectacular. I promise you, it’s not going to be anything all that great worth looking at.
We are going to have two queries, one with a quite, I mean, both with a quite obvious missing index request on the votes table, because we don’t have an index on vote type ID, so we have to scan a clustered index.
This is not the problem. I mean, you know, you could consider the cardinality estimate for the local variable demo using the density vector estimate is problematic, right?
Because it guesses 3, 7, 8, 0, 6, 2, 0, 3.78 million rows. Wait, that’s a, yeah, seven digit number, right? Because I have three fingers left over. Okay.
Seven, three left over out of 10 means these are seven. So, that, I mean, that’s probably not a great guess, right? But that’s what we get from local variables. The parameterized one, of course, gets a good guess on the first compilation.
If we, if we did something real, real fun, and we say, change this to three, and we did this again, we would, we would get parameter sniffed. And, you know, that 700 and something row guess would no longer be very valid, right?
And all of a sudden, we, we, we have like a reversi problem, but that’s not really what I’m here to talk about. What I’m here to talk about is, of course, when we look at query store, what we get from both of these in the query text.
And I, the reason why I put those comments in the query text is so that we could, we could differentiate very easily between one query and the other. And if you look here, both of these look identical up until this point, right?
Both of these look like parameterized SQL got passed to SQL Server and we, and we executed them that way. But that’s, but, but the, that didn’t, that isn’t what happened at all, is it?
That local variable did not, did not get, was not a parameter. Only in the dynamic SQL branch was it, was it a parameter. And where this can get really annoying, right?
And let’s, let’s look at the parameter version first. If you look at this query plan, that is indeed the query plan we got. And if we go to the properties, what we’re going to see is a parameter list. Let’s, let’s squeeze this in a little bit.
Let’s have everything nice and compact for you beautiful viewers at home. We have, we have a parameter list with a compile time value for that, right? And the first time we executed this, we did indeed compile it with four.
Great, right? Good job, parameter demo. Where things get annoying, of course, is if you were to look at the plan for the, the, the local variable demo, well, go, go to the properties just like we did before, but we, we don’t have a parameter list here, do we?
There is no parameter list. There, there is optimizer stats usage, right? No, like this isn’t going to help us much. All right? This doesn’t, doesn’t do much for us.
This doesn’t do much at all. It doesn’t, doesn’t help us ascertain anything. The only thing that you could maybe do is, you know, if, if you, if you see a query like this in query store and you, you look at the query text and you think to yourself, oh, wait, that’s the wrong, that’s the wrong one.
We need this one. Oops. Gave up, gave up a little something on that. Sorry about that. Don’t tell anyone. The only thing that you could maybe do with this is, you know, just maybe run this.
Like if you don’t see the parameter list in there, just, you could probably figure out that it’s a local variable by declaring this as a variable and rerunning this with any value in there or, you know, like any, many like couple values over like, you know, a few execute to two or three executions.
And if you keep getting the same card, like bad cardinality estimate, then that might be a sign that it was indeed a local variable. Now, if we did something a little bit different, right?
And let’s say in the, well, I mean, in either one of these, if we did like option recompile, and let’s, let’s steal this.
And let’s, let’s do this here as well. Just so we have, we have adequate things to compare here. And I realized that the, the boat type IDs are different in there, but if we, but that’s actually, you know, sort of a helpful thing as well.
If we look at these, right, we’re going to have two more instances of local variable and parameter text in here. And if we look at the query plan for either of these, oops, let’s keep doing that.
If we look at the query plan for either of these, the parameter value rather than like be like, rather than having to go over here and look, there’s not going to be a parameter value in either of these, right?
I’m just going to park that here. So that stays up. We don’t get parameter values for either one of these now, but the literal value is going to be embedded in the, the predicate here, right?
So you can see predicate where vote type ID equals four, for the, the local variable demo. And we’re going to see the embedded vote type ID equals, two for the parameter demo.
So the, the recompile hint will change things up a little bit. But, you know, we still have no, like no very good sign for either one that a, that a local variable versus a parameter was used.
Do I wish that there were an easier way to identify this? Yes. It’s not, not very easy or straightforward to do that. Do I wish that I had remembered my end prefix here?
Of course I do. I didn’t, I now I look quite foolish in front of all of you lovely people out there, but I do hope that you can forgive me. Anyway, uh, I, I didn’t enjoy this.
I don’t enjoy this one bit. Um, and I don’t expect you to either. So, um, when I, my usual spiel, when I, when I say thank you for watching, I hope you enjoyed yourselves.
I have a, I have a, I have a strong, strong feeling that no one has enjoyed themselves on this, on this occasion. So we’re going to skip that part. Uh, but I, I do hope that maybe you learned something.
Like, this is strange behavior. And, I do hope to see you in the next video where we’ll talk about some stored procedure stuff. Probably.
Maybe something about data types over here. Perhaps. We’ll see what happens. I don’t know. Just got to live that long. Anyway, uh, thank you for watching.
I’m going to go do that now. 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.