All About SQL Server Stored Procedures: Local Variables

All About SQL Server Stored Procedures: Local Variables

---

Video Summary

In this video, I delve into the often-overlooked topic of local variables in stored procedures within SQL Server. While many developers use them for convenience or believe they are a panacea for parameter sniffing issues, I explore both their proper and improper uses, as well as how they can impact cardinality estimation and overall performance. I share practical examples from recent experiences where local variables caused unexpected slowdowns, emphasizing the importance of testing before implementing such fixes. By understanding these nuances, you’ll be better equipped to write efficient T-SQL code that avoids common pitfalls while leveraging the benefits of local variables when appropriate.

Full Transcript

Erik Darling here with Darling Data. And we are going to continue our magical demystifying tour of SQL Server stored procedures. And in this one we are going to talk about another thing that comes up quite a bit, and that is the use of local variables in stored procedures and how they can have strange and profound effects on many things. Well, I realize that this is a topic that has been well-trod on this channel, but again in the interest of completeness for a series about stored procedures, I’m doing a video about it here. And also this is a topic that I cannot get away from. Just this past week I was on a call with four senior developers for a fairly large company, and they were all puzzling over why they were and they were all puzzling over why this stored procedure was suddenly acting up. And, you know, when one of them decided to share their SQL Server Management Studio window with me, well, what happened was not a stored procedure. Of course, it was, creator-alter procedure was quoted out. The parentheses around the, you know, two or three parameters for the stored procedure were quoted out. And the little, uh, little parameters that they had, little parameters that they had, a few parameters that they had, had a little declare, uh, inserted above them and they had some values assigned to them. And they would hit F5 and have no idea why this thing was suddenly a lot slower than when they were testing it for some QA thing. And of course, we had to have the talk about the birds and the bees and local variables. And, um, as soon as, as soon as it was executed as a stored procedure, everything was, back to normal again. Um, it’s a rather a long store procedure. So I didn’t want to have them go through and put option recompile hints on every statement because that would have, that would have taken a long time to do and then undo. And then, uh, you know, they, they, they, they probably would have had, uh, perhaps a new bad habit formed for them, but, uh, it was, it was, it was, it was demonstrative enough to just execute as normal.

Now, uh, what we’re going to talk about in this one is, um, some of the okay uses of local variables, uh, some of the not okay uses of local variables, how to tell, uh, when local variables and other similar constructs, namely the optimize for unknown hint are causing problems. And of course, how to test code that is parameterized appropriately. So we’ve got, uh, we’ve got quite a rundown here and hopefully we can do it all before the death of the universe or something. Uh, if you enjoy this content, if you are gung ho on what the me and bats cook up in the kitchen and bring to you here on YouTube, uh, you can, uh, sign up for a paid membership.

We don’t get anything extra. You just say, thanks for doing a good job. It’s a little tip jar for me to keep doing these things. Uh, link in the video description, four bucks a month entry is the entry fee on that. Uh, you can of course go higher if you are feeling generous at all. Uh, if you, uh, are short the four bucks a month, perhaps because, um, I don’t know, maybe you spent it all on collective, collectible PEZ dispensers or, uh, the actual PEZ that goes in them.

Uh, that’s fine. I understand financial hardship, especially if you’re a government employee, uh, might be having a, having a tough time recently financially. Uh, you can do other things to support the channel. You can like, you can subscribe, uh, and, uh, you can comment. Just be nice. Some of you leave crappy comments.

And, uh, if you want to ask questions privately that I will answer publicly in my office hours videos, uh, in which I answer five user questions at a go, uh, you can go to that link, which is also down in the video description. Make this tremendously easy for you. If you need help with your SQL Server, even, even if you are a government employee, I will help, will help anyone who, who, who writes a check.

Uh, we can, uh, we can do any one of these things. Health checks, performance analysis, performance tuning, like actually fixing problems. Not just someone standing there telling you everything you’ve done wrong, but actually, actually helping you fix things.

Uh, if you are having a performance emergency or if you need training so that you do not cause any future performance emergencies, the easiest fire to fight is one that never starts, right? Uh, I am available for all these things. And, of course, as always, my rates are reasonable.

If you would like some training to get better at SQL Server performance tuning, uh, golly and gosh, do I have a whole bucket of it? Uh, and you can get it for about 150 bucks for the rest of your life. Just, uh, I would maybe recommend signing up with a personal email address because if it’s tied to your work email, you know, you know how that goes.

Uh, if you use the link up there and use that discount code, that will bring the price down to something affordable even for the, uh, recently departed. Phone keeps making weird, weird sounds. Uh, if you would like to, uh, in person see me do various, uh, administrative tasks, I will be at SQL Server, SQL Saturday, New York City, 2025, taking place on May the 10th in beautiful New York City.

Times Square, really the height of New York City, uh, where every subway stops, I think. Uh, and, uh, there’s a, uh, full day performance tuning pre-con on May the 9th given by Andreas Volter. And, uh, he’s got some performance stuff he’s going to teach, teach the world about.

Uh, so that, those, those, that’s going on. I’m still waiting to hear about shoring up some dates for other stuff. So hopefully I’ll be able to expand on some of this soon.

But with that out of the way, let’s talk about these local yokel variables. Now, uh, let’s, let’s see if Zoomit works. I’m going to hit control and one here.

We’re going to see what happens. Wow. First try. So, uh, most people use local variables out of convenience. Just purely, hey, I can declare a thing and do something with it.

Okay. Some people get it in their heads that local variables fix all their parameter sniffing problems. You will never have another performance problem so long as you avoid parameters.

Do you honestly think that if parameters were that bad that they would just stay in the product as a way to fool you, to ruin things for you? Well, it seems a bit goofy to me.

But just like you can’t fix a broken bone with some good vibes, you can’t fix parameter sniffing with bad advice. Uh, we’re going to talk more about that later on in the series, but I do want you to be prepared for this eventuality.

Uh, like many things that we’ve talked about so far, uh, local variables have their uses and they are a convenience for you to do various things with. But, uh, when you use them in specific ways, they can, they can really muck up, uh, cardinality, estimation, plan choice, and ultimately performance.

If you are a frequent watcher or viewer of this channel, you, you’re probably not terribly surprised by any of that. You’ve, you’ve seen me talk about it before. You may have even read some of my extended thought pieces on the, on the matter.

Uh, so, you know, it, it, I, I, well, I do try to, uh, balance up the, uh, the, the, the more advanced weird stuff on here. I, I do have to, uh, do some of the evergreen content that hopefully people find and solve some, some easier, earlier problems for them.

Uh, but local variables do present, uh, a rather attractive proposition for a lot of people who are programming in T-SQL. You can set a constant value for something like SQL Server doesn’t give, have like domain variables for stuff.

Uh, and you can assign values from rather complex subqueries to a single variable so that you don’t have to recalculate that rather complex subquery over and over and over again. Uh, you can also do other stuff for them.

Um, they are really handy for loops, uh, when you want to, uh, print or, uh, raise error some feedback about where, where, how your loop is progressing, things like that. Um, so they are useful for stuff.

Uh, one of those things where, you know, that I am okay with is, you know, either, you know, using it to like have a consistent batch modification time. Uh, this is, this is an okay thing to do for what I’m going to show you because this sets the batch modification time to one single value.

So if you have to update multiple tables to show a batch modification time, then it will be consistent for each step in the batch. Otherwise, the sys date time, the sys date time call will be, uh, recalculated every time it’s invoked.

So if one batch modification happens like five minutes after another one, well, that they’re going to, they, they might look like different batches because they’re spread apart and, you know, they don’t have the same batch time.

Uh, and of course, if you need to calculate some rather complex sub-expression and assign that to a variable, it becomes even more important to, uh, to stabilize that somehow so that you, you don’t get caught re-executing the same thing over and over and over again.

That can be, can be not fun, especially if, uh, this thing ends up interwoven as a subquery into a bunch of other queries. So where that’s okay with me is if you’re going to do something like this, where you just need to use that as a, as a scalar value and insert or an update or something like we’re just setting a value to something or inserting a value with something that’s totally okay.

There are good and convenient and sensible uses for local variables where things get, um, a little thrown off is when people start using them down in where clauses, I guess probably, uh, far less common would be in a, in a join, but, uh, where clauses, you do see them in there quite a bit.

Though, uh, I, I do see some people really go out of their way to, to screwball things by, uh, setting a local variable to something and then like joining on a, like case expression or an, or an or clause where if the local variable is one thing, you, you join to this other thing.

And if it’s one thing, the other thing, you join to this other thing. And you’re like, stop, you stop. This is not, not going well.

Uh, but many developers are under this strange impression that, uh, parameter sniffing is the worst thing in the world, right? They hear the words parameter sniffing and all of a sudden they, every, everyone has an opinion.

Everyone has a feeling, uh, deep in the gut. And all of a sudden they, they, they are the world’s foremost expert on something. It’s like when you say optimistic isolation level and someone’s like, well, I read a blog post that says it’s dirty read.

Like, I’m like, well, good for you. You’re wrong, but stop reading wrong blog posts, I guess. But, uh, the real problem that you aren’t into is parameter sensitivity. But, uh, local variables are often not a very satisfying fix for parameter sensitivity issues because while, while they do remove, uh, parameter sensitivity as a performance issue, you do, uh, end up with what is a sort of generalized, uh, cardinality estimate, which may not fit well for many of your data distributions, depending on, uh, if they skew high or if they skew low or something like that.

Uh, local variables use an estimate derived from the total, uh, rows in the table multiplied by the assumed uniqueness of a column’s data. And more often than not, that’s either a very small number or a very large number. So if you have the type of skewed data that led you to having these parameter sensitivity issues, that, that sort of fuzzy guess will either skew high or skew low, and you probably won’t get a plan that’s very good for one or the other to use anyway. You do remove parameter sensitivity as a performance issue because you are no longer using a parameter, but you are introducing another, uh, potential performance issue into the mix by doing so. Um, every, so every once in a while, this, this does fix something.

And if you want to test it to see if it, if it fixes something across a variety of executions and parameter, uh, uh, uh, parameters, parameter values with different distributions assigned to them, you can, you can absolutely do that. I don’t want to tell you never to do it. I just want you to tell you to test to make sure it’s a valid thing to do because more often than not, it’s the wrong thing to do.

So this is sort of the effect of local variables. Uh, what I’m going to do is, uh, clear up the plan cache because I want you to understand that there is no plan reuse happening here and the plan, plan reuse is not why we are seeing the same estimate over and over and over again. If I run this loop when I do have query plans enabled, so we don’t have to worry too much there, we are going to get our eight counts back.

And I want you to see that all eight of these counts return wildly different numbers, but when we go look at the query plans, we get the same estimate across all of the query plans. This will be the same number no matter what. This is not a lack of statistics. We have statistics on the table, uh, or rather on for this column, you can see SQL Server using them. If we go to optimizer stats usage, we will have a variety of statistics loaded in where a SQL Server was cardinality estimating for us, right? So SQL Server was, was using statistics. We are not in the statistics free zone here, but because we use a local variable, SQL Server performs the same calculation for every single one of these plans in order to determine, uh, the cardinality of, uh, this operation. And of course this is wrong for every single one of them.

We’re off by 380% there. Uh, we got 167 of 2857030. So 167 of 2.8 million. Uh, this, this particular, uh, density estimate density vector estimate skews high or somewhat high. But if you look through all of these, we just get a sort of poor guess for every single one of them. Even on the high side, we’re either wrong by 210 or 388%. So this isn’t, this isn’t a very good, uh, thing either.

Uh, this will be, uh, this would be the same thing if we were to use optimize for unknown, right? This would, this is like the same basic effect here. Now, if we throw option, like, like what I was talking about earlier, if we throw option recompile on this, we will get the same counts back. And you will see that SQL Server all of a sudden is capable of, of getting, of garner, garnering us, uh, precise cardinality estimates. Here we hit, well, we’re close enough to right there. We’re close, we’re, we’re dead on there. Uh, we’re good here, right? Well, I mean, we’re off by like what, 12 or something, 20, 20, 20, 20, something like that, some math. Uh, but like SQL Server is able to get very close to the number that we care about for all of these, right? So SQL Server does have good statistics to calculate that, but it doesn’t do that calculation. It doesn’t look at the histogram when you use, um, local variables or when you use optimize for unknown, it does not do that. You do not get, uh, a view of the histogram, uh, that the SQL Server has for a column. We just get the like two, uh, components of the histogram, the number of rows in the table and the, the assumed selectivity and those get multiplied together. Um, the percentage that SQL Server uses will be different across the quality predicates. So direct, you know, column equals something like we’re using here, uh, versus inequality predicates, like greater than, equal to, less than, equal to, or not equal to. You get all different stuff for those. Um, but, uh, sometimes, what happens is people use, um, people use local variables in a way where because of some other predicate, uh, it could be a parameter. It could be a literal value like, like we’re using here, but because there are other predicates in the, in the query, the effect of the local variable gets tamped down quite a bit. So if we look at these two queries and I’m, I’m forcing SQL Server to use the clustered index here. But what’s important to note is that the index that you use to fetch data from is not always the index or the statistics that you use to perform cardinality estimation for the query.

SQL Server may choose a completely different statistics object with a better sample, like a or something like that to, um, or a more, uh, more recent, uh, update, fewer modifications, something like that in order to, uh, do the cardinality estimation. So for these two queries, where in one of them, I’m using a local variable, the other one I’m using just a literal value, you’ll see that the cardinality estimates for these are not terribly far off, right? We get the same count result back, but when we look at the query plans, and if SSMS will listen to me, pretty please, pretty please, there we go. The cardinality estimates for these two queries, one using the local variable and one using the literal value, they’re not terribly far off, right? 15,282 versus 15,606. Was either one of them particularly close? Not very, but this is kind of what happens to a lot of people. Uh, they’ll, they’ll use a local variable in a spot where they have other parameters that, you know, sort of make the, uh, cardinality estimate look closer than it is. And maybe they don’t realize that there is a, there is a difference in how SQL Server estimates cardinality for these. So while there is a small difference here, it’s not so profound that it would like change much about the query plan in a way that like you would notice.

So, uh, when you want to test whether something you’re doing is okay, the hardest part really is, uh, understanding what options you have available to you that might, may produce different results. Uh, there are many ways to pet, see, pet, pet, uh, a dog. I don’t want to just say the thing with cats.

Many ways to pet a dog up here, behind the ears, near the, near the butt, not like on, like above the tail, above it. Stay, stay above the tail. Don’t go below the tail. Uh, if you go below the tail, I don’t know what’s wrong with you. Uh, but there are many ways to, um, to, uh, work with SQL Server to, uh, figure out if what you’re doing is the smartest thing that you could possibly be doing. Uh, there, but like I said, the hardest part is really knowing that what you’re doing, what you’re doing might not be a good idea and understanding what all of your options are in these cases. Um, a lot of this is why, uh, when you look at a SQL Server code base and you find a problem, you see that problem repeated everywhere. That’s how various things that just are like pervasive, uh, it was like issues in, with, with T-SQL code that I look at just ended up all across it, right? Code is culture. The second something yucky ends up in the culture and no one knows any better, or people are afraid to like do anything with it or do anything different or touch it or, you know, get away from it or say, hey, that’s not good. Uh, that, that one instance ends up everywhere, right? And that goes for a lot of things. That goes for like stuff like no lock hints, table variables, uh, CTE, scalar UDFs, multi-statement UDFs, and people wrapping like over protectively every single column and every join and where clause with like, is no coalesce, stuff like that. And like, there’s like, like, like undoing all this stuff is a long process, right? Cause like, it’s just absolutely everywhere. And like, especially if you’re like, especially if it’s, you know, the, in the UDF territory, uh, you have to like not only rewrite the UDFs in a lot of cases, but you also have to change the way that the queries call those UDFs because invoking a table based UDF is a lot different from the way you call a scalar UDF. And of course, local variables are on this list. So, uh, what I’m, what I want to show you here is, uh, sort of more explicitly how, uh, some of the stuff that I showed you can end up causing a performance issue. I’ve got a couple indexes created on the post table, one on the parent ID column and one on the owner user ID column. And we are going to create or alter this store procedure here. And then we are going to run it. And while it runs, I’m going to, I’m going to show you, uh, the different ways that things are going in here. Oh, you know what? I do, do, do, do have an extra index that I forgot to drop. So we are going to really quickly pretend that didn’t happen. Do, do, do, P2 on posts, because one thing that I was going to show you later was how compound indexes can help stuff a little bit, but for now we’re going to return to, uh, to Narnia here. So, uh, while this runs, we are going to have, uh, one, uh, expression of the query where we’re using two literal values. We have one expression of the query where we are using two parameter values, right? These are the parameters that got passed in up here. Uh, and then we have another one where, where we are using local variables that got assigned the parameter values that got passed in. That’s what we’re doing right here. All right. This is, and this is what I catch a lot of people doing when, with a little comment that’s like, fix the parameter sniffing boss. And then down here, we are using the optimize for unknown variant, which gets you the same, uh, cardinality estimation effect of using the local variables. And if we look at the execution plans for these, uh, this one turned out okay, all right, fine with this 817 milliseconds, fine with me. 815, 851 milliseconds, again, fine with me.

Uh, these are, this is for the literal values. This one is for the directly used parameter values. But down here, we have two very different query plans, right? Rather than using each of the non-clustered indexes that we, uh, we originally created individually, we have the p1 and the p0, uh, being used. Uh, this one, these two just do a more classic, uh, index seek plus key lookup here and here, right? You can see the little key lookup operator, and these take significantly longer.

These are about 11 seconds a pop. And this is specifically because SQL Server did a sort of a bad job of estimating what would be the best, uh, what would be the best set of stuff to use here. Um, so this is one of the, like, just an example of how, when you, uh, when you write with, like, when you use local variables, when you use optimize for unknown, you do not always end up with the best execution plans. So across all that, uh, it’s important for people to know how they can test code effectively. Uh, now not all code comes from a store procedure. You might be dealing with an environment where, uh, ORMs are in use and they are hopefully, uh, you, they’re hopefully passing in parameterized queries, right? One, one would hope anyway. Uh, so what I’m going to do is show you how you can test that code effectively. So if you’re troubleshooting a performance issue, you want to make sure you’re troubleshooting the correct performance issue. So I’ve got, uh, two queries.

Here, I’m going to show you one of them and, uh, I’m using my store procedure, SP quickie store. You can get this at my GitHub repo. Uh, and I’m searching to, for a couple of different query IDs. Uh, no particular reason for these, just that they are fairly good examples of what, uh, what, uh, an ORM query might look like in query store. And I’m just going to click on this top one. So when you click on this, you get the full query. And if we go and highlight this whole thing, except for that little question mark doodad down there, we can plop this right into our query editor window. Now, of course we can’t execute this as is all sorts of, we would get all sorts of errors. But what you might notice here is that we have what looks like the beginning of the store procedure. Now, one thing that, uh, SQL Server does allow you to do is create temporary store procedures so that you can have, uh, what is, I mean, just like effectively, uh, what you would get from like parameterized SP execute SQL, which is the way most, uh, ORMs work. And if we just say create or alter up here, and I’m just going to call this pound sign P just to keep it short. And all we have to do is come down here and stick an as right there. Now, uh, just to make life a little bit easier, let’s put these on new lines.

So when we assign values to them, it’ll be a bit simpler. And, uh, now we’re going to need another thing from the query plan, right? What’s that other thing that we’re going to need? Oops, that didn’t go well. There we go. The other thing we’re going to need from the query plan is the parameter values.

So if we come back to, uh, query, the query store results and we open up the query plan, there are two ways for you to go about this. You can either right click, oops, tool tip getting in the way. We can either right click on the select operator and we can go to the parameter list and we can see all the values that we care of all the compile values that we care about in here.

All right. But if there’s a bunch of them, it gets a little tedious copying and pasting back and forth. So what I usually do is show execution plan XML, bomb all the way to the very bottom and copy out this block. And once you’ve got this block copied out, you can get rid of the query plan.

All right. You can just, we can paste the, paste this up here. And now all we have to do is line up the, uh, parameter names with what we have down here. So offset equals zero and fetch equals 50. And end date equals 2014, blah, blah, blah, blah, blah. So we’re going to put that here and, uh, start date equals 2013, blah, blah, blah, blah, blah. And so we’re going to put this here and now we can create or alter this store procedure with, uh, those as the default values. And if we just say execute pound sign P, we will get a working store procedure that effectively mimics the way a parameterized ORM query would, uh, would run with, uh, with, uh, like with SP executes equal SP executes equal, and this are a completely the commensurate, uh, equal way of doing things. One thing that I do want to bring up though, is depending on the ORM you are using and, uh, how that ORM operates, you may need an additional piece of information.

So one thing that, uh, Quickie store returns way over here in the results are the context settings for your query. So any context setting that you see here, uh, would be on, O-N, in the, in the query. So if anything is not in here, you would have to, uh, you would have to, um, look at the ANSI settings and you would have to set that to off. These are generally the ones that you would care about. Uh, so this usually, and usually the one that ORMs have off that they may want to have on is a Rith abort.

So just be very careful with, uh, usually if you’re testing against ORM stuff, you want to turn a Rith abort off, uh, so that you get, uh, the same, uh, either cache hit or something for them. It’s not going to fix your parameter sniffing problem, but it will at least, you know, bring you to the query, like get, usually get you the query plan of the thing you are testing. So that is, uh, how you can effectively test code without falling into the, uh, local variable problem that we’ve been discussing for this last, wow, 30 minutes. Jeez Louise, got ahead of myself a bit there. Ah, anyway, uh, I should probably wrap this one up then before, before the camera starts to overheat.

Lord knows most people start to overheat if they look at me long enough. Uh, so thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. We, where we will talk about how you can use wrapper procedures. You can see that over there, uh, to alleviate some problems with, uh, things that we talked about. Like when you have a bevy of local variable assignments happening and you need to use those local variable assignments in other queries where cardinality estimation would matter. So, uh, I’m going to go prepare those demos, hopefully not, not, not forget any, not forget that I created any indexes and, uh, we’ll, we’ll go from there.

All right. 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.