Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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