Indexing SQL Server Queries For Performance: Fixing non-SARGable Predicates

Indexing SQL Server Queries For Performance: Fixing non-SARGable Predicates



Thanks for watching!

Video Summary

In this video, I discuss the challenges of dealing with non-sargable predicates in SQL Server queries and how rearranging indexes can help improve performance. I share a practical example where a computed column is wrapped in a function within a WHERE clause, leading to suboptimal query execution. By creating an index that allows for better seeking, we demonstrate how to mitigate these issues without altering the underlying stored procedure, which might be immutable due to vendor restrictions or other constraints. This approach showcases a clever workaround using SQL Server’s indexing capabilities to optimize queries even when direct code changes are not possible.

Full Transcript

Erik Darling here. Lo and behold, with Darling Data. No acquisition letters yet. I applied to Y Combinator and I said, hey, anyone want to buy this thing? So far, someone sent me a bottle of lube and a can of pork beans. Those are the only offers I’ve gotten so far. I don’t really know what to do with that. I’ll probably just keep making these videos in the meantime. So anyway, we’re going to sally forth with our series on indexing to improve query performance in your SQL Server queries. And in today’s video, we’re going to talk about how you can rearrange indexes to fix non-sargable predicates. I know that there’s, I don’t know, maybe depending on what your reading habits are or what your interests are in general, you may have seen blog posts about using computed columns to do this. We’re going to talk about computed columns a little bit later in this series because I had a comment about computed columns that now has set me ablaze.

So this is not that, this is not about using computed columns. So if you are expecting that, well, stick around because we’re going to do something different. Maybe you’ve never seen it before. Again, depends on your educational background a little bit for SQL Server things. But before we do that, we of course need to talk about your financial background and your financial backing of Darling Data Enterprises. If you would like to support the content that I create on this channel, there is a link down in the video description that says become a member. And by clicking on that link, you can give me four bucks a month to support this channel, which after taxes is a little over $3. So who is the real winner?

I don’t know. If you, you know, I realize that it’s Christmas and while some of you might feel giving in the realm of $4, you may have already bought your, your, your Nana or your aunt or I don’t know, a step parent, some, some dollar scratch offs or maybe a couple of $2 scratch offs. And that, that, that $4 has evaporated from your wallet, from your financial world. In that case, you can always like or comment or subscribe.

I’m happy to see numbers of any sort go up except blood, blood pressure and cholesterol. It’s the only ones we like to see either go down or stay even. It’s the only things we care about.

If you need help with SQL Server, I am famous for processing. You’re performing Christmas miracles. So if you need any of this stuff done, if your SQL Server is slow, I don’t know. Do you sell a lot of things on Christmas?

Probably not. But you know, it is the holiday season when sales do tend to spike. So if you have, having SQL Server problems, I am a consultant that fixes them and my rates are reasonable. So there’s really no reason for you to go anywhere else or talk to anyone else because they won’t, they won’t do as good of a job as me.

If you want some great SQL Server training all about performance tuning and other, well, really just performance tuning. I was going to say other things, but I pretty much stick to the performance tuning stuff because I don’t want to spread myself too thin. If I start pretending that I know about availability groups, I feel like you would see through that charade pretty quickly.

But you can get all of mine. It’s about 24, 25 hours of content for the rest of your life for about 150 USD when you use Yonder coupon code. The fully assembled link to perform all of these Christmas miracles is also in the video description.

So you don’t have to do too much work or typing because I’ve seen, I’ve seen your typing. Not impressive. Mavis Beacon would be incredibly depressed if she saw you typing.

Rolling in her, rolling in her CD-ROM drive. Because she is. More news on events as 2025 progresses for now.

You must live in darkness. Sorry about that. But with that out of the way, let’s talk about fixing non-sargable predicates with indexes. Now, I think I dropped this index.

Yes. Well, if I didn’t before, I did now. So I’m going to start creating this and then I’m going to start talking. So I don’t, you don’t have to sit there and wait for an index to create. That’s just rude and crude and vile.

And for some reason, ZoomIt is taking its sweet time. All right, let’s move some of this stuff around a little bit. So it’s all on the same screen. We don’t need you struggling to read. I know I’ve, again, much like you’re typing, I’ve seen your reading and you are, you are not very good at either one.

So as a consultant who gets called in to deal with performance issues to this very day, despite the bevity of, be, be, be, be, be, be, be, be, be, be, be, be, be, be, be. Despite the bevy of knowledge distributed across the internet widely and freely that when you start wrapping columns in your where clause and your join clause and functions, even the built-in ones in SQL Server, you are bound to at some point have that cause some performance issue, big or small, it will creep up on you.

And the bigger your data gets, the worse these performance issues will get. I fix these things constantly. It is like an unwritten rule of my consulting engagements that I will be pulling is null and coalesce and substring and replace and all the other things out of joins and where clauses where people are just done, just plain dumb things with them.

And what, but what, what sucks is, gosh, the, the, I think the number of actually qualified third party vendors who make products that, that, that interface with SQL Server is, is very, very small. Even Microsoft’s own, like software products that touch SQL Server, the, some of the worst code and database design and indexing I have ever seen in my life. It’s like the people who make software at Microsoft are here.

And the database team at Microsoft is here. And there is like, just like this, like this silo bunker wall between them. Like there’s just no way to meet them.

Like even the people who work on like entity framework, like they just do things that like, if anyone with a reasonable sense of databases saw what they were doing, they would just hit them with a four by four. They would like hacksaw Jim Duggan, just whack them.

I don’t know where they get this stuff from. It, it, it, it never ceases to, I think they do they just, I think they just want to keep me in business. That’s what it feels like.

I mean, if that’s the case, thank you. I appreciate your contributions, but man, it’s astounding to watch. Now, this is not a giant big fireworks demo. This is, this is just to prove a point that you can, you can make a difference even if you can’t change the code, which was, I think was the point that I was going to make when, when I started talking about vendors and Microsoft and entity framework is that sometimes you can’t change the query.

Sometimes there are things and things that you are not allowed to change. Now, vendors might disallow you from changing store procedures. That might say that puts you out of support.

You might not be able to figure out how to fix whatever entity framework query is being generated. You might, you know, like you might have zero, like queries might come from like binaries or DLLs or something built into your software. And you might not be able to do anything with, with the change that, right?

Like, what are you going to do? You’re, you’re, you’re hosed on that. You can’t change that. The vendor has to change that. And the, you know, the vendor won’t do anything. Won’t lift a finger to help you.

Who are you? Anyway, let’s say we have this index, right? And we’re going to, we’re going to pretend that the store procedure below is completely immutable. We are not allowed to change it.

We, we, we’re going to, we’re going to, we’re going to, we’re going to straddle a fine line here and change an index. All right. We’re going to be, there’s a secret that you and I are going to have to keep. We are, we might be violating a EULA somewhere.

I don’t know. But this is the index that we have currently on community owned date and score. This is on the post table. In case you, in case, again, I know you’re, I know how you’re reading is. That’s the post table.

And let’s pretend that this queer, and I’m going to handicap this query a bit rather. I’m going to, I’m going to say, I’m going to hobble this query a bit. I’m going to, I like, I like misery as a movie and I like, I like the term hobbling. So we’re going to say, we’re going to hobble this query, but I like misery much better than golf.

In golf, you get a handicap and misery. You get your ankles broken, the sledgehammer, but we’re going to hobble this query a little bit to make sure that SQL Server can’t use batch mode to do anything better.

And we’re going to limit this to max.stop one so that I can sort of show you just how profound a difference you can get with these changes, because you know, who knows, maybe you had some, maybe you hired a crappy consultant who told you to set cost threshold for parallels to like 350 or something for no reason.

Just a wild guess. They were like, Oh, those C, look at all those CX weights. Let’s, let’s fix that. Because this is the kind of help you get at unreasonable rates. When you pay Erik Darling with his reasonable rates, you get good advice.

When you pay other people, I don’t know what you get. I’ve seen the results though. They’re not good. So we have this store procedure and this store procedure is going to break a cardinal rule of querying.

It is going to wrap the community owned date. There’s the lead column in our index and the coalesce function. We’re going to, I don’t know why people do things like this. They think that like, what do you think nulls are going to throw an error?

You think SQL Server can’t handle nulls? It’s your stupid programming languages that throw those null exceptions. It’s not SQL Server. But people do dump stuff like this all the time.

Don’t ask me why. I didn’t, I didn’t make them do it. But when we run this query and we look at the execution plan, we’re going to be unhappy, aren’t we?

We’re not going to be thrilled with the performance of this thing. It takes 1.5 seconds and it scans our index. All right. We have, we have a perfectly good index for seeking into the, the, the community owned date column, but yet we don’t.

We do not seek into this index. We scan the entire thing. It takes a second and a half and we’re, we don’t like that. So what we’re going to do, because we’re, we’re allowed, we’re, we’re, we’re going to pretend that we’re allowed to do this.

We can’t change the store procedure. We can’t just take that coalesce. Like we can make a copy of the store procedure where the coalesce is removed and we could run it side by side with that, with the other store procedure. And we could send the results to a software vendor and they would say, no, no, we’re not helping you, which is what happens all the time because software vendors suck.

But if, but let’s just say we were going to change this index and we’re going to use one of my favorite index options in the world, drop existing equals on, right? Just slide this index in place, drop it on in, which, which is good for us.

So, uh, now when we run this query or sorry, when we run this store procedure, it does not take a second and a half. It takes about a hundred milliseconds because now we can seek into the index for the score, to the score column.

And we just have that stupid residual predicate, right? We now have a SQL predicate on score and we just have the awfulness that is the coalesce function, uh, running its case expression on the community owned date column. And that’s a residual predicate, but we can, we can at least seek to the scores we care about.

And that’s, that’s the good part for us, right? We no longer have to worry about, um, all that, uh, scanning of the index because of the coalesce function. Now we can seek to the, the, the, the scores that we want and then evaluate the community owned date second.

And there’s a lot less over, or there’s a lot less pain in that. There’s a lot less suffering than that. It’s far less overhead because we have already sought, you’ve already done a seek to the limited number of values we care about and applying that residual predicate is not a big deal. If the score column were on a, the score come at a less selective predicate, you know, might be, might be a seek of the whole table plus a residual predicate of the whole table.

That would potentially be less, that would potentially be equally as unpleasant as just a scan of the whole table. But, uh, we’re going to pretend that we’re, we’re a little bit smarter than that. So anyway, this is how you can re, uh, arrange your indexes to beat non-sargable predicates.

Uh, often SQL Server will do the, the smart thing and choose better indexes when it can seek because it thinks, you know, it’s like, Ooh, look at this nice cheap seek I can do. Uh, SQL Server being the cheapskate that it is, will often choose the smarter index in this scenario.

Even if you had another index hanging around, um, if like, you know, in this case, we change an index. You like, you know, if you, you, you, you run the SQL Server that your software runs on, uh, that started the vendor software runs on, you might just create a new index.

And then, you know, when you have to do a software upgrade, you just quietly drop or disable the, the custom indexes you’ve created. So the, the, the, the installer doesn’t explode and say, how dare you try to do better than us? You know, like, cause you’re all idiots.

You know, I don’t know. I don’t understand how you get paid to do what you do. I don’t understand how, how your software company makes hundreds of millions of dollars a year. The product is garbage.

Ah, well, what can you do? SQL Server is $7,000 a core and it can’t even handle a column wrapped in a function still. So, one wonders how, how deep the rot goes sometimes, I suppose. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. Uh, I hope that you, uh, are, are happy and healthy and feeling loved and appreciated by all those around you. I don’t know.

Maybe you have, maybe you have a cute dog or something. Hmm. Like, licks your hand and take it for a walk. Sounds nice, right? Smoke a cigar.

Anyway, uh, I’m gonna go record something else now. I think I have another video. Uh, apparently I have 50,000 videos to record. So, um, I’m gonna try to get that done. Anyway, thank you for watching.

Goodbye. Bye.

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.