OPTIMIZE FOR UNKNOWN vs. OPTIMIZE FOR VALUES In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the pitfalls of using `OPTIMIZE FOR UNKNOWN` in SQL Server stored procedures and why it can often lead to suboptimal query performance. I explore how parameter sniffing issues arise when you optimize for a specific value versus optimizing for unknown values, highlighting that while `OPTIMIZE FOR UNKNOWN` might seem like an easy fix, it frequently results in less-than-ideal execution plans. Instead, I advocate for optimizing stored procedures with specific parameter values to achieve more stable and efficient query performance, even if it’s not perfect across all scenarios.
Full Transcript
Erik Darling here, president and CEO of Darling Data Enterprises. And in today’s video, we’re going to talk about Optimize 4. Why? Well, because I have kind of a funny angle on it. And it’s not that Optimize for Unknown is good. Optimize for Unknown is dumb and it stinks and everyone who I see use it because, uh, perimeter sniffing. I just wish that I had, I wish that there were like a zoom feature for me to send a boxing glove on a spring. out of their computer somewhere and just whack them. Um, it is, um, it is, it is, it is internally deflating to hear these words. Uh, but we’re going to talk about how sometimes, uh, optimizing for a specific value can be a better course of action, uh, than optimizing for unknown. Uh, you do have to kind of know and care and love your data and all that stuff, uh, in order to figure out what you’re doing. What values you should be optimizing for. Because that, that can be a tricky, a tricky enterprise. But once you, once you have found that enlightenment, once you have found that Zen moment, so you have experienced that, that spiritual release, nothing will ever top it.
Promise. Anyway, before we talk about that, let’s talk about four bucks. If you’ve got four bucks a month and you want to be a member of my channel, you can click the become a member link in the video description and do that, do just that. Uh, you can cancel any time as they say. Uh, if, if four bucks a month is more than you can stand apart with, if you, if you care very deeply about every George Washington that comes, comes into your bank account. Uh, liking, uh, liking, commenting, subscribing are, are just wonderful ways of becoming part of the, the darling data community of data darlings. Uh, you can join over 5,000 other people who have, who have joined the ranks of the, the darling data, data darling army. Uh, if you need SQL Server consulting, I am world class at all of these things. Um, we don’t even need beer gut magazine to tell us that anymore. We just, we just know from experience. Uh, and as always, my rates are reasonable.
If you would like some very high quality, very low cost training. And if you’ve looked around at the black Friday offers, uh, that other people have out there and you’re like, wow, that’s still hundreds or thousands of dollars. Uh, and that’s only good for a year. Uh, you can get all mine for 150 bucks for the rest of your life. Uh, or just about 150 bucks USD. Of course, we don’t, I don’t accept other currencies. Someone else has to do that conversion and translation for me. Uh, you can, you can either click on the link up there and use the discount code spring cleaning or click the link in the video description and you can get both.
You can get all of that. Uh, upcoming events. I guts none. Oh no, I don’t, I don’t have to go anywhere. Uh, shame. I’ll just stay in my underwear at home. But of course, if you would like me to show up to your event, either in my underwear or maybe in some sort of, you know, vaguely business casual wardrobe, it might look a lot like this. At least from, at least from the top up, uh, then, then, then let me know what your event is and maybe, maybe we can, maybe we can figure something out.
With all that out of the way, let’s, let’s get into this, this fun, fun thing that we have to talk about. Now, uh, I’m going to start this store procedure off written in a way that I personally don’t like. All right. Cause this will lead to all sorts of problems. This is one of those things.
When I say, when I, when I say the words, anything that makes your job easier makes the optimizer’s job harder. This is probably like at the top of the list of those things. Cause this is a pattern that I see in almost every single consulting engagement somewhere in a query that someone is having performance problems with. This is never a good sign. Uh, and it’s not, it’s not good for a number of reasons. Um, you know, uh, we’re going to, I’m going to say parameter sniffing is one of them, but, uh, you know, it’s, it just ends up with some really ugly consequences. Now I’ve already run all the queries for this.
And if you are, and if you’re able to look under my armpit there, you might see the number eight minutes and 31 seconds, right? Eight minutes, 31 seconds. And, uh, the, the general gist of this is that as long as you are searching for an owner user ID, as long as this is not null. And you might have a, you might have a store procedure with, you know, a pattern somewhat like this, where you are just guaranteed to always get an owner, like a, see the equivalent of that ID passed in, you might do okay. Right. It might, you might just not have ever have like a terribly big problem with this pattern.
But as soon as people start doing searches for other stuff that maybe don’t focus on a specific owner user ID, that’s when you run into issues. So the first three executions of this are all looking for an owner user ID. This is all populated in here, right? This one, this one, this one, uh, where we do, oh, that was a, that was a bad, that was bad zoom and etiquette on my part there. That did not go well. Uh, but for these bottom two, um, you’ll see that, uh, we do not have owner user ID populated in there.
And that’s where this query starts to run into trouble. Like this one does okay. Right. Up at the top where we search for 22656. And I want to point out something out here that is kind of fun, uh, is that these queries actually do all get the parameter sensitive plan optimization stuff. That’s why the ones, that’s why the ones that you see are slightly different, um, in, uh, in the first few queries, right?
So like this plan, uh, because parallel has certain amount of, it has some estimates associated with it. This plan is not parallel. It gets an estimate of 117. This plan is not parallel. It gets an estimate of 95. And like the, the, the serial plans are slower, right? That’s 1.7 seconds to scan that that’s one, again, 1.7 seconds to scan that the parallel plan is the fastest of the bunch.
But even if you look at the parallel plan, like we have an index up here that leads on owner user ID, right? That one, this fantastic index right here, this single key column index that I would probably also make fun of if I saw in real life. But we have an index scan here. We should be able to do an index seek, but we can’t because we’re doing the, you know, column equals parameter or parameter is null.
You can replace this with any variation on the, on the thing where it’s just like column equals is null parameter column, whatever. You’re still going to see this same sort of thing here where you, you can’t seek into the index book. Then like these two all have, these two both have the same thing, but there’s an, there’s an additional thing in all of these in the key lookup where we’re evaluating additional predicates.
This is part of what makes this demo sort of sparkle, but like this, like this is also not a good sign to see in your query plans, right? You don’t want to see this stuff over and over again. Now down here, this is where these queries really start to have problems because even with the parameter sensitive plan optimization kicking in, right?
If you look down here, these are the last two things that executed. Uh, we’re, we’re getting like weird, like whatever query variant we’re getting for this is not so hot, right? That’s kind of bad in there.
Uh, so we get the, like the query variant plan for, you know, 95 rows, which is this one. Um, and then we get the, we get that same one again down here, but these execution plans just don’t work well at all for, uh, the, the queries that we get. That’s seven minutes and 44 seconds.
All like all that time, like aside from like, you know, the 51 seconds that you see up to the nested loops join that all that time is in that sort spilling. And down here, we don’t exactly have the sort spill, but we do have like about the 40 seconds of time spent, uh, just like between the lookup and this and everything else. So this, this pattern obviously doesn’t work terribly well.
Okay. So avoid this one as much as you can. Uh, what I see a lot of people do is even is when they do this or when they have other store procedures where things sometimes act up is stick this optimized for unknown hint in there. Now this does turn out better than the query pattern I just showed you.
I admit that it does turn out better, but it’s still not great. Still don’t love it. Uh, and the plans change for these, right?
Uh, where now rather than do any sort of nonclustered index thing, we just scan the clustered index every time. Uh, granted, we don’t have anything that spills for seven minutes, but this is not exactly the plan shape that you would want to see, right?
This is not, this is not exactly fun. And this whole predicate in here is still a problem, right? So that query problem is still an issue with the optimized for unknown. We just take away the cardinality estimates that might’ve happened.
And we replace them with the, the optimized for unknown sort of thing. And then we just get these sort of crappy plans. The two down here still have problems, right?
This one takes a minute and 11 seconds with a lot of that time spent still in the sort, right? Not a fun time to spend, not a fun amount of time to spend spilling. And this one takes 1.2 seconds.
Again, just scanning the clustered index. So the optimized for unknown hint gears all these queries towards a clustered index scan away from a lookup plan, which helps a little bit sometimes, but it’s just sort of not what we want to see overall.
Over here, I’ve got this store procedure set up to optimize for a specific set of parameters that work out pretty well across the board, right? So this is like, rather than say optimize for unknown or let SQL Server do a thing every single time, we’re going to, or even cache and reuse a plan or use parameter sensitive plan optimization stuff.
We’re going to tell SQL Server, every time this runs, I want a plan for when these parameters being these values. And this works out a bit better.
Not perfectly. We still have a scan of, we still have the same problem with the query pattern itself, right? So like, like ideally fix the query pattern. But if you’re kind of hamstrung and that’s too hard for you or whatever, you might be better off just saying, hey, these values work really well to get me the plan that I want.
So we still have the problems in here. We still have the predicate stuff in here that we don’t want, but we get like at least a sort of stable plan across.
And even for the two second query, the two final queries in this, these end up better than the first time around. The last one down here is a bit slower than the optimized for unknown version.
But when you take into account that this one no longer takes like, you know, eight minutes or like, like almost two minutes or whatever that was, like the time you save on most of the executions for this is a lot more helpful.
Granted, this one, you know, we, you know, we’re going to be ivory tower about stuff. We should really fix that query pattern instead. But if you’re going to do something like this, like the time that you lose on this one is made up for by the time that you gain on this one.
So still not great down here and still not great here, but a lot better than we saw with the original query pattern. And from with the exception of this one, with the optimized for unknown pattern.
So whenever you’re, you know, sort of digging through store procedures that have this problem, and if you’re using optimized for unknown in places, then you should probably consider, you know, figuring out a good set of sort of, let’s just call them store procedure defaults and optimizing for those instead, because you can generally find a really good execution plan for a set of values that’ll work pretty well across a lot of other sets of values.
Might not be perfect. Might not, but there might be regressions in some places, but it’s better than like almost the full thing being a regression. Like you’re like, I often see with optimized for unknown.
So anyway, hope you enjoyed yourselves. I hope you learned something. I hope you will, I don’t know, maybe optimize for specific values rather than optimize for unknown. And I will see you in another video shortly.
Maybe we’ll see. It depends on how cute I’m feeling. Anyway, goodbye.
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.