A Little About Parameter Sensitivity In SQL Server
Video Summary
In this video, I delve into two key SQL Server scenarios that highlight the importance of avoiding overly clever solutions and understanding parameter-sensitive plan optimization in SQL Server 2022. First, we examine a situation where attempting to outsmart the optimizer can lead to suboptimal performance. Specifically, I discuss how using bit parameters in stored procedures can backfire, causing SQL Server to generate plans that are not as efficient as they could be. This leads us into an exploration of why parameter-sensitive plan optimization doesn’t always come to the rescue, especially when dealing with startup expression predicates that lack histogram support. By rewriting a procedure using dynamic SQL, I demonstrate how making life harder for yourself can actually simplify things for SQL Server, resulting in more efficient query plans. Throughout the video, I also share some light-hearted moments, like my new microphone pop filter, to keep things engaging and relatable.
Full Transcript
Erik Darling here, Darling Data, and recording a rather quick one to talk about, well, a couple things. One is a situation where you shouldn’t get cute with the optimizer. And two is a situation where SQL Server 2022’s parameter-sensitive plan optimization will not come to your rescue. Now, there’s a good reason for that, and we’ll talk about it. Now, if you’re a loyal subscriber to my channel, you’ll notice something brand new in today’s video. This little pop filter right here. Yeah? Apparently, this little microphone came with a set of three in case you get hungry and want to nibble because it’s right near your mouth. And I just sort of forgot about them. So now, here’s wishing you a pop-free experience listening to me drone on about SQL Server. Sorry. So we’re going to start off with the indexes. We got two of them. One called BudLight and one called CoorsLight. This is not a taste great, less filling situation. This is just two indexes that make good company. So, let’s focus in on the right window. That’s a good start to the video, right?
So we got these two indices here. By the way, if anyone says indices, I will punch you in and sue you at the same time. One of them is on the post table. And it’s got owner, user ID, and score in the key of the index. No included columns here. And the other one is on the comments table, which has user ID and score. Now, it’s a little strange to have in the stack overflow schema in the post table. It’s called owner user ID. And in the comments table and the votes table, it’s user ID. But we’re actually in the badges table, too. So that’s a little weird. But we’re just going to run with it for now. Owner user ID. Now, those two indexes are here to help our store procedure. So I’m going to talk through how they help and why they could be more helpful. And then kind of walk through what else the store procedure does.
So what we have are two potential left joins. I say potential because you’ll notice it is these things here, which we’re going to come back to. But the indexes are here to try and help us make this faster. Now, is this perfect? No. In a perfect world, I might put score first, depending. You know, the score column is not terribly selective. So range predicates can still acquire vast swaths of data.
So maybe not maybe not the greatest idea there. But, you know, one of those things you kind of have to A, B test a little bit, see where you see where it gets you. Now, coming back to the store procedure itself, we have two bit parameters. We don’t mean that they are two bit parameters. I mean, we have two parameters that are bits.
One of them is called check posts. The other one is called check comments. Now, what these do is direct SQL Server to maybe do something or maybe not do something when we get to these joins. All right. So having these parameters in here, well, they’re just like any other parameter.
They are sniffable. And just like any other parameter, SQL Server caches a plan for the first compiled value and then reuses that plan. But we can tell SQL Server whether to actually do a join with parameters like this.
So I’m going to run this. And the first iteration is going to be to check the post table. And then the second one is going to be to check the comments table.
Now, on the first run, I have query plans turned on. I don’t have to worry about that. This runs pretty quickly.
All right. 640 milliseconds. Okie dokie. No, it’s OK. It’s not great. It’s OK. But this is one of those things I see developers do quite a bit.
They try to get cute with SQL Server’s cost-based optimizer. And what happens is that they sort of end up falling flat on their faces. It’s going to come back to my copyrighted adage about SQL Server.
And that is, anything that you do that makes your life easier makes SQL Server’s life harder. And this is one of those things. So let’s slide this plans DMs over just a little bit.
So I don’t lose my finger in a tragic green screen accident. And let’s first look at this part of the query plan. And let’s nuzzle that.
Ah, it’s in my ear. Let’s nuzzle that right here. We’ve got an index scan that doesn’t actually do anything. And we’ve got a filter operator right after that. Now, usually, when you see a filter operator after some sort of index or table access, it means you did something real dumb.
In this case, SQL Server did something pretty smart. So let’s look at the details of the filter. We’ve got something called a startup expression predicate.
Meaning this filter, rather than acting as a filter after the fact, it acts as a filter before the fact. So this filter actually prevents touching that table because the startup expression predicate, right, to like move past the filter is not met, right?
So we did not check the comments table in this one. For the post table, it’s a different story, of course, because we’ve got, believe it or not, another startup expression predicate.
But this one is met, so we do actually touch the post table, right? So la-di-da there. Now, what happens if we run this in reverse?
Let’s execute this. Or not in reverse. Let’s just run this one next. That’s sensible, right? All of a sudden, this takes a little bit longer.
The last one took about 640 milliseconds. This one takes nearly four seconds to finish. 3.8 seconds as the crow flies, as they say in my country. And if we look back way…
No, not you, tooltip. Not you either. Go away. You’re not my friend. Let’s slide this back over. And you know what? I think that’s probably good enough.
Now, this time, the startup expression predicate in the filter operator is met. But guess what? We retained the cardinality estimation from the first query, right?
SQL Server is still guessing that only one row is going to come out of there, but we actually get like 4 million rows. 401, 647.
That’s a seven-digit number. Seven numbers means millions. So, the reason why the parameter-sensitive plan optimization does not work here is because the mechanic behind that is when you run a query that’s parameterized and SQL Server looks at the histogram for various equality predicates and decides that one of them has skewed data in it.
It will mark the query plan. It will give it… It will get that multiple plan operator. And SQL Server will decide to give you up to three variations of an execution plan for your query.
There’s like a small, medium, and large. There’s no extra small or extra large. I’m not sure what that says about Microsoft. It’s not very welcoming. You could use some more plan variations, maybe.
Anyway, the reason why this doesn’t work is because there is no histogram for this startup expression predicate. It is either yes or it is no.
There is no bucketized values that this thing could possibly work off of to figure out when you need different execution plans. Maybe it could in the future because this is a, you know, I think this is an addressable case with the parameter-sensitive plan optimization.
I can understand why it’s not in V1, but I do think this is something that could be addressed by it just because it is a, I think, common enough issue and it is a probably simple enough issue.
But what do I know? I’m just a bouncer. So obviously, that didn’t work out so well. Now, coming back to my copyrighted adage about SQL Server, anything that you do that makes your life easier, makes SQL Server’s job harder, we took the easy way out.
And now SQL Server’s job is harder because it has to come up with one execution plan that’s fast for both being true or either one being true. Now, I rewrote the store procedure in a way that made my life harder, but makes SQL Server’s life easier.
And I used dynamic SQL. So just a fair warning here. I did not write this to take care of the both use cases just because we’re not testing that.
So, and for the sake of brevity, I am only writing this to address one or the other, not both combined.
Now, because of that, I had to change the queries a little bit. I can’t do the isNull on p.creationDate and c.creationDate because we have no p and no c depending on which one of these executes.
So we cannot bind a value that does not exist. All right, because up here in the original query, we’re doing this. All right, and I just chose sysUTc date time because, I don’t know, it just popped up.
Honestly, it was autocomplete. Thanks, Redgate. Just got to work that way. Anyway, let’s change our procedure to use this lovely dynamic SQL that we have done all the right things for.
And, you know, it is a bit longer. All right, because now we have to check if check comment equals one. We could do this a little bit differently, I suppose.
We could make the string itself more dynamic and like throw a case expression in here. So if like check post equals one, then we’ll insert this. And if check comment equals one, then we’ll insert this.
But no, I just decided to do this because I didn’t feel like doing it. I decided to make my life a little bit easier in this case. So now let’s execute this first one and look for check posts.
And this returns again pretty quickly. It’s actually got about 50 milliseconds faster, just not dealing with all the other stuff, right?
Not dealing with the maybes of the check comments thing. And that’s cool with me. I’m into that.
I mean, I’m not going to brag to anyone about making something 50 milliseconds faster unless it was running for 51 milliseconds. But it did improve slightly. Not a very dramatic narrative there.
But now if we run this second one where we only check comments, it will be just about as fast at 610 milliseconds. But it was much, much faster than before when it took four seconds to run because we had all of these startup expression stuff in the parameters sniffed startup expressions and the sort of poor estimates that come from that.
Anyway, I have to get out of here and go do some actual work. Thanks for watching. Hope you enjoyed yourselves. Hope you learned something.
And I will see you in some video in the future. If you’re the type of person who celebrates Fridays and you’re watching this on a Friday, then happy Friday or happy Friday if you’re from a part of the country that pronounces it Friday or part of the world.
Actually, probably nowhere else in the world is going to say Friday. That’s an American thing. I don’t know. If you’re from somewhere outside of America and you call it Friday, well, you can leave a comment for me.
I’d love to hear from you. Anyway, smell you later. Stink bombs.
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.