A Little About Parameter Sensitivity And Startup Expression Predicates In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the world of parameter sensitivity in SQL Server stored procedures, focusing on a common coding pattern that can lead to suboptimal performance. Erik Darling walks through an example where a procedure uses startup expression predicates based on dynamic parameters, which can cause significant issues with query plans and execution times. I explain how these predicates can make your job easier but complicate SQL Server’s optimization process, leading to poor estimates and wasted resources. The video also covers the use of `OPTION (RECOMPILE)` hints as a potential solution, while emphasizing that this is not always the best approach. By sharing practical examples and insights, I aim to help you understand how to write more efficient stored procedures and avoid common pitfalls in your SQL Server coding practices.
Full Transcript
Erik Darling right here with you, cranking away, having a great time, trying to figure out where to put stuff so it’s less distracting than my desk. One of the most distracting things on my desk is the lens cap to my camera because it’s not quite a fidget spinner but it has this very pleasant squeezy motion about it so you can really get a pretty decent two-finger workout. on something there. I don’t know. I don’t really know what good that would do you. Picking up Legos. Pick up those Legos. In this video, we are going to talk about parameter sensitivity in the context of startup expression predicates. If you don’t know what those are, don’t worry. I’m going to answer that question. Relax. Calm the hell down. crazy. Alright. So this is a coding pattern that I see at a lot of client installations. I don’t know what the hell else to call them. Client sites. Like every client is just a website I visit. And they’ll, you know, coming back to my favorite SQL Server adage that I’ve ever said, is anything you do that makes your job easier, makes SQL Server’s job harder. And this is one of those cutesy, like, you know, got your nose optimizer things. Maybe that’s what this is. Got your nose. And this, this will just do you no good. So I see this pattern a lot where they’re like, oh, we’ll only join if this thing is true. Problem is, sometimes this thing will be true. Sometimes it’ll not be true.
And when you ask SQL Server to create query plans based on this type of logic, you will be quite unhappy in the long run. Things will not go well for you. So let’s make sure that we have the correct version of this procedure running. It happens, it happens a little too often to me where I forget to create the crappy original version of the procedure. And I go execute it and I’m like, wow, that was amazingly fast. Did the optimizer change? No, I just, I just didn’t overwrite my fixed version of the code. So we’re going to make sure the crappy version of the code, we’re going to make sure your version of the code is in there, not my version. My version’s good. Yours sucks. Stinks. Look at this thing. Ugh. Disgusting. Throw it in the trash.
And so we’re going to just, you know, because we are incredibly superstitious people, we are going to recompile this procedure. And what we’re going to do is execute this. Once to look for posts and once to look for comments. Now I realize, a lot of you are probably sitting in the back screaming, yelling, ugh, but what if you check for posts and comments? Or what if you check for neither? Or what if this, blah, blah, blah, blah, blah, blah, blah, blah, blah. Well, you know what? This is, at some point these demos have to fit on one screen. And at some point these demos have to fit in one video.
If I go through every single iteration and permutation possible of parameter combinations, not only are we going to be here for a long time, but no one’s going to watch it because it doesn’t get to the damn point. All right. So just keep that in mind that none of the demos I’m going to show you have proper handling for both and neither. Right. It’s either one or the other. Okay. That’s just what we’re dealing with in this one.
So here’s the execution plan. And what we have is the first one that finished relatively quickly because SQL Server cashed an execution plan for the first execution of this procedure where we were hitting the post table. All right. And everything turned out pretty okay for the post table. But if we look down here, we still have the comments table in the mix. All right. And if you look at what the estimates are for the comments table, we have all, well, I mean, it’s all one row.
All right. So this is not a very good, not a very good estimate at all for future executions of this store procedure. Now, again, you may be out there in the world and the void, the void beyond the screen screaming. You can just throw an option recompile hint on there and you absolutely can.
And if you do that, I’m not going to argue with you. It’s a fine choice. It’s totally okay with me. I am okay with option recompile. Do it. I don’t care. If it fixes things quickly for you, go for it. Right. Do it. Right.
What am I going to do? Spank you? That costs extra. All right. Family friendly SQL Server consultant. If you want that kind of action, big bucks. All right. So the second execution of this query takes about nine seconds.
All right. 8.7, which is close enough to nine. And the thing down here is that that bad, that one row estimate on the comments table from the first execution really hemmed us up bad there. All right. Because we got a lot more rows. We got a lot more than we bargained for in here.
All right. So this is probably a good point to tell you about startup expression predicates. All right. Because that’s what I said was going to make this thing parameter sensitive. And that is what’s happening here.
So in both of these query plans, you’ll notice we have these filter operators. All right. There’s one up here and there’s one down here. Usually when I see a filter operator, I get nervous.
Well, I mean, yeah. Usually when I see a filter operator, I get nervous. Unless I know that it’s there for a reason, like we have a having clause on some aggregate, or we have a windowing function where we’re filtering to, like, for example, row number equals one, where you need to generate that expression at runtime and then filter on it later.
Whenever I see a filter without one of those things, I get a little antsy, because that can often mean that we have written a predicate that is very complicated or very non-sargable, the opposite of sargable, anti-sargable.
And usually that means performance is going to suffer in some weird way. It can also mean that we’ve written a left join with some sort of null or not null check on the left join to table, in which case we should be using not exists or exists or whatever would logically fit the scheme of the query.
But in these cases, what we have here are these startup tooltip. You know, you show up when I don’t care about you and when I don’t need you, and then as soon as I need to show you, you disappear on me.
So in these filters, we have a startup expression predicate, which means that only rows that pass through this filter will touch the table, right? So if we can just keep hitting this filter with nothing, and eventually SQL Server will say, okay, well, nothing passed, so we’re not going to do anything with that table.
There should be no I.O. for the post table in this execution, because nothing passed this predicate, right? We had a whole bunch of rows try, but nothing did, right? They all failed.
None of them passed the predicate. For the comments section, where we have check comments equals one, well, a whole bunch of these do pass, way more pass than what happened before, and this is part of the cardinality estimation process.
SQL Server comes up with an execution plan based on these parameter values, and it says to you, well, comments score, sorry, if check comments is zero in this procedure, then I don’t expect any rows to pass that.
Sorry, you’re going to get a plan for that, and performance will inevitably suffer. So what a lot of people think is also a good and cute, well, cut your nose, that’s why we do our squeezy finger exercises, so we can be really good at grabbing the optimizer’s nose, right?
Boom. So what a lot of people also do is something that they think is clever and cute and will work like this, where they’ll separate these things out.
And again, I know, I don’t have a code blocking here that checks for both equals one or both equals zero. I get it. I don’t have any handling in here for those scenarios.
You’re just going to have to live with it. You’re going to have to take these two fingers and pinch whatever part of yourself helps you cope with the fact that I am not writing the most robust implementation of this stored procedure that I possibly could.
All right? Deal with that fact for a few more minutes. Thing is, this doesn’t help either.
Bare naked if blocks suffer a similar fate as the sort of startup expression predicate type code, where, and I’m just going to show you an estimated plan real quick. All right?
So SQL Server will compile and figure out a query plan for both of these branches on the first compilation, regardless of if both branches are explored or not. You get a fully-fledged execution plan based on whatever parameter values get passed in the first time, and you end up in a situation that’s quite a bit like the non-if block startup expression predicate portion.
All right? So if I execute these two things, we’re going to see a very similar outcome to the above query, where the one for the initial compilation for posts, totally fine.
The second run for comments, not so hot, right? It’s about 9, 10 seconds again. So if we look at these two query plans, almost identical outcomes just in two execution plans rather than one. All right?
We have 741 milliseconds here and 9 seconds here. So clearly the if block thing does not work so hot either. Now, again, if you want to throw a recompile hint on the first query, you can do that. If you want to do the if branch thing and throw a recompile hint on both queries, you can do that.
I don’t care. Whatever you want to do, roll with it. One way that you can solve this problem without recompile, you can even keep your if blocks, is to just use dynamic SQL parameterized, of course, so that we don’t get anything terrible happening to us.
And you don’t even have to, like, start declaring variables and all sorts of other things. I took an incredibly lazy way out of doing this. And I just, instead of setting a parameter to this, I’m just putting the whole string in there.
Screw it. If I’m going to make you angry with not having error and code checking for every potential combination of check comments and check posts, well, I’m just going to do this too.
All right? And if we create or alter our procedure, this is the one that would have run the first time and made me look somewhat foolish. But if we run these now, SQL Server is able to cache and reuse these execution plans over and over and over again.
And performance for the comments run no longer sucks because this is now treated like its own individual query. And whatever happened before doesn’t actually matter all that much. For the post run, this is just about as fast as it’s been for anything else.
Is there more that we could do here? Yes. Absolutely.
There are indexes we could add, tweaks we could make to the query. Sure. There’s probably lots of stuff we could do. There’s probably thousands of things that we could do. What they are, I can’t even possibly begin to enumerate the billions of alternatives we have for tuning this query.
It’s just an insane amount of, insane variety of things that we could do to fix this thing up. But that’s probably for another video. So in this video, we learned that anything that we do that makes our job easier, like writing shortcut code like this, makes SQL Server’s job harder.
We end up with startup expression predicates, and these startup expression predicates are based on the passed in parameter values. And if we do a big switcheroo and we sometimes join to one table and sometimes join to the other table, whatever table we compile the plan for first is going to be okay.
Whatever one we didn’t compile the plan for first is probably going to suck. If you want to throw an option recompile hint on this, you have my blessing. I will write a letter to your employer.
Say option recompile is okay for a small fee. If you want to do this, it will also not work without option recompile hints. You will have typed more.
You will have looked busy for another 30 seconds, but you will not have solved a problem because bare-naked if branches like this where you just execute raw queries in them do not actually separate, does not actually give you any sort of performance fencing.
The logical fencing is still there. The performance fencing is not. SQL Server compiles a plan for both of these on the first execution. If you want true separation, you can execute something else, like sp-execute SQL with dynamic SQL.
You could put each of those queries into a store procedure and execute that store procedure within that if block, and that would give you true separation. You could also add option recompiles to absolutely everything.
I won’t care. I like option recompile a little bit. It’s kind of a good thing. So, if you’re going to write this kind of code, you can always also hire me to fix it.
Thank you. I love you. I hope you enjoyed yourselves, as usual. You might be able to tell I enjoyed myself a little bit. I do hope you learned something, or maybe picked up on something new.
What else? Gosh almighty. So many things to say. So little time. Sometimes I just want to pull up a chair and talk to you all day.
If you like this video, thumbs-ups are nice. Comments are nice. Smiley faces. Emojis.
Lots of emojis. Just a crap ton of emojis. Totally cool. If you like this sort of… If you like SQL Server… I don’t know. This sort of content. If you like SQL Server content, because I have a wide variety of SQL Server content, most of it is performance tuning.
None of it is backups, like maintenance, high availability, disaster recovery, because what a snooze fest that stuff is.
It’s all pretty much like performance tuning, query writing, stuff like that. Subscribe to my channel. Like almost 4,000 other people have. Because it’s a nice thing to do.
For a handsome young consultant, so we can continue to afford getting haircuts to look good in these videos. We can’t have ugly consultants on video, can we? Bad idea.
Anyway. I’ve got things to do. I’m going to go do them. Before my wife yells at me. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.