1

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:

enter image description here

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.