A Little About Local Variables In SQL Server
Video Summary
In this video, I delve into the nuances of local variables in SQL Server and how they differ from parameters and literal values. I explore why using a local variable to fix parameter sniffing is not only ineffective but also counterproductive, as it leads to poor cardinality estimates and suboptimal query plans. Through practical examples, including dynamic SQL and stored procedures, I demonstrate that relying on local variables can exacerbate the very problem they are meant to solve. I also discuss alternative strategies, such as using recompile hints or parameterized dynamic SQL, which can help mitigate issues with parameter sniffing while maintaining performance and accuracy.
Full Transcript
Erik Darling here with Darling Data. And in this video, we are going to talk about how local variables are different and how they work in SQL Server. What are they different from? Well, parameters for one, literal values for another, things like that. And if you want maximum detail, on this post, I would suggest you go here. This is a post on my site. You can follow the trustworthy bit.ly link here. That’s bit.ly slash uppercase L, local, uppercase V, variables. If you go to this URL, but you neglect to put the proper capitalization in, you will end up at a place that I cannot voucher verify for. I have no idea what’s going to show up on your screen. anything .rtf, disappearance of here. A sla as their lib ucont that you get is on the leading key column of the index.
There are no 3D or 4D chess statistics objects in SQL Server, for better or for worse. Maybe someday we’ll get vectorized statistics objects or something.
But when we create indexes and the statistics that get created alongside them show up magically in our databases, in our statistics views in our databases, this is roundabout the data that SQL Server stores about what is in our statistics.
You have some basic information up here. You have some density information down here. And then you have the histogram down here, which has 1, 2, 3, 4, 5 columns in it that helps SQL Server make cardinality estimates based on what you choose to filter on.
The one that I want to filter on here, or the one that I want to call attention to, is for range high key 2, 2, 6, 5, 6, which has 27,901 equality rows in it.
And SQL Server knows it. SQL Server can tell that from the statistics objects. So if we do an equality search on 2, 2, 6, 5, 6, we should be able to get a correct guess for the number of rows that exist for it.
But if we do something like this, let’s turn on query plans too. We might need those down the line. And we get the correct number of records back. We declare a local variable.
It’s an integer. We filter to that integer. And we look at the execution plan. SQL Server had no idea what the hell was going on. Guess 12. 12 rows.
That’s not a very good guess compared to 27,901. Might make for some interesting plan choices if we were joining this off to other tables and consider this to be part of some critical piece of code. Now, we get that guess.
If we look at the statistics, we get that guess from looking at the all density portion of the statistics object and multiplying that by the number of rows in the table.
So if we take those two numbers here and we look at what comes out, we get 11.9 some number of digits in SQL Server rounds that up to 12.
So it guesses 12 rows come out based on that density vector guess. Density is calculated by looking at the distinct number of rows assumed for that column in the table.
So this number here, I promise you, is equivalent to the all density number in the statistics object that we just looked at.
Now, I still hear every time when I look at client code that they used a local variable to fix parameter sniffing. However, I’m not sure in most cases how that can be a valid use case.
It’s a valid use case about once every four or five years. I’m not sure how that can be a generally valid use case considering the guess is so bad that you get that guess for everyone.
You get 12 for everybody no matter what. It’s a uniform guess. If you truly have a parameter sniffing problem because some rows in the table have, some rows are responsible for millions of entries in your table and some rows are responsible for thousands or hundreds of rows in your table and you’re getting bad plan choices because of that, it’s hard for me to imagine how you fixed parameter sniffing when everyone’s getting that crappy guess.
It’s just not sane nor rational to me that anyone still believes that when they say it out loud. I don’t know how anyone says that with a straight face.
I don’t know how anyone says that with any face, really. Anyway, if we look at, let’s take a store procedure where store procedures are usually what people are afraid of, getting whacked with that old parameter sniffing problem.
We’re going to run this query in three different ways. Now, inside our store procedure, and this is usually what, I’ll see something close to this, we’re passing in a parameter called ID, and inside the store procedure, we have a local variable called ID fix.
Now, let’s just pretend once upon a when someone passed in some bad data to the store procedure, it got a bad execution plan, and then things went all haywire.
So we create a local variable and we say if the ID is less than zero, then we set it to one, else we use the ID. And we’re going to use this in three different ways. We’re going to do this once using dynamic SQL, but we’re going to pass the ID fix local variable to dynamic SQL.
The trick here is that the dynamic SQL is parameterized so that we will magically, through alchemy, change our local variable into a parameter.
And I disagree with this capitalization here. I don’t know where that came from. I’m terribly embarrassed. I don’t know where that came from.
Aaron Bertrand’s going to, I don’t know, find some way to kill me. I don’t think he owns guns. But we’re also going to look at the results of this query, which is using the ID parameter rather than the local variable.
And then finally, we’re going to use this one where we use the local variable outside of dynamic SQL. And so we already have this created. Now, all we have to do is execute this store procedure.
We get the correct number of rows back for each and every one of these queries. But of these, only this one gets a correct guess of 27,901.
This one gets a correct guess of 27,901. And this one, of course, gets the crappy local variable guess of 12.
So again, using a local variable does not fix parameter sniffing. It merely changes the cardinality estimation model used when SQL Server goes and does all that fancy relational algebra of figuring out how tables get assembled using various letters in the Greek alphabet.
So as much as possible, I do urge you to avoid using local variables like this. If you’re dead set on using local variables for anything, I would suggest if the code is not frequently called, throwing a recompile hint on there.
If the code is frequently called, and you need to figure out some local variable values, please either pass those local variables to parameterized dynamic SQL or to a store procedure, which can, again, through the magic and wonder of alchemy, convert them over to parameter values.
And we don’t have to worry about getting crappy guesses over and over again. Now, this isn’t to trivialize issues with parameter sniffing. I do talk a lot about that and how to fix that in various ways we can deal with it.
Aside from Microsoft SQL Server 2022’s parameter-sensitive plan optimization, which is, I don’t know. You know when Goldilocks shows up to the three bears’ house and Mama Bear’s porridge is cold and Mama Bear’s bed is too squishy and I sort of feel like that’s what we got out of the box with that.
Hopefully some improvements get made there, but I do wonder. And speaking of the parameter-sensitive plan optimization, if we look at the text for these, we will see that that optimization did kick in for these first two queries, which used a parameter value, but not for this third query, which used a local variable.
So using local variables breaks the parameter-sensitive plan optimization, at least as of today’s recording. Anyway, that’s enough about this. I’ve said too much already.
I don’t know. Thank you for watching. Hope you enjoyed yourselves. Hope you learned something. I do hope you’ll choose to like and subscribe and stick around a little bit.
And, you know, maybe learn a thing or two from an old man about SQL Server. An old man yells at SQL Server. 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.