A Little About Filtered Indexes In SQL Server
Video Summary
In this video, I delve into the intricacies of filtered indexes and how they can sometimes lead to unexpected behavior when used in conjunction with parameterized queries. I share a series of examples where I tweak a query to demonstrate how SQL Server handles these situations, highlighting both the benefits and limitations of using filtered indexes. With a touch of humor, I also sprinkle in some personal anecdotes about setting up my recording environment—complete with light-induced distortions and the meticulous alignment required for a perfect shot. This video is packed with practical insights that should help you navigate the complexities of SQL Server indexing more effectively.
Full Transcript
Blank Darling here with Darling Data. And I’ve made another gain adjustment that I think will solve all of my problems. I’m recording one more video today because I have a fetish for making my wife nervous about being late for things. That worked on too many levels. But anyway, hopefully the audio is completely kink-free this time. The only thing around here that would be kink-free. But, you know, a lot of work does go into figuring out how to get things exactly right for these recordings. And these are a lot of test runs and I appreciate all the feedback and stuff that I got. Just to give you an idea of sort of how complicated sometimes things can get, I’m going to bring up a Windows Explorer window on my desktop. Y’all can’t see it, but if I bring it up on the screen, all of a sudden I start to get a bunch of distortion down here because of the extra light from having another white window up. But as soon as I minimize it, the distortion clears up. So a lot goes into this that, I don’t know, maybe not everyone appreciates the amount, just how much tinkering and weirdness you have to deal with. But anyway, we talked about computed columns a little bit. So now I want to talk about filtered indexes a little bit because the two things often solve a lot of problems.
Either traditional columns are not having a column at all and not filtering data in your indexes and whatever. I forgot where I was going with that. I lost my train of thought because I thought of one other thing. My bathroom, you obviously can’t see it because it’s not that kind of show. But if I leave my bathroom light on, which is across the way, it also creates a bunch of distortion behind me. So very delicate setup over here. Everything has to be just right. Moon has to be in Pisces. Mars has to be in Pluto. I’m going to leave other planets out of this one. Anyway, let’s create an index because that’s what we do. We create indexes. This is a filtered index. This index has a filter where reputation is greater than 100,000. And that will give us some magical superpowers to find all the really important users in the user’s database. Stack overflow helpfully tracks our reputation, among other things, depending on which cookies you allow them to set.
And let’s run this query, which looks like a pretty simple query. And it looks like a good candidate to use our filtered index. And it is. So as we go over and look at the execution plan, we actually do use our index over here. But because this plan is simply parameterized, SQL Server throws us an erroneous error. And it tells us that we have, there’s a warning involved here. Red skies at night. We have an unmatched index. This warning typically shows up when you have a query that uses a parameter.
It’s not simple parameterized, like a stored procedure or dynamic SQL that is parameterized. And this warning will show up if you have a parameter where you could have used a filtered index because queries that use parameters can’t use filtered indexes because the parameterized execution plan has to be safe for any value that gets passed into that parameter. And depending on the filter definition, you may pass in a value for one iteration of the query that’s perfectly safe to use a filtered index. And then another time you may use a parameter value that is unsafe to use for the filtered index. A good example is a bit column. So let’s say you have an is deleted column. You typically search for values in that column that are not deleted. So is deleted equals zero. And then you have a stored procedure that runs.
The search for is deleted is a parameter. And you might look for deleted. You might look for undeleted or not deleted. Not undeleted. Undeleted would be three and that would break your bit completely. And so you might not be able to use a filtered index for every outcome of that parameter value. So that sucks. And this is another reason why I end up using this one equals select one thing over here. This is just enough gusto for SQL Server to not simple parameterize the query.
The reason why this query gets or is attempted to be simple parameterized is, well, stupid. So let’s not get into that. It’s a bad idea. So if we run this query, and it wouldn’t matter much if we did this for 10000000000 or 1000000001. Now we no longer have that warning over here and our index gets used magnificently, wonderfully, all is well in the world.
But if we change that back to 100001 or we search for a range of values within that reputation column, SQL Server will only sort of use our filtered index. In this query where we just searched for that whole greater than, equal to predicate up here. Oh, I lost my finger again.
If we search for this or we search for this range of values that are hopefully hidden by my giant head, SQL Server scans the clustered index for the first query and uses our clustered index a little bit for the second query. But in the second query plan, we actually have a lookup back to the clustered index.
And that lookup is figuring out which reputation values are less than 100999. The reason for that is because when we created our filtered index way up here, the only thing we did was tell SQL Server that all the values in this reputation field are greater than or equal to 100000.
We did not put the reputation column into the index so that SQL Server had all of the literal values that are stored in this column available to it in the index. So what happens is SQL Server knows that it can locate however many rows for this query that qualify for 100000 by scanning the filtered index that we created. And then doing a lookup back to the clustered index where the reputation column is stored to evaluate the second half of our predicate, the second half of our where clause to figure out where reputation is greater than 100999.
So let’s recreate our index a little bit. And let’s see what happens when we run some of these queries again. So all tree of these…
Did I change that down here? I didn’t. Let’s fix that in the demo. 1, 2, 3, 9, 9, 9. There we go.
I knew I forgot to do something earlier. Alright, let’s do that again. All three of these… We… Scan…
Our filtered index here, which is fine. Because… Every row in here is going to qualify… For it. SQL Server knows that every single row… In this index…
Is greater than or equal to 100000. Up here. So it doesn’t have… There’s nothing to seek to. There’s no point in seeking to these 600-something rows… Because it knows every single row in here…
Is going to qualify… For… That predicate. Right? There’s not even a predicate here. If you look at… If you look at the details of the tooltip… There’s not even a predicate applied…
Because the predicate is sort of implicit in the fact that… Every… Like our filter definition… Is our exact where clause here. So SQL Server is just like… Nope. Just scan it. 600 rows.
Who cares? For the second query… Where we look for greater than or equal to 100001… We do indeed… Get an index seek. And we find… Every single row in there.
These both return… 613 rows… But… The slight tweak to the predicate up here. So SQL Server says… Ooh! I can seek now. Which…
I don’t know. Take it or leave it. It is what it is for 600 rows. If you have… If you have performance problems with a 600 row scan… You have much, much bigger problems in your life. I promise you that.
And then of course for the final query… We do get another… Index seek. And this evaluates… Just one seek predicate.
And since our starting predicate… Would qualify the entire… Contents of the index… For the starting point of the where clause…
The only predicate we have to look for… Is where… This one is less than 100,999… Of course if we change this… To…
Look like… Our query above… Where we look for 100,001… SQL Server would… Seek completely… To all of those values… And we would apply… Both sides of the predicate. Because the first half of the predicate…
Because the first half of the predicate… Now doesn’t exactly match… The where clause… Of our filtered index. This still leaves us with… Kind of a… What I think is sort of a weird… Downside to filtered indexes.
And that is… If I’m looking for… The max reputation value… Is going to be… Over 100,000…
Right? It’s going to be… Included in that… Like… That like… That little water… That watermark… In the… In the data… But… We go and run these… SQL Server…
Says no… Not really… Can’t figure that out… Got to have a clustered index… Scan up here… We scan the entire thing… But of course… If we ask for…
The max… Value… Where it’s greater than… Or equal to 100,000… We go back to using… Our filtered index… Then I sort of get it… You know… Like…
You know… If we deleted a bunch of stuff… From our table… Or… You know… We artificially reduced… Everyone’s reputation… To some… Super low value… Or something… Cut everyone in half… Something like that…
Then that… There might be no data… In that filtered index… That plan might not be safe… To reuse… But… You would think that… If we made enough changes… Like that to the table… Then the stats update… Or the…
The statistics… The statistics… The statistics… Modification… Counter… Would… Get a new plan… Created… But… Anyway… I don’t know… It’s complicated…
It seems like… One of those things… That would just… Happen for you… Like… The max values… Have to be in this chunk… But… You know… Always and never… Anyway…
Thanks for watching… Hopefully… Again… All the kinks are out of the audio… This time around… If not… I give up… I quit… Throwing away all my computers… I’m gonna go collect rocks… Live in the mountains…
Eat varmint… Stuff like that… Regrow my… Full beard… Anyway… Thank you for watching… I’ll see you in… Another video…
Not today though… Cause… Now… Now I might actually be late…
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.