Optimizer Rules I Wish SQL Server Had
Video Summary
In this video, I dive into some optimizer rules for SQL Server that I wish we had, focusing on how they could improve query performance and make our lives easier as database administrators. I start by showcasing a new rule in SQL Server 2025 that optimizes queries written using `LEFT OUTER JOIN` with a `WHERE` clause to remove nulls, demonstrating why it’s more efficient than the traditional approach. Then, I explore other rules that could benefit us, such as decoding case expressions that produce literal values and transforming row goal costing into more intuitive query plans. Throughout the video, I highlight how these proposed changes would simplify query optimization and reduce unnecessary overhead, making our work with SQL Server even smoother.
Full Transcript
Erik Darling here with Darling Data. Look at this wonderful logo, this steadfast forever logo of mine. I’ve tried various redesigns of this, but none of them quite have the same feeling, you know? Logo gives me a feeling. Today we’re going to be talking about optimizer rules that I wish we had. And when I say we, of course, I mean SQL Server, but we being SQL Server users, we would have them if SQL Server had them, and that would be wonderful.
I’m going to show you one thing I’ve talked about before, a new rule in SQL Server 2025 that I think is just grand. And then some other rules that it would be cool if the optimizer had available to it. So we’ll do that. Down in the video description, since my job is making SQL Server faster in exchange for money, you can do things to support those efforts, like hire me for consulting. I can make your SQL Server faster in exchange for money.
You can buy my training, and you can learn how to make your SQL Server faster, again, in exchange for money. That’s buying the training. You can support this channel. That’s the sort of kindness of your heart thing. I suppose I am teaching you how to make SQL Server faster. You’re getting most of the money for that one, though, since this is free.
And of course, you can also ask me office hours questions. You can ask as many of those as you want. And like the trained monkey I am, I will answer them. And of course, please do like, subscribe, and tell a friend. I understand if some of you out there want to keep this channel a secret. Keep it under wraps so you can maintain your competitive alpha SQL DBA advantage at work by knowing all this stuff that no one else knows.
But sharing is caring, my friend. So, you know, do spread the good word and all that. By the time you watch this, it will still not be 2026. But these events are obviously happening in 2026 because March of 2025 is long gone. So in 2026, I will be at Datatune in Nashville, March 6th and 7th. And I will be at Data Saturday Chicago, March 13th and 14th.
We’ll be a little bit closer to baseball season. I’ll be about ready to leave the house again. I will have a reason to live, as they say. Okay. With that out of the way, though, let’s talk about these optimizer rules over here. We’re going to go over this way and talk about some stuff. Now, this is my SQL Server 2025 instance. And I got a couple of queries here. And one of them, well, they both have hash join hints.
I’m going to show you why they’re there in a minute because it’s quite frankly embarrassing. But one of them is operating in compatibility level 160. That is, of course, the highest compatibility level if you are on SQL Server 2022. And this rule is not available there. The database default for my database over here is, of course, 170.
But just to be explicit about it, I am telling I’m showing you that this is compatibility level 170. Now, if I run these two queries and we look at the resulting query execution plans, we will see what the rule that I am talking about in action. Now, the first, we’ll go back to the queries for a moment. This is a query pattern that I have had to argue with many a person about over the years.
And I say, you know, this maybe isn’t the best way to do things. You should maybe try writing this as not exists instead. Might be a little bit more efficient. You know, might be something to look at and they’ll say, well, I would, but I don’t know how to write a not exist query. And I say, well, my rates are reasonable. I can help you with that. It’s pretty easy. Right? Not exists. And then a thing.
And then you’re done. Right? So don’t forget your where clause. So this query is essentially looking for rows in the user’s table that don’t exist in the post table. Wonderful. This is, but again, this is not the best way to do that. This is probably how I would say, no, don’t do that here.
But we look at the query plans. We’ll see that the first one has done a hash match left outer join as I have told it to do. And then we have a filter operator. And of course, I’ve talked about all this stuff before in the past. So yes, this filter operator is responsible for removing rows after the join where the ID column in the post table is null.
Remember that it with outer joins that null rows are preserved or non matching rows are preserved. And we can, we can filter them out. We can filter the non matches out. And this, this works well for, well, I mean, it works with the, using the clustered primary key on post because it is not nullable and it does not have any null values in it.
So if nulls are produced by this join, it means that the rows didn’t match. Okay. But the, the, the way that you would normally write this query to get the query plan that I’m about to show you would be using not exist. But SQL Server has a brand new optimizer rule, which says, hold on.
Hang on. I know, I know what you wrote, but I think I know what you meant. And I’m going to do things a little differently. You’ll notice that there is no filter operator here. We just have a hash match left anti semi join.
Have to be very careful saying that one, lest you get sued. So, but this, this query is essentially removing the non matching elements at the join rather than fully joining everything together and filtering it out afterwards. I think this is wonderful. High five, Microsoft. You did it.
The reason I have the hash join hints in here though, is cause golly gosh, without, without those hash join hints, uh, things, things didn’t look good for either one of these queries. Let’s just to show you, right. Let’s, let’s run this. Uh, you know, this is not going as fast as it did that first time.
And, uh, that’s not good. These query plans are embarrassing. Yeah. I mean, the only thing that could make them more offensive would be if they were parallel merge join plans, but, uh, they, they basically follow the same pattern where now we have a serial plan, uh, which is a shame for the number of rows that we have.
Uh, and now we do a merge join left outer join, and then we have the filter and, and then down here, um, of course we have, uh, the, the, almost the same thing where it’s, it’s serial and we have, but this is a merge left anti semi join. Not a good time. Not a good time. Uh, and of course for completeness, another way to think about writing this query aside from not exist would be using the fabulous except operator, right? Just write the query like this and, and count this, right? That, that works pretty well. Uh, remember both of these queries got back, uh, 1 0 3 0 9 8 7.
And if we run this one again with the hash join hint, we will get back 1 0 3 0 9 8 7. And we have our left anti semi join plan. So all that’s great. Right now that is wonderful. All that is fantastic. Let’s talk about some other optimizer rules that might be nice for us to have.
One of them is this. So a lot, a lot, a lot, I’ll see people write, uh, create a view or create or alter a view. If, if you’re, if you’re hit with that. And what they do in the view is have some sort of case expression. And the case expression usually allows, uh, a user to like, you know, uh, supply like maybe a more user friendly search term for something.
Like question or answer or wiki or tag wiki ex, ex, ex ex, ex, ex, ex, ex, ex, ex, exerpt. Uh, well, whatever. Forgive these things.
Uh, but the, the problem becomes that when you, you, you, you, you try to write a query that filters on a case expression like this, um, for the, like the outcome of the case expression, then things get a little dicey, right? So let’s create or alter this view. I don’t know which one we’re doing.
What do you think we did here? Did we create or did we alter? I don’t know. Now, if you write your queries like this, which are not user friendly, because now you’re expecting users to have like memorized whatever data dictionary you have and know that post type IDs of one are questions. Like if we write the query like this, you know what we get an index seek into the, the, the index that we created on post type ID and, and all is well there.
But if, if we write the query like this, where we say, Hey, I want to find where the, the post type. Now this is using the post type case expression column. Well, we don’t use that index so well anymore.
Now, now, now we have to scan that whole index and things slow down a bit. So one rule that it would be really nice to have is, you know, just, and we could start simple here. We could start real easy and just, you know, for, for, uh, case expressions that produce like literal values like this, the optimizer could do a little something to decode that.
I realized that there are much more complicated case expressions in the world that could be written. I’m not asking to take care of all those because that would be nuts. Right. They’re like, you know, even if this were like case, what, like, you know, like a column or something, or maybe a column from a different tape, like different tables, that stuff gets real dicey.
But like, just for like when the case expression has some literals in it, like, why doesn’t the optimizer just sort of like reverse engineer that a little bit? Just like decode the stuff. So we get the nice easy seek plan.
Like why, why, why do you need to beat us up with this stuff? So that’s, that’s not cool. It would be nice if the optimizer could do that. Another one that I, I, I have a particular problem with is the, the costing around, um, uh, the, the row goal costing that leads to query plans that look like this.
So let’s run this thing. And what we’re doing is saying, we’re giving me the top one post, uh, ID from the post table, uh, that doesn’t have a vote, right? Ordered by the ID column descending.
This query takes around five seconds. And you can see most of the time in this plan. I mean, all of the time in this plan really is spent in here, right? Top above scan, almost never productive, right?
This is almost never a good time. There are very few cases where this works. And the, the reason why this, why this query plan pattern kicks in is because of some row goal stuff, right? We say select top one.
That’s a row goal, um, exists and not exists. Make it may introduce row goals in our queries. I have that whole video series on row goals or my presentation. So you can go watch that if you’re interested, even talk about this here, but this, this, this query pattern is almost never useful, right? We do get a missing index request, but SQL Server should really just not bother with that at all.
Like if we say, Hey, just hash join, right? Uh, this does not take five seconds to run, right? This takes, well, I mean, I guess, no, let’s, let’s, let’s give, let’s give one of those fancy optimizer things.
Uh, like, like memory grant feedback a chance here. There we go. Now that thing doesn’t spill anymore. Look at that two seconds instead of five seconds, because we get a hash join plan. Granted, we don’t have a missing index request now, but it would be cool.
The SQL Server would give us the missing index request and the hash join plan and just move on, right? Like why the top above scan? Why the nested loop?
Why, why are you beating us up? If you can’t have a top above a seek, don’t do it, right? Lay that thing out, get away. Right? Not a good time. Another one that I really dislike is the eager index pool.
So if we run this query, right, this query is going to take a little while to run. Oh wait, maybe it’s not. Cause I have an index on there. Ah, crap. Ah, yeah.
Ruined the whole thing. All right. Let’s get rid of indexes. I should have cleaned that up before I started writing this. All right. Let’s get rid of our indexes and let’s just say, do this. Right?
So now we’re going to get the slow plan that I meant to get. All right. This is what I get for like having multiple versions of SSMS open and working on multiple things at once. Not everything goes well on the first try. But this query is slow.
This query is not fun to watch. Right? And you can see this query took about 15 seconds and all of the time in here was spent eager index spooling. Right?
So we have 15.6 seconds here. The whole, well, like 15.7. It’s not that far off, but anyway, all the time is spent doing a nested loops join and building this eager index spool to make the nested loops join more palatable. Okay.
Now, since this query is using cross apply, SQL Server is not really hip to the hash join. Right? Query processor cannot blah, blah, blah. But there are other ways of achieving this that the optimizer could say, hey, I was thinking about building an eager index spool, but instead, why don’t, like, I understand this query. Right?
Seven seconds. And we don’t have the big nasty spool in there. Granted, like, this is still not like a well tuned query, but it’s so much better than the alternative. Eager index spools off of large tables, anything over like a million or so rows, they just completely lose their marbles.
And then, like, eager index spools, they get built single threaded. I can go back a little bit to talk about that. Even in a parallel query, eager index spools get built on a single thread.
Otherwise, they risk sort of deadlocks and stuff. They get loaded one row at a time. It’s very inefficient.
Spools, the code for those hasn’t changed in SQL Server seven. So, like, they don’t have any of the cool new, like, bulk loading and, like, other optimizations that things like temp tables have. So, like, if we come over here and we look at the properties of this and we look at the actual number of rows, we’ll see thread two got all the work.
And this isn’t just a quirk of this demo. This is every single eager index pool that gets built. It’s a little harder to show, like, the row by row loading, but you can see, like, taking 15 seconds for 8 million rows is not the sign of, like, a bulk load.
Right? Like, if you dumped 8 million rows into, like, a temp table just said, like, select into, maybe a second at the high end. Right?
And if you’re especially, I mean, if you’re an Azure, like, like, SQL database or managed instance, it might be longer because their tempTB is on, like, glue and dead frogs or something. But anyway, this would be a nice query plan pattern to have to take the, like, get rid of the eager index pool top one of these stuff and just sort of transform it into, like, a row number, like, type thing. Right?
Because this gets us the same query plan shape just without the eager index pool. Another particularly gross one is when you join on an OR clause. Right? It says, we don’t, we don’t like joins on OR.
Well, I mean, I don’t like joins on OR clauses because of what they do to, like, query plans and performance. But I mean, I guess fixing them is how I make money. So that’s, that’s okay.
But the optimizer really should be able to unroll stuff like this. The optimizer should be able to do a better job of, like, saying, hey, like, like, clippy style, like, like it did with, like, you know, one of the 2025 rule where it was just like, you wrote a left or join and let them check for nulls. Because I know what you meant.
I think I know what you meant in there. So when we run this query, right, and we say, select the stuff from the post table joined here on either the ID in the users table equals owner user ID or the ID in the users table equals the last editor user ID. Right?
We could join on this or this. Anything will do, right? Give me anything back. I don’t even know what this query means anymore. This query takes a rather long time to run. We’re at about 20 something seconds now.
And it’s still going. Chugging along here. Not easy like Sunday morning.
So 31 seconds later, we get this giant catastrophe of a plan. SQL Server should be able to say, hey, I think I know what you meant to do here. All right.
You should be able to unroll this a little bit. This is a very, very simple set of predicates. SQL Server should be able. And I’m using apply here sort of out of convenience. But if we write the query like this, it is no longer a 30 second ordeal. SQL Server just does a C or rather a scan of both of these indexes concatenates the rows and moves on.
We don’t have all that crazy junk with the constant scans and other stuff that we had before. The optimizers should be able to handle these situations, right? And I’m not, again, I’m not saying every super complex case should be covered by this, but there are some pretty easy ones.
Like, again, I’m a high school dropout and I can figure this out. People who work on the optimizer team can unroll this. We’re in trouble.
Right? Now, there is an optimizer rule that I’ve changed my mind about in recent years. I used to, you know, complain quite heartily about, you know, eager about CTE in SQL Server because there is no way for SQL Server to materialize them. Other database engines like Oracle and Postgres and I’m sure many others offer either automatic or hintable, like materialization of CTE.
This, of course, really hurts SQL Server when you reference CTE over and over again because you have to re-execute the query in them. They don’t materialize naturally in any way in SQL Server. So I wrote kind of a weird set of queries to try to find a good way to show you this.
But, like, if you run this inner part of the thing here, we get values 11 through 91 back. And if we run this query, basically what this is doing is showing me which IDs in the votes table sort of give me what the row breakdown is with different modulo math. And this will make more sense in a minute.
But when you look at these numbers, you can kind of see, like, okay, well, if I modulo by 91, I get 581,000 rows. If I modulo by 11, I get 4.8 million rows, stuff like that. So what I want to show you here is kind of why I’ve changed my mind about this in SQL Server.
And it comes back a bit to the eager index pool conversation where, you know, like loading data into the eager index pool, it couldn’t be done in parallel. It’s row by row. There’s no bulk loading, all that other stuff. It’s a bad time, right?
So, like, if, you know, Microsoft are willing to invest time in improving spools, then this would be a palatable optimizer rule. But because they’re apparently not, because again, instead we get fabric, you know, birthday herpes, we get stuff like this. So what I’m going to do is I’m going to write queries that will intentionally, like, load a bunch of data.
Like, this one’s going to put a bunch of data into a spool, but not return any rows. Because what I want you to see from the query plan is how, like, the difference in time between rows ending up in a spool and rows ending up in a temp table. So we do this. This one’s relatively quick, right?
The whole query runs for, let’s see, we have, oh, this thing gets all sorts of weird. But, you know, we have a clustered index scan of votes over here. This takes 1.1 seconds. And then by the time we get to the eager, we can ignore the gather streams operator here.
Just pretend this doesn’t exist. We spend about 600 or so milliseconds getting data into the eager index pool, right? The rest of the time in the plan, this is actually kind of a funny one, right?
Because we spend 1.1 seconds here. The gather streams is just like, oh, well, I take 424. Like, the timing gets all thrown off. Now, this is a completely roll mode plan.
So the time is supposed to go higher as you go from this part of the plan to that part of the plan. But things get all thrown off. And there’s a really funny one at the end, too, where, like, the entire thing has taken 1.6 seconds. But this is like 3.5, right?
But it gets a little bit more amusing, too, if we look at the query time stats. Because the query time stats, they largely agree with the query plan itself. Like, I took about 1.9 seconds.
I didn’t know. Where did 3.5 come from? I don’t know. But you can’t see that it took, like, 1.7 and then 1.6 here. The whole thing is a shambles.
Anyway, you can sort of figure out that it took about 600 milliseconds for the million or so rows to end up in the eager index pool. Which isn’t in the eager table spool, which is what it would take for CTEmaterialization. You couldn’t lazy load that.
But just comparing that with a loading data into a temp table, this does not take that long, right? Like, you see it’s 890 milliseconds here and then only about 100 milliseconds to get the rows in there. So, like, the slowdown is obvious, right?
And it gets worse as more rows come into play. So, if we do that same sort of top 99% query, and granted, like, even the 99% query is loading fewer rows because this is, like, loading everything where ID modulo 51 equals 0. This is only loading the top 99%.
I need that 99% in there to get the eager spool. It has to count the rows, right? So, if we look at this one, you know, we spent 900 milliseconds here. Again, we’re ignoring this thing.
And then we spent almost 8 full seconds loading 5.2 million rows into the thing, the eager table spool there. If we do the same thing with a temp table, it does not take nearly as long, right? It does not take 8 seconds to do that.
This thing takes, well, there’s 1.4 seconds here, and then there’s 1.8 seconds. Here’s, like, 400 or so milliseconds there. So, SQL Server does a much, much better job of loading data into temp tables. If we could make at least the eager version of spools, I mean, all spools should be equivalent with temp tables at this point, but the eager version of a spool were made more on par with the way temp tables get loaded, then CTEmaterialization would be a worthy effort in SQL Server.
But until then, it would suck, right? Not be fun. Not be amusing.
Like, I would be angry. There might be some okay trade-offs, especially with not rerunning some subtree in a CTE over and over and over again, but for the most part, I would be very nervous about big, eager spools getting data loaded into them.
Anyway, I talked for longer than I thought I would. It’s time for my protein shake. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And today’s Friday, so I won’t see you until Monday, but I do hope you have a great weekend. All right.
Thanks for watching. Bye-bye, Matthew Red Crossed Costord. means
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.