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

Well, the issue is now resolved:

While it would seem logical to use filtered indexes (NOT NULL), to reduce database size and as so many sources on the web say, increase performance, the reality it seems is something else entirely.

In layman's terms, SQL Server query planner resolves even your basic inner joins without making any assumptions as to the content of the columns. Even though NULL values do not form a join, they must be included in the column index in order for query planner to use it, unless otherwise specified with predicates such as WHERE joinCol_ID IS NOT NULL. Basically, SQL Server does not use filtered indexes for joins at all, unless the queries themselves are modified to account for the filter value. Instead, it will create new statistics on these columns and / or use a clustered index scan or other indexes including the column, whichever it deems most effective. Using filtered indexes on foreign keys is therefore an absolutely horrid idea.

We still have no idea how months worth of testing this in multiple other environments never produced the same results outside of this one, single DB, but this is the way it's supposed to work. Apparently something that as far as we know is not related to cache, statistics or configurations, caused the production DB to behave differently and correctly detect and use the filtered indexes, while all of the testing environments simply used the old indexes (seeing as the indexes were dropped and recreated with the same name, this seems a valid theory even if there is no real proof).

So the lesson of the story: The web is filled with examples of how underused filtered indexes are, how awesome they can be. But this serious downside never popped up except as a nagging thought in the back of my head saying "if these are so great, then why aren't NULL values filtered out of indexes by default, since they only take up space and only serve a purpose in special circumstances"? Well, now I know why. :)

I think that the key here is the lot of updates, and the bloat on the index.

The index contains pointers to rows in the table that are no longer 'live'. These are the old versions of updated rows. The old row versions are kept around for a while, to satisfy queries with an old snapshot, and then kept around for a while more because no one want to do the work of removing them more often than necessary.

When scanning the index, it has to go visit these rows, and then notices they are no longer visible, so ignores them. The explain (analyze,buffers) statement does not report on this activity explicitly, except through the counting of the buffers read/hit in the process of inspecting these rows.

There is some "microvacuum" code for btrees, such that when the scan gets back to the index again, it remembers that the pointer it chased down was no longer live, and marks it as dead in the index. That way the next similar query that gets run doesn't need to chase it down again. So if you run the exact same query again, you will probably see the buffer accesses drop closer to what you predicted.

You can also VACUUM the table more often, that will clean the dead tuples out of the table itself, not just out of the partial index. In general, tables with a high-turn-over partial index are likely to benefit from more aggressive vacuum than the default level.