Have All Thirteen
Video Summary
In this video, I delve into an interesting quirk of SQL Server execution plans when using bad dynamic SQL—specifically, when concatenating values directly into the `EXEC` statement. I demonstrate how, despite the common belief that such practices don’t lead to plan reuse, certain conditions can result in unexpected parameterization behavior. By running a series of queries with varying numeric values, I show how SQL Server’s simple or auto-parameterization can cause execution plans to be reused in ways that might not be immediately obvious. This video aims to highlight the nuances of dynamic SQL and execution plans, providing insights into why and when plan reuse occurs, even in seemingly straightforward scenarios.
Full Transcript
Ehh, it’s you again. Uh, Erik Darling, doing Erik Darling data stuff, and loving every, every other minute of it. Maybe, maybe every other hour. I don’t know. Everything’s, everything’s fun though. Everything’s great. Uh, I wanted to talk about a funny little quirk with, uh, how execution plans sometimes get cached when you use the bad dynamic SQL. And by bad dynamic SQL, I mean when you just use exec and when you concatenate things right into the string like this. I don’t mean like the good kind of dynamic SQL where you use SP execute SQL and you don’t leave yourself at least somewhat prone to SQL injection problems. So, what’s gonna happen here? is really best described by a demo. So, I’m not gonna talk too much. I’m not gonna preamble too much about it. I just want to point out that I’m, I’m using nvarkar 10 here because nvarkar 10 is the length of the longest possible integer after, after, after, well, no, it’s not like after this thing goes up a digit, but after this thing goes up like a number, this thing is no longer a regular integer. It is then a big int. And big ints can get very long. So I left, I left nvarkar 10 is, you know, the, the, the cap for how long that number is.
that number will ever be. Now, when I run the, when I run these queries, something kind of cute and funny is going to happen. And we’ll look here and we get some results back and we’ll look and we’ll see. We wouldn’t expect that using exec would result in plan reuse, but in this case it does for a very specific reason. We see that we have one instance of this query and our, uh, our literal value, our, the value that we tacked on there has been replaced with a part of this query.
parameter. So SQL Server has done something a little bit tricky. If we scroll over here a little bit, we’ll see that that query actually got executed twice. Now what happened is the reason why we got this thing over here, we got this, uh, this parameter replaced our literal value. That’s a side effect of something called, uh, simple parameterization or auto parameterization.
Uh, it’s, it’s referred to, it’s been referred to as both over the years, but that happens when you get a trivial plan. If I go over here and I hit a four and I hopefully zoom in, Oh, that didn’t do it for some reason. Uh, that, all right, let’s try controlling one. There we go.
When we run in and do that and we look, that’s a trivial plan is, and that’s the thing that makes, uh, simple parameterization or auto automatic parameterization possible. You can see that our value got replaced by a one there. Now that’s all well and good, but where this starts to, to go astray a little bit is, uh, if this number changes. Now it’s not like if I change this to 25, it’s going to make a difference. We’ll still get plan reuse there.
And we still get that one line with, uh, the select count query. It is still parameterized. And if we can slide over here a little bit, that still has two executions that’ll remain true. Even if I go up to two 55 and I look at this and we see that this is still that same select count.
It’s still parameterized and we still have two executions right there where this will get where this will get changed, where this will be different is if I change that to two 56 and I run this and now we’ll have two instances of that count query. They’ll both be parameterized. There’s an at one there and an at one there, but the difference now is what that at one got parameterized to. If you were paying really close attention in the first plan, you may have noticed under the parameter list that one here was a tiny int.
Now, if I go look at the other query plan, it’ll still be simply or automatically parameterized, but the parameter type will be different. If we go look at the parameter list now, it’s not, not a tiny int. It’s a small int. So a SQL Server for values between one and 255, which is a tiny int, it will create a plan where the parameter has a type of tiny int.
As soon as we get above that, it’ll move up to a small int. And if we say two five six zero zero zero, I believe that should, that should get us into the regular integer range. If we run that, we’ll still have that second plan, right? If we go and look at that only that second plan this time, we can see that the parameter list now shows us that the data type is an integer. So it moved beyond a tiny int or a small int, which caps off at like 65,000 or something, if I’m remembering correctly.
I hardly ever remember things correctly, which is why I have to think about things so hard. So you can, with simple enough queries, get some plan reuse from Dynamic SQL. Granted, this all goes out the window as soon as you do something more complicated where you can no longer get a trivial plan or a simple slash automatic parameterization.
There’s all sorts of kind of weird and funny rules around parameter, around the simple automatic parameterization stuff and around the trivial plan stuff. I’m not going to get into those here because I just wanted, I wanted to keep this as a short video. So in some cases you can, in some quirky cases, you can get plan reuse when you use exec, even when you concatenate variables, variables, variables, variables directly into the string.
But under most circumstances, your queries are just going to be big, bad, gnarly, complicated enough that you won’t get a trivial plan, nor will you get simple slash automatic parameterization. All right, cool. Anyway, that’s all I had.
Thank you for watching. I hope you learned something. I hope you enjoyed yourselves. And I will see you in another video, another time, another place. I don’t know. Maybe I’ll even be sober for that one. We’ll see. We’ll see how it goes. It’s a weird thing. Thank you.
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.
Just to nitpick, negative int could be 11 characters long.