Fix Your Indexes, Pal
Video Summary
In this video, I explore the nuances of filtered indexes in SQL Server and why they might not always be used as expected. I set up a simple query to count posts based on certain conditions and created two indexes tailored to these conditions. However, when running the query, I noticed that SQL Server did not use one of the indexes designed specifically for filtering out null values from the community owned date column. This led me to investigate further and discovered that including the community owned date in the index definition was necessary for the filter to work as intended. The video delves into why this happens and provides a solution, which could be particularly useful for those working with filtered indexes to exclude nulls or other specific values. I also share a link to a related issue on Connect and mention Aaron Bertrand’s reference to Ladybug, hoping it might help others facing similar challenges.
Full Transcript
You know what would be fun if me writing demos were entertaining at all? I’ve seen Chrissy Lemair’s live streams of her coding things and me writing demos would not be entertaining at all to anybody. So I’m going to leave that. Leave that idea. Let that idea die. What I want to talk about in this video is where filtered indexes are broken. I don’t mean broken like they don’t filter data or something. I just mean filter like you might not see them used. So we have a simple query here. And I call it a simple query because I usually I try to write queries to be as simple as possible to get the point of the demo across. So we have a count in here. And this count is against the post table. And we’re counting where the parent ID in the post table equals the ID from out here and the score is greater than zero. And from the outer count, we are filtering on where community owned date is null. So fairly simple stuff. It’s a count with an account, sort of a join inside. And to satisfy this query, to make sure that this query is satisfied.
I have created two indexes. One index that satisfies, I think, fairly well the inner query where we need to hit, we need to see where parent ID is equal to q.id and score is greater than zero. And this other index on ID where community owned date is null to make sure that we can filter down to this. Now I should have already created these. It’s been a weird day. So yes, I did. Good job. What I’m going to do is turn on execution plans because without them we would not have much of anything to look at. And I’m going to run this query. And we are going to sit and wait. Why are we going to wait so long? I don’t know. I don’t know. Apparently we had a lot of stuff to count. And when I go look at the execution plan, we are going to see something kind of funny. SQL Server used this index down here to great effect, wonderful effect. Yes, wonderful effect. 1.7 seconds spent in that index. And up here though, we see that we did not use our filtered index. We read all 17 million rows of the post table. And because of that, we spent, I don’t know, 10 seconds there. And I don’t know, we just didn’t have a good time generally running this query.
Now what I want to do is tag this index back in. I want to say, SQL Server, please use this index regardless of what you think your best judgment is. And I’m going to run this query again. And I don’t know. It’s not going to necessarily do any better. Doing better is actually not the point. The point is to figure out why SQL Server didn’t use that index to begin with. We have a question of SQL Server. Why? I asked you, I’ve created this index especially for you and you didn’t use it and I feel neglected and alone. I don’t think you take our relationship seriously. When we go look at the execution plan for this one, which took just about twice as long. Ooh la la. Some funny things happened. What is new in this plan? Well, we still hit the goat. The goat was fine.
But now we see that we hit this index and we did fairly well here, but 13 seconds in a key lookup. Bugger, what happened? What did we key lookup? Well, sadly, we evaluated our predicate. Community owned date is null over here. Even though that index is filtered on where community owned date is null. We don’t have any of those null values in there. SQL Server didn’t believe us. I mean, I hate when people say literally, but literally this index filters out anything where community owned date is null. What predicate is there to evaluate?
What could possibly be happening in there? Well, what we need to do in order to get this to be functional is include community owned date in the definition of the index. So we will, and this is maybe something that should be, would be obvious to some people, but we, we got away up with score not being in the index. It was just where score equals zero. And SQL Server had no problem with that.
But in order to get this to work, we need to include community owned date in the index definition. Why? I don’t know. It’s been called a bug. It’s been called an oversight. It’s been called a mistake. But I’ve been called all those same thing. So I don’t, I don’t really, I don’t really know what to think there. But now when I run this query, we will get our, I mean, it’s going to use our index because I’m telling SQL Server to use our index. And we’ll finish a few seconds faster than our previous time. But more importantly, we use our index and there is no, no key lookup malarkey or shenanigans.
So with creation date included in the index definition, we no longer have to do that. Now, this is, this seems to only happen with is null, is null filters. So if you are the type of person who creates filtered indexes to, to screen nulls out or, or whatever, then you may find this to be useful information. There is a link to fix this thing. And I think this thing has been hanging about since connect with a thing.
Aaron Bertrand recently referenced something called ladybug, which I am not, I’m not nearly old enough to recall. So best of luck there, Aaron. But there’s a shortened bit.ly link that goes to this link. So bit.ly forward slash uppercase B, uppercase F broken filters. These links are case sensitive. So if you do care enough to go and vote on things, you will have to type that in the, the, the long way, but I will put links to them in the video description so that you can exert minimal effort in your life.
It’s like with everything else. Anyway, that does it for me today. I am, I am done recording stuff. It is about 4 p.m. Eastern. And I am going to go drink now. I’m going to go enjoy my day. I’ve done enough for you. Anyway, thanks for watching. Hope you learned something. See you tomorrow. Maybe. I hope not. Goodbye.
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 performance problems quickly.