IF Branching In SQL Server Stored Procedures: Patterns And Problems
Thanks for watching!
Video Summary
In this video, I dive into the complexities of if branching within SQL Server stored procedures and how it can significantly impact performance. Erik Darling from Darling Data shares his insights on why these constructs are problematic, especially when dealing with parameter sniffing issues. He highlights that while simple if branches might not cause immediate problems, they can multiply the effects of parameter sniffing across multiple queries, leading to suboptimal execution plans and slower query performance. By walking through various scenarios and providing detailed examples, Erik illustrates how these branching conditions can introduce unexpected cardinality estimations, making it crucial for developers to be aware of these pitfalls when designing stored procedures.
Full Transcript
Erik Darling here with Darling Data. Boy, we just full of energy today. I’m not sure where it came from. Maybe it’s the Lord’s energy. It’s hard to tell. I’ve never had that before, so it would be brand new to me if that were the case. But in today’s video, actually for the next three videos, we have a three-part series coming up about if branching in SQL Server stored procedures. And in today’s video, I’m going to teach you about the sort of problems, about the problems that come along with if branching in stored procedures.
It is definitely not the Lord’s work to do this sort of thing, unless you write your code very, very carefully. But before we get into that, I do have to make a few sales pitches because I guess I’m a used SQL Server car salesman and that’s what we’re going to do. If you like the work that I do and you want to support the work that I do with money instead of just with like watching the video for a few minutes and then going to do something else, you can click the link in the video description, the very first one next to the words like become a member or whatever.
And you can do that for as little as four bucks a month. You can join the 30 other people who are contributing to my nearly 119 pre-tax dollars monthly that I make from this channel. It’s amazing stuff. Life changing. If you would like to support this channel, but the four bucks a month is too much for you. You need that for your lotto tickets or whatever else. Infant for your formula, whatever.
You can do other stuff. You can like the videos. You can comment on the videos. And of course, you can subscribe to the channel. The subscription thing is a one time effort on your part, but it goes a long way towards you getting notifications along with over 5,150. Over 5,150 other data darlings out there in the world who all keep up to date on SQL Server stuff by subscribing.
Oh, I skipped one. Don’t want to skip this one. This is the important one because if you need help with SQL Server in any way, shape or form, I am the best consultant in the entire world. I do all of this stuff very well. And as always, my rates are reasonable. If, oh geez, this thing is real skippy today. I don’t know what’s going on with. PowerPoint is like rebelling against me.
Usually it’s Zoomit. Today it’s PowerPoint. If you would like some very high quality, very low cost training about SQL Server, all about SQL Server performance tuning, you can get mine for life for about 150 USD. Good luck beating that other Black Friday sales. I don’t know. Maybe they’ll probably beat me in other ways, but it’s a hell of a deal. Check it out.
Upcoming events. Yeah. PowerPoint’s on a weird lag today. All right. Well, we’re not fixing that. We may be able to performance tune SQL Server. We cannot performance tune PowerPoint. That is beyond beyond our can. Upcoming events. I got none. And quite frankly, I’m not doing none until after the new year.
But if you are hosting any events in the year 2025 that, you know, involve in any way, shape or form SQL Server stuff, and you would like me, Erik Darling, as a pre-con speaker to show up and do this sort of training there. I can do that. You just have to tell me where and when those events are so I can make arrangements to show up there. But I think that about does it. And we’re going to wait. We’re going to click and wait 15 seconds for PowerPoint to progress.
And now I’m going to say, let’s party. All right. So if branches and store procedures. Bad news for performance. Like really cause a lot of problems. The trouble, the thing that no one understands about this is what happens when you put if branches in a store procedure. And let’s say the store procedure looks something like this. And of course, these tables don’t exist.
That’s why there is a cadre of red squiggles in this code. But if you write your store procedure like this, the very first time that this store procedure compiles, and on any subsequent compilations after a reason for recompilation, right, like a plan gets cleared out of the cache, you restart SQL Server, the plan gets evicted from the cache, no memory pressure, any one of those settings changes, any one of those things that clears out the plan cache, any subsequent recompilations, SQL Server will compile both of the queries, compile query plans for both of the queries that you see in these if blocks, based on the first set of parameters that it sees. It is classic parameter sniffing behavior, not parameter sensitivity, not yet, but parameter sniffing behavior.
So if the first time we, we run this store procedure, we say we want to select the data from this table where parameter one equals this. Well, that might be okay for that first query. But if you run that query again, like without any, without a recompilation thing happening, then SQL Server is going to use the plan that it compiled for the second query, it’s going to reuse that plan, most likely with the parameter value for p2 as null.
And that can cause a lot of problems. I can cut, oh, geez, mouse, calm down, buddy. So, yeah, that’s, that’s essentially what happens in there. And you’ve really just multiplied your parameter sniffing or parameter sensitivity problems for each query that you put into an if branch like that. So, uh, the first time this runs, we say p1 equals 100 and p2 is null. And the second time, and the, you know, then, uh, future executions, we decide to start looking at the other table.
Uh, you’re going to get a lot of, you might get a really, really bad execution plan for the second query. Now, this is just a very, these are just two very simple queries to get you used to the idea of what happens when store procedures like this run, uh, in compile plans. We’re going to look at, uh, in this video, we’re going to look at times when that matters and when that doesn’t matter. Okay.
There’s really one time when it doesn’t matter and it’s, it’s probably pretty rare. So, uh, this is the one case where it would make absolutely no difference, uh, if you were doing if branching and let’s, let’s, so let’s create this procedure and let’s look at what it does. Now, the important thing here is that the only thing happening in this, in this procedure is we are using if logic to figure out which branch of this procedure to run.
There are no parameter values in any of these queries. We are not filtering on any parameters. We don’t have any, like, I mean, if there were literal values, it wouldn’t make a difference either, but we’re just doing straight joins. Um, there’s no parameters, like, uh, there’s no parameters to sniff. Basically we are out of parameters to sniff in this one. So for every time this runs, the only thing we’re going to get is the full join cardinality for any of these queries.
But it still functions the same way where SQL Server figures out join cardinality based on that initial compilation. Uh, but in this case, it just doesn’t matter because the, the, the cardinality estimation is just the full joins. Right. But let’s look at just an example. Let’s look, let’s look at an estimated plan for just one of these.
So you can see what I mean by all of the plans get, get a plan compiled when this executes. This is what it looks like to SQL Server. When the, when the store procedure compiles, you have the store procedure itself. This is where we set no count and exact abort on. Right. So that’s our T SQL on and off.
And then you have this conditional, right? This little question mark, what’s going to happen. We don’t know. Is it going to be you? Do you get picked? Do you get picked? Who gets picked? But SQL Server like, like pretty much like that, like comes up and compiles a plan for every single one of these joins.
When the store procedure compiles, right? When you actually execute it, this is what the cast plan will look like. It’s going to look exactly like this. And you’re not going to really know what, which one of these ran. And it’s going to probably be confusing and you’re not going to like it too much.
So that’s what the plan looks like to SQL Server when it, when it compiles something and when it caches something for this, for this store procedure over here. Right. Because we just have an if, and then a, like a, just a query that runs, this is what it’s going to look like. A somewhat more realistic example is something like this. And I don’t know, maybe this wouldn’t be realistic to you.
I’m going to cover a few different scenarios that kind of like this, where we have just a single date filter parameter. Right. And we’re going to use just about the same set of queries up there, except now each one of them has a table where we’re using that date filter as a parameter, as a, as a parameter, as that we’re using that parameter as a predicate across a bunch of these queries. Right. So if we create this thing and we run it, we’re going to see that not only do we like, not only do we have parameter sensitivity for one query, right?
Like if I run this for badges, this is, this is going to run quickly because we don’t find too many rows. But then if we run these two where we get, we’re going to search for badges again. But we’re going to go back a little bit further and then we’re going to search for comments and just go back a little bit further.
Now we hit a parameter sensitivity issue and we’re going to have a parameter sensitivity issue for every single query in the procedure because we are reusing the cardinality estimation for the sniffed value here. Right. So both of the top for both of these further executions, we’re going to get really bad cardinality estimates because this is the, this is the parameter sensitivity thing. We get a bad cardinality estimate here and that translates into a much slower plan all around.
And then we get a bad cardinality estimate here and that translates into a slower plan all around. This plan is different, but this was, this, this would have been the initially compiled plan for this one anyway. Like if we go and we look at the estimated plan for this with the date filter in there, like that first query, this is the serial plan for that.
Right. And then when we hit the comments table, we got, this is the parallel plan that we got for the print for the parameter value compiled as 2013 0101. And then we’re going to see the same thing basically repeat for the other queries. Now you can, you can see that when you get the actual plan, if we do this one again, this only takes a few seconds, but we run this and this finishes.
And then we look at the, we right click on the select operator and we go to the parameter list. The, for date filter, we have the compile value down here, right? 2013 1231 and then the runtime value of 2013 0101.
So SQL Server didn’t wait to compile this plan until like, until it saw like, until the if branch got explored. SQL Server compiled this plan. The first time the store procedure compiled everything, compiled all four plans and you can reuse cardinality, reuse the cardinality estimate for that there.
So reusing the same parameter across a bunch of queries, regardless if they touch different tables, now you have parameters sensitivity for everything. A really common thing that I see with, with these sorts of branches is like maybe doing different sets of joins. But if like, we’re going to have essentially the same problem with these two, where let’s create this and let’s run these three just like we did sort of last time.
And we get the first result back very quickly, right? We get the execution plan and we get the, the join cardinality of this, which is pretty, or rather the parameter cardinality for this, which is pretty close. And then for the next two, we get, I mean, we’re going to get slightly different, we’re going to get slightly different estimates because of the, of where the parameter get kind of gets hit.
But like for this one, we get rather, sorry, for this one, we get the same cardinality estimate of one, nine, seven, nine, seven for both. But for the parameter that goes back further in time, we get more rows out of it, right? So this one gets 21,000 rows.
This one gets 61,000 rows, but we, we stay with the initial estimate of one, nine, seven, nine, seven. This isn’t that big a deal. The difference isn’t tremendous, but you can, you can see where the initial cardinality estimation stuff gets reused across all these. Now, what some people will do to try and safeguard against this is create different parameters for each, each like different table they might filter on.
So this, here’s, here’s an example where that happens, but this is where things actually get a lot worse than, than sometimes reusing parameters. Because the, for like, we’re going to sort of run into the problem that we ran into with the first, with the first talked about with like sniffing nulls. So like this one, like the first time that we run this for badges, we get, you know, we’re again, this is going to, you know, compile all four plans at the same time.
So if we run this, this comes back really quickly and we get pretty good cardinality estimation for this one. This is fine, right? This is typical parameter sensitivity stuff.
Good cardinality estimate for the first one, probably a good query plan for the first one. And then awful after that. But now look what happens when we run this for comments and we use the C filter rather than the B filter. This is going to run a bit longer.
And this is, you know, this is another one of the issues that you run into when you just if branch your queries and do this sort of stuff. This one’s still running. I forget.
Okay, 12 seconds. Usually it runs for like 15, but close enough. But for this one, we get this plan where this is just a hopelessly bad estimate and a hopelessly bad query plan for this. And the reason for that is what I talked about early on where this plan compiled and sniffed a null for the compile value.
And then the runtime value was this. So the difference here, the difference here is like between other examples where I showed you like using reusing the same parameter where that gets you like like parameter sensitivity across a bunch of different if branches. This does two, but in a different way.
The sniff nulls are probably almost worse than the parameter than like sniffed actual parameter values. So like for this one where we sniffed a null SQL Server just guessed one row, but we got a lot more rows back than that. So if branching and store procedures.
Well, it may. This is one of those like easy for you hard for the optimizer things that I talk about a lot. If branching might be very easy for you to write in this way, but for the optimizer, it is a very, very tough situation.
And we’re going to talk in the next couple of videos about different ways of approaching this and fixing this and then even fixing problems with performance across multiple executions of these things. So stick around for those. As always, I hope you enjoyed yourselves.
I hope you learned something and I will see you in the next couple of videos where we will dig more into this topic. Alright, cool. 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.