IF Branching In SQL Server Stored Procedures: Dynamic SQL!

IF Branching In SQL Server Stored Procedures: Dynamic SQL!

---

Thanks for watching!

Video Summary

In this video, I delve into the world of parameter sensitivity issues within SQL Server stored procedures, particularly focusing on how if branches can lead to these problems. I explore three effective methods for mitigating these issues using dynamic SQL, which allows us to tailor query plans based on different parameters without relying on Enterprise Edition or SQL Server 2022’s new features. By demonstrating practical examples and providing the code necessary to implement these solutions, I aim to equip you with the tools needed to optimize your stored procedures for better performance. Whether you’re looking to improve query execution by dynamically adjusting plans based on parameter values or simply want to avoid sharing plans across different data volumes, this video offers valuable insights and actionable steps to achieve those goals.

Full Transcript

Erik Darling here with Darling Data, doing everything I can to save the free world. In this video, we’re going to… This is the third and final part of my videos on if branches. In the first one, we sort of defined the problem. In the second one, we talked about other ways people use if branches stupidly and incorrectly in store procedures. The differences between with recompile and option recompile and using substore procedures along with a wrapper store procedure to sort of fix some of the problems that you can run into with if branches. The problem that you can still run into when you use store procedures or anything with parameters, anything with if branches, is that you can end up with a bad parameter sensitivity problem. SQL Server does have a newish feature as of SQL Server 2022 called the parameter sensitive plan optimization thing. The problem that the problem with that is it’s quite limited. It’s only for equality predicates. You only get three plan variations. Sometimes SQL Server will pick the wrong parameter to give sensitivity training to. So there’s a lot of stuff that can get weird with that. In this video, we’re going to talk primarily about how to use dynamic SQL to fix parameter sensitivity problems. I’m going to give you three ways that I use pretty frequently to do that. Maybe you can use… Maybe you can do something with that. Maybe you can’t. I don’t know.

If you can’t. If you can’t. Well, let’s talk about… Let’s talk about if you can’t do it, maybe I can. If you like this channel. If you’re like, wow, that Erik Darling. I hit mute and just watch him talk. He’s handsome. He’s in great shape. He’s young. Good looking SQL Server consultant. Voted by BeerGut Magazine to be the youngest, handsomest SQL Server consultant in the world. I just want to pay four bucks a month to see him wobble around on my screen and smile and make faces and hand gestures. Well, you can do that for four bucks a month. It’s a pretty good deal. At least as far as I’m concerned. It’s like… I don’t know. I mean, granted, if you buy scratch tickets, you maybe stand a better chance of becoming fabulously rich and famous than if you watch my YouTube videos. So I don’t know. It’s up to you what you spend that four bucks a month on. But, you know, if you give it to me, I promise I’ll spend four bucks a month on scratch tickets. How about that? If you don’t want to give me four bucks a month for scratch tickets, you can like my videos, you can comment on my videos, and you can subscribe to my channel.

Right now, we’ve got about 30 people who do this, and they’ve got about 5,000 people who do that. So, I don’t know. A bit lopsided, but we’re working on it. YouTube doesn’t let you do this until you have a lot of that. So, I don’t know. I’ve got to play in ketchup, I guess. If you are in need of SQL Server Consulting, I do all of these things. And I do them very well. Again, BeerGut Magazine says I am the youngest, handsomest, not only best looking, but best all around SQL Server Consultant. So, if you need help with any of these things, hit me up and we can talk.

If you would like some very high quality, very low cost SQL Server training, and you’re like, wow, I’m looking around at all these Black Friday sales, and gosh, there’s still a lot of money. It’s still like two grand for any of this stuff. You can get all mine for 150 bucks for the rest of your life. Just head to that site, use that discount code, and boy howdy, you’ll be swimming in SQL Server knowledge that you never dreamed of. If you ever dreamed of SQL Server knowledge, I feel bad for you. That’s depressing.

Anyway, I got no upcoming events. I keep saying this. If you have anything for 2025 that you want a young and handsome SQL Server Consultant to show up to and wax philosophical about performance tuning, well, gosh darn it, I might be the fellow for you. With all that done and out of the way, let us continue our magical journey into if branching. Now, there’s a lot of green text here, and this green text is mostly an ode to my discovery of and my ever-increasing love affair with Dynamic SQL.

If you ever look at the analysis or procedures I write, like pressure detector, quickie store, log hunter, health parser, there’s probably another one out there. I forget. I don’t know. They’re all good. You should use them, too. There’s a lot of Dynamic SQL in those, and I love Dynamic SQL for a lot of reasons.

And as soon as I started to write not crappy Dynamic SQL, I found even more good reasons to use it. So, you know, that was great, too. But the problem that we’re going to talk about today is using Dynamic SQL to fix parameter sensitivity problems because you can absolutely do that.

You know, SQL Server does give you ways to not deal with getting the same plan for a query every single time and using that and having problems with that by using Dynamic SQL. It’s a wonderful thing. It just, what can’t it do?

It’s like coffee. Every time you read a new study about coffee, it’s like, it protects you for life. It makes you a better person. You’ll be happier and more charitable and loving and caring.

Like, wow, coffee does it all. So does Dynamic SQL. So let’s look at how we can use Dynamic SQL to solve parameter sensitivity issues. I’m going to give you three options for it.

Two I’m going to sort of explain to you and show you the code for. One of them I’m going to show you, I guess, query plans for, too. But they all do roughly the same thing, just in slightly different ways. So if we look at the votes table and we look at the distribution of vote type IDs, you’ll see that some of these vote type IDs have way more values than others.

If you wanted to separate this into buckets, like sort of the way the parameter sensitive plan optimization does, we have this bucket up here for 37 million. We have these buckets down in here for like, let’s include that one, too, for like about a million to about 4 million. And then we have these buckets down here for low numbers up into like the low hundred thousands.

And what I’ve done in this store procedure, I’m going to just minimize that for a moment so you can bask in the glory of my fantastic Dynamic SQL. Is, of course, with Dynamic SQL, we must use the unicodes. So I have initialized three unicode things in here.

Now, you notice that I do have a thing for post parameters, but just for the sake of brevity, I’m cutting out the post portion of this so we can focus on the votes table vote type ID portion of this. So don’t worry about that, right? It’s okay if you declare a variable and then don’t use it.

It’s not like terribly wasteful because we’re just assigning this string to it. We’re not doing some big dastardly subquery to assign values to it. So stop it.

Stop yelling at me. God, I can hear you from here. And then down here, this is where we begin our Dynamic SQL. This is just sort of a remnant from when this was split into running two different queries. But things that I like to do with my Dynamic SQL, I like to format it nicely and make sure everything is sort of, you know, on the line.

So, you know, I got like, you know, this part sort of indented over to here and I got a little a little leftover space down here where I can plop this thing onto. And one thing that I think is tremendously important to do with Dynamic SQL is to put a comment in it with the name of the module that owns the Dynamic SQL. So if someone finds this looking through query store or the plan cache, they can say, oh, this belongs to this store procedure.

This is not just some random piece of ad hoc SQL that I might or might not care about. Okay. So that’s one good thing.

Now, I’m just going to scroll down a little bit and I’m going to bring our results back up here. So let’s just say that if our vote type ID is one of these, we want to change our query slightly and we want to add this string to it. And then if our query down here, say, is in one of these, that’s 16 through 9, then we are going to tack this onto the end of our query.

And if our vote type ID is down here in one of these, we are going to tack this thing onto our query. Right. So adding any one of these three dummy additional ands to our query actually does generate different query plans because SQL Server is like, oh, it’s a different query.

Look, this one wants to know if one equals select one. This one wants to know if two equals select two. And this one wants to know if three equals select three.

It’s a wonderful thing. So it’s a fantastic thing that you can do to almost do the same thing as SQL Server’s parameter sensitive plan optimization without having to be on Enterprise Edition or SQL Server 2022 or Compat Level 160. You can hand roll this thing.

It’s wonderful. All you have to do is be as smart as me or maybe hire me to do this sort of thing for you. Either option. Pretty good.

My rates are reasonable. It’s Christmas. Darling children need presents for Christmas. So we’ve got this store procedure here. And just so you can see the different results, I’m going to print out the SQL there.

And then I’m going to execute our dynamic SQL. This is probably the third and one of the most important parts of writing good dynamic SQL is to parameterize your dynamic SQL. There should be no plus signs concatenating strings into your dynamic SQL.

And you should use sp execute SQL with passed in parameter values. If you don’t do that, you are a foolish idiot and you deserve all the SQL injection that you get. And I don’t feel bad saying that.

So with this done, what we’re going to do is run this version of the store procedure. We have query plans turned on. So we’re going to run this for vote type ID 7.

And if we look over in the messages tab, this will be the query that ran. Look how nicely formatted that is. And this is where we got the 3 equals select 3 version of the query. The query plan that we got for it looks like this.

Simple, elegant, efficient, parallel, nested loops. Ooh, beautiful. I don’t know what that accent that is. I apologize if I offended anyone’s nationality.

And then if we run this version of the store procedure, let’s just go to the messages tab. That’ll print out eventually, I suppose. Do-do-do.

Do-do-do-do. There we go. So vote type ID 2 is a little bit of an outlier because this one has a lot of votes to it. This one always takes a little bit longer.

But if we go to the messages tab, this did indeed get 1 equals select 1 and does get a very different query plan from the first one. If I were going to be a little bit more fair and not say get the 37 million vote 1, oops, we could do vote type ID.

And I keep hitting, oh, I hit insert. No wonder everything in my life was going wrong. And we do vote type ID 1. This should run a little bit quicker because we’re not getting 37 million rows. We’re getting 3.7 million rows.

So this one does run a little bit better. And, you know, SQL Server did misestimate some stuff for vote type ID 2. But that’s okay. We got a little bit of spilling.

Oh, I still have insert turned on. Shame on me. But, you know, there are things that we could do to fix performance generally after that. Right? We could certainly do things that would make even the vote type ID 2 version of the query more efficient.

But the big point is that we are not sharing query plans anymore between small amounts of data and large amounts of data. We are sharing plans for large amounts of data and medium amounts of data and small amounts of data.

Those will all get shared. But for the big amounts of big, small, medium, and large, we are no longer sharing plans across those things. Right? So we might still have some performance problems that we need to fix for large amounts of data. But that’s a totally different thing that we can totally fix in other ways.

Right? That’s all sorts of other options we have for that. I’m going to show you two other things that I’ve employed over the years that have done sort of similar things.

I’ve used them situationally. Obviously, not every situation has this obvious of a fix for it. I think one very specific problem that you might run into is date ranges.

So one thing that I’ve had to fix quite a bit using Dynamic SQL is to say, hey, if the date range is recent, right? Like, let’s say it’s either an open-ended where start date is greater than, or it’s a closed-ended with like a start date and an end date.

Sometimes you want to look at like whatever parameter value someone passed in and be like, hey, if this is like today, do the one equals select one. If this is like a year ago, do the two equals select two.

If this is 10 years ago, do the three equals select three. So like, or like, you know, you can apply that to open-ended with like I just described or closed-ended where you’re like, hey, if this is like a week, one equals select one.

A year, two equals select two. Five years, three equals select three. You can do stuff like that to figure out whether the, you know, the start date open-ended or the start date end date closed-ended ranges are like, like what breadth of data they’re going to have to touch.

But there are other ways to do that. Like you can, like one thing that I have done at times is like, let’s say that like going back to the, the data distributions where there’s a large amount of data, right?

So two, one, three, five, 10, six. Those are the ones that had the most data assigned to them. Let’s say that even sharing a plan within those is kind of annoying.

So what we can do is say if vote type ID is in one of those big things, then we’re just going to stick an option recompile hint on the end of the query. If it’s not one of the big things, then we’re not going to stick an option recompile hint on there.

So what that means is that a SQL Server will run this query with, and like use recompile to do parameter embedding, like I talked about in the second video, come up with a query plan specific for the value that’s in there and do that.

Option recompile, depending on the frequency of execution, the plan complexity can be an unattractive solution at times. If it is to you and you don’t want to do this, there’s another really neat thing you can do that requires a little bit of string substitution.

That’s what I’m going to show you next. So rather than say optimize, rather than like have an optional option recompile on there, one thing, one other thing you can do is say option optimize for, and you can use like a weird like string that will never actually get used in the data.

In this case, it’s just open bracket, close bracket, and three at signs. Right? And so we’re going to say, like we couldn’t, we can’t put a parameter or a variable in there. And because I hate string substitute, and because I hate doing string concatenation stuff, I want something that I can easily identify and replace in that string.

So what I’m going to do is every time this runs, I’m going to run the replace command, and I’m going to replace that little canary string with whatever the vote type ID is. This is cool because this will get you plan reuse for each different vote type ID.

This will get you a different query plan for each vote type ID. This has upsides and downsides. And the upside for me is that there are like 16 vote type IDs that we could possibly have.

Right? Which, you know, I mean, sure, we have 16 plans. So what?

16 plans is nothing in the grand scheme of things. If you need to do this for like a lot of different values, then I wouldn’t recommend that. I would recommend this for things with a sort of known quantity, known small quantity of values.

But if, you know, if you are completely out of options, maybe the optional option recompile doesn’t, won’t work for you for whatever reason. Maybe the, you know, the one equals select one, two equals select two, three equals select three thing where you can bucket things a little bit better.

Or rather, you can bucket things in a way that you can’t do with this. If that doesn’t work for you for whatever plan sharing reason within those buckets, or maybe just fix your buckets.

I don’t know. We could talk about this all day. But this can be a pretty good solution if you have a small number of known values with a high skew between them, which the vote type ID column in the vote table certainly does.

Remember like 37 million, then like 3.7 million to a million, and then like 800,000 to like two or something. Those are like pretty good buckets to work in.

But like, let’s just, you know, for the sake of argument, say that sharing plans for the big buckets, medium buckets, and small buckets, like even within those groups, doesn’t go well. For a small number of values, we can also optimize for a specific value in there.

So those are the three different ways that I’ve, oh, why are you all the way over there? Those are the three different ways that I’ve used dynamic SQL to fix parameter sensitivity problems.

And the reason why this is included in the if branches is because how did we do this? We used if branches to decide what we were going to do in a correct way with dynamic SQL to make sure that not only do we not compile every plan in a store procedure, but we also compile a good plan for the parameter values that we pass in.

This is a great use of if branching. So with that done, thank you for watching. Hope you enjoyed yourselves. I hope you learned something and I will see you in a future video.

I’ve got kind of a fun one about lock time out coming up. So we’re going to, we’re going to have that. We’re going to, you have that to look forward to. This video should be dropping on a Friday. So if you’re, if you, if you’re watching this on a Friday, have a great weekend.

It’s all I ask of you is have a great weekend. Anyway, 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.