Strange Query Plans With Inequality Predicates In SQL Server
Video Summary
In this video, I delve into a fascinating aspect of SQL Server query plans—inequality predicates and their impact on performance. Specifically, we explore how parameterized queries can lead to strange query plan patterns when dealing with nullable columns. By examining real-world examples and discussing the nuances of index usage, I demonstrate practical solutions such as adding redundant predicates to improve query performance without altering indexes. If you’re facing similar issues or just want to enhance your understanding of SQL Server query optimization, this video is packed with valuable insights that can help streamline your database operations.
Full Transcript
Erik Darling here with Darling Data, and in this video we are going to examine, like, I guess it’s all on the next slide, isn’t it? Strange query plans with inequality predicates. And by inequality predicates I mean not equal to, whether it’s the exclamation point equal sign or the two opposing type characters there. because I’ve run into this, you know, I’ve reached my cost threshold for talking about it publicly, working with clients on a few things. And there are a few things about this that I think are interesting and that I hope can help you with your query tuning life because you can’t always change the indexes, right? So we’re going to talk about this. There are some things that go along with it, like, nullable columns, and parameters and parameters and stuff, but there’s only so much you can put in the title before it just becomes, like, an article unto itself. But before we do that, let’s talk about you and me and my friend Moolah.
So, if you would like to support my endeavors to bring you the very highest quality, most interesting, incisive, probably the most important SQL Server information available anywhere in the world, you can visit the link in the video description below and sign up for a membership. And for as few as $4 a month, you can keep my beard nicely aligned because, you know, the razors are expensive these days. I don’t know if you’ve bought razors lately. Man, more expensive than eggs. It’s insane. If you have spent all your money on eggs and razors or if you’re over there shaving eggs or whatever it is you do with your free time and you have run out of money, you can do other things to help this channel move along in the world.
You can like, you can comment, you can subscribe. We are up over, let me actually get a current tally of things here. I’m going to look at my YouTube app here. We have about 6,200 subscribers and about 60 paying members. So, we are reaching nearly like a 1% status there. Pretty good. Pretty good. Pretty good.
If you would like to ask a question that I will, privately, that I will answer publicly, there is also a link down in the video description to do so. There is a little Google form. You type in your question and then I answer it during office hours. I do them five at a time, which is, I don’t know, just seem like a nice number at the time.
Maybe I will change that if people are, for some reason, five-a-phobic or something. If you need help with SQL Server in a way that asking office hours questions or poking around the internet isn’t doing you much good for, I am a consultant with reasonable rates and I do all of this work with SQL Server quite effectively and quite efficiently.
And, you know, you’d have a hard time finding a better deal on performance tuning SQL Server. At least, if you’re trying to avoid just some numbskull who’s going to look at missing index requests and tell you to add them. Because that ain’t my game.
If you would like some equally reasonably priced training content, you can get all 24, 25 hours of mine at the beginner, intermediate, and expert. Not just advanced, but expert level. For about $150 USD and that is good for the rest of your life.
It just keeps going as long as you keep going. So, stay healthy out there. SQL Saturday, New York City, 2025, May the 10th, Times Square, Microsoft offices. It’s going to be a hoot.
It’s going to be a real hoot. With that out of the way, let’s talk about the subject of today’s video. Now, in order to sort of show you where this gets interesting, I have created two indexes on the post table.
One on a column called parent ID and one on the column called owner user ID. And you’ll notice a couple little red squiggles here, which means I was kind enough to create these indexes ahead of time. And if I run this query with a couple literal values, we get a very sane and rational query plan using both of those indexes.
We have an index seek into P1 and we have an index seek into P0. And when we seek into these indexes, we very efficiently evaluate. Oh, come on, tooltip, stick with me here.
This predicate, the equality predicate on owner user ID. And we seek into this index and we find where the parent ID is greater than 0 and less than 0. Or some combination there.
Both greater than and less than 0. So, not equal to 0. And that all looks pretty good. Now, there is a missing index request here. And if you are able to create composite indexes or change indexes on your server and do all that stuff, great.
We’ll talk about that in a moment. But what gets interesting is when you take a query like that and you parameterize it. So, now I have the exact same query set up.
And this would be the same with the store procedure. This is no different than using the store procedure here. But when I run the query like this, where both of these parameters have the same value, 0 and 22656. And they are the same definition in here.
And they are used the same in here. The query plan takes on a rather strange shape. Look what happens. Now, we have all this additional stuff in our query plan.
We have some constant scans. We have some concatenation. We have some top-end sorting. We have some merge intervaling.
And then we have a nested loops join to the P0 index on the table. And, of course, the P0 index is where we are looking to do our seek on parent ID, that inequality predicate. What’s particularly, let’s say, a bit icky about this one is that this whole thing is in a serial zone.
You’ll notice that SQL Server steps out of the serial zone immediately after doing that and distributes the streams parallelly to the rest of the parallel zone in the query plan. But this is where we spend the majority of the time. This thing runs for about two seconds total.
And we spend 1.7 seconds in this section right here, between the 1.4 seconds there and the 1.8 seconds there, isn’t it? Pretty close. Now, this is because SQL Server has to do some additional protections in case you ever pass in what might be a null here.
It doesn’t have to do that when you have a literal value. Part of why this has to happen is if we hover over, and I’m going to show you what happens when you flip these in a second. But both the owner user ID column, you can see that is nullable there, and the parent ID column, you can see that is nullable there.
And if we were to switch these around, and I’m not saying that this is the correct query, but if we best show you what I mean. I’m going to hit the insert key there. We don’t want that.
If I switch these around so owner user ID is an inequality predicate and parent ID is an equality predicate, the exact opposite will happen. All right. This will run for roundabout the same amount of time.
Well, actually, a little bit longer there, 3.8 seconds. Hoo-wee. But now the index seek have switched places, right? Now the index seek up here for the equality predicate is on parent ID, and the index seek down here for owner user ID is this is where things get all weird, right?
So this is the strange part of the query plan now. But let’s focus on the original form of the query, right? So this is limited to the inequality predicate with a parameterized query with a nullable column.
Now, what you can do if you want to fix this without changing any indexes is add a sort of redundant predicate here and say, and P, that’s supposed to be a dot, and the dot didn’t come through. Parent ID is not null. And if we add this in alongside our inequality predicate, all of a sudden SQL Server has a whole lot less to worry about.
And we get just about the same query performance that we were getting before, right? So this plan looks just about the same. We have an index seek.
We have an index seek. And this all takes just about 550 milliseconds, which ain’t bad at all. Now, I’m going to quote this out for a second. And I’m going to create the composite index that SQL Server was requesting on the post table.
So that’s leading on owner user ID with parent ID as a secondary key column. That’ll create in a second there. And what I’m going to do is just show you that even, like, this does help the performance generally.
But you still get the weird query plan when you don’t have the not null check on the parent ID column for the inequality predicate. So if we run this, this query will run very reasonably fast. But we still have all this weird stuff in there, right?
We still have the constant scan, the concatenation, the top end sort, the merge interval, and the index seek down here, which we can, of course, get rid of if we keep the semi-redundant predicate on parent ID not being null. And we can get a much nicer, neater execution plan when we tell SQL Server to discard any nulls that might exist in that column.
Now, the kind of funny thing here is that in the owner user ID column and the parent ID column, actually, neither one of these actually has any nulls in it. But SQL Server does still have to protect itself because it has to create a query plan.
Because what if some nulls show up? Sure, there are no modifications right now. But what if, like, three seconds later, I insert a null value in there, and all of a sudden, SQL Server has to figure out some way to cope with that?
So if you have inequality predicates in your query plans, even if they are rather quick query plans, but you have all of that, you start seeing weird stuff with the query plan pattern that I showed you before, where you have the constant scan, concatenation, top end sort, merge interval thing going on there.
All it takes is the redundant predicate to weed all this stuff out. Sometimes that is just a useful thing to do to cut down on query plan weirdness, because you never know who’s going to be looking at these query plans and getting very confused by things.
They might see all that stuff happen and say, wow, I have no idea what all that is. I don’t know. I have no clue.
So it’s just a nice formal thing to do to get rid of it with a redundant predicate and say, let’s reject those nulls out of hand, and let’s just have a nice simple index seek.
So if you are having performance problems with this type of query, that’s one way to fix it. Of course, the composite index is another way to fix it. So, you know, you might want to mine that a little bit.
And of course, if you need help with this sort of thing in real life, and you just can’t figure any of this stuff out on your own, well, my rates are reasonable.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that we will meet again soon in the next video. All right.
Thank you for watching.
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.