IF Branching In SQL Server Stored Procedures: Fixing Parameters, Recompiling, And A Good Enough Plan
Thanks for watching!
Video Summary
In this video, I delve into the complexities of if branching within SQL Server stored procedures and explore various strategies to mitigate issues related to parameter sniffing. We discuss the differences between `WITH RECOMPILE` and `OPTION (RECOMPILE)`—how they can be used to address performance problems but also highlight their limitations. By sharing practical examples, I illustrate how these techniques might not always provide a comprehensive solution and may even introduce new challenges. Additionally, I touch on the importance of maintaining a balance between optimizing query plans and ensuring efficient execution, especially when dealing with null parameters or complex search patterns.
Full Transcript
Erik Darling, Darling Data. So many Ds, so little time. In this video, we’re going to continue on with talking about if branching. In the first video, we talked about some patterns and some problems that can arise when you use if branching in SQL Server, stored procedures. In this one, I want to talk a little bit more about sort of different ways I see people try to intervene with if branches in stored procedures. A little bit about the width recompile versus option recompile, and one potential solution to the problem of if branching in stored procedures. It may work for you, it may not work for you, but we’re going to talk about it, and we’ll let you make up your mind if that’s the right thing for you. Before we do that, of course, we need to talk about more about me, and me wanting to buy Christmas presents for my kids, or just keep recording videos so that you don’t have to pay exorbitant amounts of money to get training, which is probably nice too. You can sign up for a membership to this channel, and for as little as four American dollars a month, you can say thank you for all the hard work that I put into these things. If you don’t have 4x of dollars a month for whatever reason, I don’t know, maybe you owe credit card debt, maybe you spend too much money on clothes and booze and vacations, and I don’t know, maybe bad divorce settlement, I don’t know, whatever it might be.
I can’t tell you why you don’t have 4 bucks a month. I wish I could. 4 bucks. Pretty small amount of money, even on a monthly basis. You can do all sorts of things to say thank you, I love you. You can like the videos, you can comment on the videos, and you can subscribe to the channel and join the over 5,150, 60, I forget, data darlings who love this channel enough to get notifications when I post new things.
So that’s all cool. It’s all fine with me. If you are the type of person who has a SQL Server and needs help with that SQL Server, I am available for all sorts of consulting work. I do all of these things for a very reasonable rate, and I’m the best in the world at them. So if you’re thinking about hiring someone else, you’re screwing up. If you would like some very high quality, very low cost SQL Server performance tuning training, I’ve got over 24 hours of it.
You can get it for life for about 150 USD with that discount code right over yonder. So that’s a pretty good deal too. Now, I do enjoy speaking live to people, traveling, going places, talking to people, pressing the flesh as it were, but I don’t have anything right now. I finished up past data summit, and we’re getting towards the holidays, and I got nothing.
So if you’re planning events for 2025, and you want someone to come and talk about SQL Server performance tuning, I’m pretty good at it, just like consulting. And I don’t know. I tend to sell a lot of tickets if that’s any help. So we might be able to benefit each other this way.
But with all that out of the way, let’s talk a little bit more about this whole if branching thing. Now, if you watched the last video, or if you’ve been working with SQL Server long enough, or I don’t know, maybe you read a couple blog posts and now you’re an expert, you probably think parameters have a really, really bad name.
They get sniffed. They get snorted. They are sensitive. All sorts of bad things happen when parameters get involved, it seems like. But that’s not really true. Parameters are often a great thing.
They’re often wonderful things to help you have stable query plans and performance and not have to compile all the time. And not just, you know, I don’t know. Look, it’s usually a pretty good thing. I don’t understand why you’re freaking out.
Now, almost everyone who I work with has a bunch of store procedures, either written by them, someone else, that person who hasn’t worked there in 10 years that they’ve been meaning to fix. And they all do something to try to beat parameter sniffing.
They’ll use option optimized for unknown. They’ll declare variables inside of the store procedure and set those variables equal to the parameters. And some people will even have the gall to tell me that it’s a best practice.
They’re like, oh, we had some parameter sniffing, so we just turned off parameter sniffing, and we got a crappy plan for everybody, and it was better. Well, I refuse to accept that.
If you’re calling me to talk about performance problems, and we see a performance problem with a store procedure doing this, it’s not fixing the problem. It’s not. You have not fixed it.
You have disabled parameter sniffing. Anyway, it’s probably not a good thing. Anyway, one thing that I see a lot of people do is try to intervene inside of a store procedure and do something with it to right a wrong.
Now, I want you to notice that I’m saying if creation date is null, we’re going to set creation date equal to 2008-0101. We’re just going to give it a canary value so that we find something.
Up here in the store procedure definition, I’m even giving creation date a default value. The problem is that neither one of these things override an explicit passed-in value when the store procedure executes.
Right? So what I’m going to do is show you exactly what happens via this magic, and this magic, oh, this is a rather slow kind of magic.
This is that Sunday driver kind of magic. If you’re watching the little clock tick away over here by this armpit, you’ll see that we have been ticking away for almost 15 seconds.
Exactly 15 seconds on the nose. Not bad. I forget if I predicted that or not. Someone predicted that.
I don’t know who. But if we look at this store procedure, we’re going to see a very bad estimate here. A whole bunch of rows out of one row. Not a good estimate. And if we look at the properties of the select operator, we’re going to see our creation date has a parameter compiled value of null and a runtime value of 2008-0101.
So despite the fact that we had an if branch that intervened and changed our parameter value to something else, the store procedure still compiled and still did cardinality estimation for creation date being null. All right?
We did our best, but we were just like, hey, creation date, you are null, sucker. All right? And so we got a really bad cardinality estimate based on that. You can substitute this with like, well, I mean, it’s not a direct substitution.
It’s somewhat different. But imagine if you were the type of person who declares a bunch of local variables in your store procedure. Or maybe you are the type of person who declares a bunch of local variables and sets them equal to values and then uses those.
Or you’re the type of person who declares a local value and sets them equal to parameter values. You can end up with similarly bad performance because you are getting that awful density vector guess rather than an actual cardinality estimate based on the values that get passed in.
If we recompile this and we run this with the actual value 2008-0101 passed in, we get a much different execution plan that makes a lot more sense for this query executing, right? We get a good cardinality estimate.
SQL Server is able to figure a whole bunch more stuff out. And we get just generally better performance. Now, I do understand the sort of reticence to let SQL Server sniff parameters and do its thing with those because you can run into problems.
We’ll talk more about that in the next video. But one thing that seems to escape a lot of people is that making SQL Server use the density vector guess that you get from a local variable is not a direct fix for parameter sniffing.
You might get okay plans for some values. You might get bad plans for other values. But the thing with density vector guesses is that they assume sort of uniform data distributions in the table.
So if you have the type of wildly skewed data that leads to parameter sensitivity issues, then you assuming that the data is uniform is not going to fix that problem. It’s not going to get you what you want.
Now, there are all sorts of similarly bad search patterns that I see employed that attempt to, you know, do something like this, right? Essentially intervene with the optimizer and say, hey, if that’s not a thing, then do something else.
You might see people do isNull like this and say, hey, if that parameter is null, substitute this. Or you might even see people pass in the column over here too.
So rather than passing in a literal value, you might say, oh, well, if the parameter is null, then where it’s greater than or equal to the creation date column. Bad.
Same problem with coalesce. Same problem with doing this and saying the parameter, the column is greater than the parameter or the creation date is null or like, or the creation date is greater than or equal to creation date.
And especially noxious1 is using a case expression like this where the creation date column is greater than or equal to. Case when creation date is null, then p.creation date, lsp.creation date, right?
It’s like, there’s like a whole bunch of dumb things you can do in there. Now, one thing that I want to pause and talk about in a slight sort of digression about these, about sort of similar topics to this is the use of recompile.
There are two different ways to add recompile to a store procedure. And using recompile can solve parameter sensitivity issues because SQL Server, within the context of a store procedure, not an ad hoc query, within the context of a store procedure, if you create the store procedure like this with recompile, right, if you do this, SQL Server will not cache any plans for the store procedure at all.
It will evaluate parameters on each execution, right? So every time this runs, SQL Server will take whatever parameter values you pass in and do cardinality estimation based on those parameter values.
What this does not do is parameter embedding. And I’m going to show you an example of what I mean by that in a minute. If you use option recompile, it will only cache the most recent plan for any statements that you have the option recompile hint for.
It does not reuse those plans, but you will have the most recent version in the plan cache. Just like using with recompile at the store procedure level, it will evaluate parameters on each execution.
And option recompile also does allow for parameter embedding. So let’s just use like the example that we have above. And what I want to show you is what that means when you have a with recompile hint and you do something like this, okay?
Now this is the store procedure level with recompile, and we are, they have one parameter, and we are telling SQL Server to say where the column is greater than that parameter or the parameter is null.
Now before I run the store procedure, I want to show you that I do in fact have two indexes on this table that lead with the creation date column. So SQL Server should be perfectly capable of seeking into either one of those indexes to figure out, to find the data that we care about.
But when we look at the execution plan, we do use one of those indexes, but we don’t seek into it. We scan that index.
If we look, if we hover over that index, look what happens. We have a predicate instead of a seek predicate. So we have this residual predicate where SQL Server is like, well, it’s either it’s null or it’s not. Now that’s a crappy problem.
Note that we do get a good cardinality estimate from it, right? SQL Server does make a good accurate cardinality estimate from it. And if the tooltip will get out of my damn way, we’ll see in the parameter list, we have the compile and runtime value are exactly equivalent.
That’s because we used with recompile at the store procedure level. If we change that parameter value and re-execute, SQL Server will create this plan again, or rather we’ll compile this plan with whatever parameter value we pass in, but it won’t embed the parameter the way that it can with a statement level option recompile.
Let’s take a look at what that means, right? So now rather than having, like we took the with recompile out of this part of the store procedure and we stuck an option recompile hint at the very end, right?
So we’re going to, let’s do this and let’s execute this in the exact same way. Now we still get the same cardinality estimate, right? About 99%.
That’s right. But note that we don’t have an index scan anymore. Now we have an index seek. This is the difference between the store procedure level with recompile and the statement level option recompile. SQL Server is able to do parameter embedding with this one and change that or clause into a seek with the literal value that we pass in.
So there are some differences in how SQL Server treats option recompile versus the store procedure level with recompile. If the problem that you’re trying to solve for like an entire store procedure is just parameter sensitivity and you want SQL Server to come up with a cardinality estimate based on whatever parameter values you pass in, you can probably do that and get away with it.
If the problem that you’re having is a lack of parameter embedding because maybe you have catch-all store procedures that do stuff like this or repeat the search patterns that I showed you up here with is null, with coalesce, with a case expression, SQL Server does not do parameter embedding in those instances, right?
So the store procedure level with recompile won’t fix that stuff. It will fix cardinality estimation issues, but not parameter embedding issues.
So be very careful with how you use with recompile at the store procedure level versus how you use option recompile at the statement level. All right.
So one thing that you can do to get around to short circuit a lot of problems with either null parameters and like maybe fixing those with like say, hey, if this parameter is null, change it to this.
And maybe like another, you can also solve problems, a lot of performance problems with like compiling if branches with just like a regular query in there is to create store procedures that run to do the thing you want to do within those if branches, right?
So if you have like sort of a minimal amount of code and you’re not too worried about it, creating a store procedure that does the stuff in those if branches can be tremendously useful.
So let’s create a store procedure to do the post query. That’s going to be this one. And let’s create a store procedure to do the votes query. That’s going to be this one.
And now we’re going to create a wrapper store procedure that sort of runs and does something depending on what which post type ID or vote type ID is null or not.
Now, I realize that there’s a lot of like handling in here that should be done that’s not done just for the sake of brevity. I’m skipping over a lot of stuff that I would normally do if I was writing production level, production ready code.
This is just to make the example a little bit tidier. So this can be a pretty good way of not compiling a plan for an if branch unless that if branch is explored.
SQL Server will not compile a plan for a branch that doesn’t do a thing. The problem is that this does introduce parameter sniffing for each of those store procedures.
So if we were to run a little version of the store procedure, we would explore that if branch and we would do a thing in here. And then if we run a bigger version of the store procedure, right?
Like post type ID 8 doesn’t have nearly as many values as post type ID 2. Post type ID 8 is like a weird wiki thing that hardly anyone ever does. Post type ID 2 is questions.
So obviously there are a lot of, there are like 11 million questions in the post table. So reusing the plan from one to the other does not work out as well as we would hope. SQL Server, you know, we reuse the estimates for the first execution.
And we sort of have some problems with spills for the hash join. And this takes about 11 seconds rather than finishing very quickly like it does for post type ID 8 here, right? This finishes in just a couple of seconds overall, right?
So one and a half seconds there versus like 10, 11, 12 seconds for the other one. So you will still have parameter sensitivity potentially per store procedure that you have in there.
But at least you don’t have it sort of globally at the store procedure level when you just put queries into if branches. In the next video, we’re going to look at different ways of solving parameter sensitivity issues in here.
And actually, that might be it. I forget. We’ll, you know what? We’ll see when we get there. You have to watch it to see what’s in it. But I promise, it’ll be fun.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you over in the third video in this series, the final video in this series. We’ll talk about all that good stuff.
So thank you. 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.