A Little About Filter Operators In SQL Server Query Plans
Thanks for watching!
Video Summary
In this video, I delve into the importance of filter operators in query plans and why you shouldn’t ignore them. Erik Darling from Darling Data shares insights on when these operators are expected to appear and when they might indicate performance issues. I cover various scenarios where filters can be seen as normal, such as with halving clauses or windowing functions, but emphasize that a high number of filter operators generally suggests potential problems. The video also highlights examples like non-sargable predicates and max data types, explaining how these can lead to unnecessary row filtering late in the query plan, which is inefficient. Throughout, I provide practical advice on how to identify and address performance issues related to filter operators, including testing different query permutations and considering alternative join methods for better optimization.
Full Transcript
Erik Darling here with Darling Data, and today we’re going to talk about why you shouldn’t ignore filter operators in query plans. Now, I’ve done a few videos where I sort of touched on the filter operators. I mean, not like actually touched on the filter operators. Guy could go to jail for that. But just where I sort of talked a little bit about them and sort of like sometimes why they show up, sometimes when they’re bad. So I wanted to do one kind of specifically about filter operators, why you might see them and when you might see them and when you should pay attention to them and also like sort of how to fix queries where you see them and the queries are slow. Now, in general, filter operators are not a good sign. We’re going to talk about when they’re sort of expected sign and when they’re a bad sign. But in general, when I see query plans with lots of lots of filters in them, I get a little nervous. Mostly because, you know, filter operators are a lot of filter operators are a lot of filters in them. So, you know, filter operators are SQL Server removing rows from a result set at some point in a query plan, right? But really, you don’t want to see rows get removed from a query plan like over here. You want to see rows get removed from a query plan like as close to when you touch an index as possible. Ideally, as you’re actually touching the index, right? Like when you seek into an index, when you scan an index, that’s when you want to see rows get removed from a query plan.
SQL Server filtering out the rows. You don’t want those rows carried over through other operations and then removed later, right? You want to work with a minimal set of rows as possible. Grammatically, that seems daft. That was a bad sentence. You want SQL Server to deal with as few rows as possible as early as possible, right? You want to remove as many rows as possible as early in the query plan, as far to the right in a query plan as you can. As soon as you start removing queries, as soon as you start removing rows far to the left in a query plan, you have started to fail from a performance perspective.
So, when you would expect to see filters in query plans, here are a few examples. One is when you use a halving clause. And since halving clauses work on aggregates, you have to figure out exactly which rows meet that requirement. No rows are going to meet this requirement. That’s totally okay. I just need to show you a query plan where, you know, the SQL Server doesn’t know how many rows are going to meet that count predicate from the halving clause, right?
Where count big is greater than 2.1 billion, blah, blah, blahs. Well, you have to fully execute that query and do that count to figure out which rows would actually qualify for that. So, when you use a halving clause, you can rightly expect to see a query plan with a filter in it to filter out those rows after that expression has been calculated.
Another sort of similar idea is when you’re filtering out on like a windowing function because you have to, at runtime, like again, just like a count big, at runtime, you have to execute that windowing function, come up with what that windowing function expresses itself out to over the results that you’re asking it to express over and filter that out later. So, here’s another example of a query plan with a filter in it.
And this filter is totally reasonable because we need to generate that row number and figure out where it meets the predicate applied to it, right? That row number has to get calculated just like the count big. Similarly, you might see, so, this one’s a little tricky to explain because there are, you know, some caveats and, you know, other things to consider with it.
But just to make it, give you an easy example, if you play tricks on SQL Server to get batch mode into your queries, like create, like my example over here where I’m creating a temp table with a columnstore index on it, and I’m doing this sort of silly join to that table in here, this will generally get SQL Server to do something batch mode-y at some point in the query plan. But like I’ve talked about in other videos, this does not get you batch mode on rowstore the way the batch mode on rowstore feature gets you batch mode on rowstore.
This might get you a few operators to operate in batch mode, but it is not the full batch mode on rowstore experience. So, you end up with this query plan that’s a mix of row and batch mode operators, and batch mode bitmaps are not really tightly integrated into that. So, you’ll end up with a query plan that looks a little something like this, where you have a filter over here, and that filter comes from a batch mode bitmap, right?
Because if you look over here at this hatch join, hash, hash join, not hatch join, we are not hatcheting anyone. This hash join does execute in batch mode, and it does produce a bitmap, right? If you right-click on this, where is that thing?
Batch, yes, define values, opt bitmap. So, this is a hash join where there is a bitmap involved, and that bitmap gets applied, but not over here. We’re like, you know, a row mode bitmap, wow, row mode bitmap, we’re not even going to repeat that one.
Row mode bitmap, you would normally see applied when you scan or seek in a clustered index. In this case, though, it is not applied over here, it is applied in a filter operator after the fact, which can actually be kind of crappy for performance if you’re scanning really big tables.
Another one that I’ve covered in a previous video is you might see filter operators for startup expression predicates. Now, you can ignore the sort of red squiggles in here, because this is just a statement from a store procedure that I pulled out. This is the store procedure that I pulled it out from, I just wanted to show you what it looks like, where there’s sort of this conditional join logic.
So, if we are going to check the post table, then we actually do this join, or if we are going to check the comments table, then we actually do this join. And what that looks like from the query plan perspective is something like this, where you have one, two filter operators in the query plan.
I’m not covering up either of them, good for me. But if you look at the filter operator, you have this startup expression predicate. So, if check posts is true, then we will touch the post table, or if check comments is true, then we will touch the comments table over here, which I am blocking a little bit, but that’s okay.
This filter operator, you can still mostly see what we care about in here, right? So, these are some reasons why you might see sort of expected filter operators. These aren’t necessarily good, bad, ugly, somewhere in between for performance, but they are things that I would generally keep an eye on, and just, you know, make sure that I understand why they’re showing up.
And especially in the case of the startup expression predicate, I would definitely want to test this store procedure like I did in the video that I recorded for you, which I’ll put a link to in the show notes here.
I would definitely want to check this, any query that does something like this, with different permutations using, or reusing a query plan for check post equals one, and then check common SQL is one, because you can run into some really tricky performance issues when you do things like this.
Once again, anything you do that makes your job easier makes SQL Server’s job harder. These are words to live by when you are developing in T-SQL, or probably just any even unhyphenated SQL.
So, of course, times when you will see filters in query plans, because you did something necessarily silly, would be if you wrote a completely non-sargable predicate.
Say, we’re going to actually just, you know, we’re going to run these two queries together, and mondu, I did not semicolon that query. Let’s, you know what, let’s do a little plug for my friend SQL prompt, and let’s say, let’s insert semicolons so that we make sure we don’t have that problem again.
Thank you, SQL prompt. This video is now unofficially sponsored by SQL prompt, which can help you insert your semicolon. I often lovingly and jokingly refer to SQL prompt as my $75 a year semicolon inserter.
You can, if you feel that was maybe a not exactly family-friendly name for it, well, that’s on you, that’s not on me. So, for these two queries, the first one, of course, we are doing something awful with our lives.
We are wrapping not one, but two columns in a date diff function. And, you know, this is generally not a great way to write the query. And I’ll show you the difference between these two.
Now, even though we are going to break the sargability rules a little bit here, and we are going to have at least these columns in a non-sargable sort of fashion in the where clause here, we should see a pretty decent improvement in query speed by doing more work in writing some additional stuff out.
So, this top query up here, of course, we have a filter. Way later in the query plan, we fully scan the post table. We fully scan our index on the post table.
That’s not really the point here. The point here is that in the bottom query plan, if we look at the nonclustered index scan here, we have absolutely no predicate applied to it at all.
All of the rows from the post table are filtered out way over here, right? This predicate greater than five. If we do things a little bit differently, we still end up with an index scan, but we reduce all the rows at the index scan here.
Now, granted, this is still not fantastic. We are still scanning the index. We are not seeking into the index. Partially, this is because of the way the index is designed, because we have the owner user ID column that we’re joining on first.
We don’t have the creation date or last activity date column prioritized in the index for seeking. It’s okay here, right? Not every index can be absolutely perfect.
Excuse me. But, you know, at least writing the query in a slightly more sane way, right, where we at least have some clean expression on the creation date and last activity date columns, even though we do something non-sargible with them over here, which, you know, again, not great, but at least tolerable in this point, we actually get a pretty decent speed up on this query.
You know, not a huge, awful thing anyway. This was 1.6 seconds, almost 1.7 seconds, and this was just under a second. So we shaved a pretty okay amount of time off this query just by typing a little bit more. Again, we made our lives a lot harder.
We typed a lot more stuff. We had to do a lot more work. But when we did it the easy way, we made SQL Server’s job harder. And the harder you make SQL Server work, the angrier your end users are.
All right. Again, words to live by. Another time you might see that in a sort of similar way. And again, this is a matter of surgability.
If you were looking to figure out if a column meets a certain length requirement, wrapping that column in a function can be pretty bad. Really, all you need to do is see if that column contains any character in it, right?
And the underscore wildcard is any single character. So if we run these two queries, again, sort of a similar setup. The user’s table is very small.
It’s only a couple million rows. So you’re not going to see a dramatic performance improvement here. But in real life, in your actual queries, you might find this sort of change in querying to be very helpful. So if we look at these two.
Oops, that did not drag where I wanted it to. Curse you. Damn your eyes, SQL Server Management Studio. Why won’t you give me the draggy thing? If we look at these two queries, of course, the one where we use data length and the where clause results in a filter after scanning the entire user’s table for all the rows.
In this one here, we don’t have that filter operator, right? We have a like of a single underscore character, which is, again, wildcard for any character. They both get the same results. And, you know, again, because the user’s table isn’t huge, there’s not a terrible time difference on this.
But in real life, you can probably see more dramatic results with, you know, the queries that you’re working on tuning, right? So, you know, this is generally bad up here. This is generally a better way to figure out if a column meets a certain length requirement.
Another time when you’ll see filters is when you make bad join decisions. Again, if you go through any, you know, sort of SQL, you know, code camp thing, they’ll only show you the first way of figuring out rows in a table that don’t exist in another table.
And it’s doing a left join from one table to another and then figuring out where the primary key or keys of the column that you’re left joining to are null. Now, again, this isn’t going to be a gigantic speedup, but it’ll be noticeable.
Where the top query with the left join, we have to fully join users to posts, right? We do absolutely no filtering here.
We do all the filtering much later on over here, right? And that filter operator is, of course, asking for where the ID column in the post table is null, right? So this filter operator is usually a pretty bad sign.
This won’t always, always, always, always, always, a thousand million billion percent be the case. There will, of course, be times where, you know, you might test the same query paradigm two different ways, right?
You might test it with the left join, searching for the null. You might check with not exists. Depending on indexing and a number of other things, you might find that the left join is faster.
It’s totally happened to me in my life where I was just like, I know how to make this better. And then it did not get better, at least not without some, you know, indexing magic and stuff.
So definitely two ways to check on here. But, you know, whenever I see a filter operator in a left join type query like this, I get nervous and I want to try, at least try the rewrite using not exists instead of the left join thing.
Another thing that can cause filter operators to show up in your query plans are when you are dealing with max data types. This goes for column data types and this also goes for parameter or variable data types.
Now, I have a recompile hint on the query over here because I want you to, what I want you to see is that just declaring the variable as max means that SQL Server can do nothing helpful for you.
Right? So this and this query up here, the about me column is an envarchar maximum. We can see that right here. And if we want to search on that column, we’re going to have a filter operator in our query plan.
And unfortunately, there is no helping this. You could create a computed column that is a substring of like a reasonable search index for your query, right?
Like you might not need to search all 2.0 bajillion gigs of the bytes of data in this column. You might only need the first like 100 or even if you made this an envarchar 4000, which is a decent chunk of stuff to be able to search through, this would be okay.
A lot of people might say, oh, full text indexes, but full text indexes in SQL Server stink. They’re awful. Don’t even bother. You’re better off using like Elasticsearch or something.
Once you start thinking about full text indexes in SQL Server, you should mentally just jump right to Elasticsearch because that will actually be better for you than anything else.
And again, if we define our parameters and variables with reasonable data types, we will not see filter operators in our query plans. We will see regular index seeks and scans like in this one down here.
This one is where the location variable is declared as an envarchar 100, which matches the length of the location column. And this one up here, where I made location in envarchar max, we filter that data out later.
Even with a recompile hint, SQL Server can’t like sniff the fact that we only had, that the length of our local variable was four bytes. It was here, or I guess eight bytes because it’s unicode.
SQL Server can’t figure that out, right? So SQL Server can’t just discard the maxness of the one up there. It has to, it has to, you know, fully scan whatever table, apply that, and apply that envarchar max filter later.
Another time when it’s particularly bad, and I’m going to start this running because this is kind of an amusing one, is when you put UDFs of nearly any variety in your join or where clause.
Now, you, so let’s limit this at first to non-inlineable scalar UDFs because, you know, SQL Server 2019, at least, you know, if you, you know, have all the right incantations, will attempt to inline some scalar UDFs.
There are a lot of restrictions on it. There are a lot of caveats to it. And performance isn’t always better. Sometimes you might start seeing eager index pools and query plans where SQL Server has rewritten your scalar UDFs as inline versions.
That just means you stink at indexing and you need to fix something. But in this case, you know, you could also see this with multi-statement table-dialued functions, inline table-dialued functions.
You are far less inclined to see issues like this. You still might, but very, again, you have to really screw something up within the inline table-dialued function to make that happen.
So here are two queries, the top 175 and the top 180. And what’s kind of funny is if we look at how long each of these ran for, when we, for the top 175, we do okay.
All right? We still had to read a bunch of rows here, but not nearly as many as we read down here. And what’s very funny about all this, right, is that this query took three, oh, sorry, I messed up highlighting on that.
This query took about four seconds. This query took 34 seconds. All right? And why? I’m going to tell you why. This is very funny. This is kind of like a good, this is a good, like, like, level 500 DBA row goal joke.
This top query found if I lift up this arm and I zoom in correctly and appropriately and I don’t mess anything up. Ah, gosh darn you.
We get 175 rows back. Good on us, right? That’s the one that took four seconds. If you look at this query, well, you might, I’m going to get things lined up down here.
I’m going to lift up my arm. I’m going to scroll over and keep scrolling over and there we go. We are at 179 rows. So I asked for the top 180, but only 179 rows qualify.
So we, we, we wasted a lot of time trying to meet, trying to find that 180th row. That’s why, in the query plan for this one, we could actually get done pretty quickly because we only needed 175 rows.
In this one, where we asked for one more row that actually existed, we needed to have all 2.4 million rows come out of the user’s table to search for that elusive 180th row, but we did not find it.
And that is why we had so much more work done in here. Uh, and this filter operator, the filter operator for both of these is going to be our scalar UDF. Now, I know you’re going to, you’re going to say something, uh, about putting scalar UDFs in computed columns.
And maybe you might even say something about schema binding. Uh, if you’re the type of person who puts scalar UDFs in computed columns, you deserve to be smacked with everything that someone can find within reasonable reaching, reasonable reaching distance and, and smacked with.
Whether it’s a fish or rubber hosing or, uh, you know, a chair or something like that. Because sticking computed columns, sticking scalar UDFs in computed columns will, will doom you to a lifetime of, of query misery.
Um, they will force every single query without like, you know, trace, you can use trace lag 174 to get around this sometimes. Uh, but in general, every single query that touches your table will be forced to run single threaded.
Uh, it, you will have a bad time with all sorts of indexing and maintenance tasks. Uh, you will have a bad time with DBCC, check DB, unless you’re just running in a physical only. A lot of things get bonked up.
That also goes for computed columns and, uh, check constraints, sorry, scalar UDFs and check constraints. So, scalar UDFs and check constraints and computed columns. again, uh, you know, think the immortal words of 50 cent.
We will, we will get the strap. All right. another thing that will cause, uh, filter operators to appear in your query plans. Now, uh, this, this door procedure called Longingly is available at my GitHub repo.
Uh, it really only has one use and that is to, I’m going to show you what it does in the debug mode for a small number of loops. Uh, what it does is, um, it will, uh, declare, uh, like however many, whatever you set for loops, that’s how many, uh, uh, variables it will declare.
Right? So, I chose five, so we have five variables in here. Uh, it’ll also generate this select count from users where reputation is less than zero or where display name is in however many, uh, parameters we decide to create and then the query that it executes final at the end is all those declares with the, uh, with the final statement.
Right? So, fun times there. Now, the execution plan for this one is useless. We don’t need to look at it, but the execution plans for these are a little bit more interesting because what we’re going to see is that when we only search for 14, uh, of those, we will have a clustered index scan and we will filter out rows at the cluster index scan.
Clustered index scan. I, I can’t remember if I said that right the first time, but this is basically what that looks like where reputation is less than zero or display name is in, or display name equals blah, blah, blah, blah, blah up through parameter 14.
Right? We can see parameter 14 right there or P14, which is, I guess it’s a local variable because I declared it. As soon as we hit 15, SQL Server says, this is far too complicated for me.
You have made my job hard. I’m going to use a filter. I’m going to scan the whole table and we are going to apply the filter later. So again, we’re bringing all the rows from, uh, the, from the user’s table.
Every single one, we apply no predicate here. We apply the entire predicate in this filter. So where reputation is less than zero or, uh, we are in, well, I mean, you can see P15 there.
So at least, you know, for whatever value of complexity we have in this query, uh, 15 in, in, in clause arguments was where SQL Server went, I’m done.
You might see this with fewer, uh, in clause arguments. You might see this with more in clause arguments. I don’t know. It depends on a lot of, it depends on a lot of things, but, this is just another example of where SQL Server might throw a filter operator at you when you were expecting those rows to get eliminated a lot earlier.
Again, performance for this particular example isn’t disastrous because, you know, again, user’s table, pretty small. We’re not doing a ton of work. Uh, we don’t have, like, a much bigger query where we did lots of other joins and stuff and then that filter got applied way later.
Just a simple example so you can kind of see what I mean. So, that brings us to the end of, of my examples. Uh, I’m gonna hit save there. Oh, no, I’m gonna hit, I think I hit the wrong button.
We’re gonna hit save there to make sure that I retain all my semicolons. Uh, once again, thank you to Redgate SQL Prompt for inserting semicolons for me. Automatically.
Is that DevOps? I don’t know. We’ll find out. It’ll be fun. So, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. Uh, if you like this video, uh, I like thumbs.
I like comments. You can leave me, I mean, you know, good, bad, or ugly. I don’t care. Comments in general. Anything that I can respond to, right? Uh, we like to, we like to generate, um, we like to generate, lots of, uh, interaction, right, on these videos.
the more interaction I get, the better. Even if, even if you call me a fat dummy. But then I’ll come find you. I don’t care where you live. I got, I got, I got, I got, I got Delta Miles.
Boy, howdy. I can, I can come find anyone. Uh, uh, if you like this sort of SQL Server content, you can subscribe to my channel. There’s a, there are buttons that you can push, uh, where you can get notified.
And I’m going to make sure that I have the most, um, up to date count. Uh, so you can join 4,009. I finally crossed the 4,000 mark. I’m going to, I’m going to overtake a few Amiga repair channels at some point.
Uh, you can join 4,009, uh, data darlings, uh, and, and getting notified when, when I, well, actually, you know, I mean, some people might have subscribed and turned notifications off.
I got Delta Miles for you too. All right. Um, so we’ll, we’ll, we’ll get that done. All right. Anyway, uh, I think that’s about good here.
I’m going to get this uploaded and scheduled and all that good stuff. Uh, thank you for watching. Uh, all, all of my data darlings. I, I appreciate you. Oh, so very much.
All right. Goodbye.
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.