Sql Server Plan - what is a Filter after an Index operation?

Question

I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL Server to do so, instead of doing all in the Index scan?

For an example:

The index already contains all the fields used by the WHERE. I expanded the Index Scan operation, and it execute part of the where with some parameters involved (totally expected). I then expanded and inspected the Filter operation, and what it does is to apply some other filtering on a couple datetime fields already evaluated by the Index Scan, plus some other conditions on some other SP parameters.

What's going on here? I obviously don't care about my specific query, that's not the point, I'm trying to understand what conditions can cause this to happen.

Answer 1

Filter operators are often used for non-SARGable predicates, or when the predicates are too complicated to be pushed effectively to the index access operation.

In your case, I'm willing to bet that it's a series of optional predicates. These are not SARGable: Conor vs. Optional Parameters .

...plus some other conditions on some other SP parameters...

If your code is doing something like col = @var or @var IS NULL, it would fall into that category, and may result in the filter operator.