Minimum rowcount for filtered index?

Question

Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known.

But with the advent of filtered indexes, I'm wondering if there is a minimum (or maximum) row count that you should consider too much of a performance hit.

For example, if you have 100,000 rows in a table that gets, say, 75% read operations, would it be wise to add a filtered index that only covers 500 rows? Or 100 rows? What if the filter covers 85,000 of those rows?

I think the performance implications of adding a filtered index to a read-heavy table are pretty clear. I'm just wondering if there's a natural limit to this performance.

Another way of looking at this question: If 90% of your updates aren't fitting within the filter, is the performance degraded less than it would be for a non-filtered index?

Answer 1

For example, if you have 100,000 rows in a table that gets, say, 75% read operations, would it be wise to add a filtered index that only covers 500 rows? Or 100 rows? What if the filter covers 85,000 of those rows?

That would depend on the queries against the table. If query patterns are such that they target a subset of the 500 or 100 rows that your filtered index covers, perfect. On the flip side, it's unlikely that the optimiser is going to choose a filtered index that includes 85% unless it is a covering index for a particular query.

I haven't tested this but I would expect filtered index utilisation to exhibit the same tipping point behaviour as non-filtered indexes.