Included Column versus Filtered Index

We currently work with table named tb_tranfers. This table has 40 million rows and is ~26 GB in size (11 GB data, 15 GB indexes).

10 to 15% of the rows are soft-deleted rows (DeletedDate is not null). The applicaiton only uses rows where DeletedDate is null. All queries to this table will include a clause to that effect.

There are 15 indexes on this table. The missing index DMV contains suggestions to create indexes with DeletedDate as an included column.

Would it be helpful to use a filtered index WHERE DeleteDdate IS NULL on all 11 nonclusetered indexes? Or would it be better to have the DeletedDate column as an included column?

Yes, modifying all 11 NC indexes to be filtered indexes (CREATE INDEX ... ON ... WHERE DeletedDate IS NULL) would help. This way you get two advantages:

• The Query Optimizer will know that any row coming from these indexes already does satisfy your query filters on DeletedDate, so it won't have to lookup the clustered index to double check the DeletedDate

• All NC indexes will be 10-15% smaller in size, requiring less memory and fewer IO operations to lookup.

The trade off is that any query that looks after soft deleted rows (and there have to be some query, otherwise why do the rows exist?) will be unable to use these NC indexes.

StackExchange Database Administrators Q#6726, answer score: 12