Included Index Vs Filtered Index

Question

We currently work with table named tb_tranfers. This table has 40 millions 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 applicaitons only uses the "DeletedDate Is Null" rows. All queries to this tables will have this clauses to it.

There are 15 on Indexes on this table, The missing index DMV comes us with suggesion to created indexes with DeletedDate column it in.

Would it be helpful to use Filtered Index "where deleteddate is null" on all the 11 non clusetered indexes or could it be beneficial to use deleteddate column as Included Indexes?

Thanks Thyagu

Answer 1

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.

Answer 2

If you ALWAYS use the DeletedDate IS NULL filter in your queries, then yes you will likely see a significant performance increase by adding the filter.

It's a lot less pages for the engine to check to find the relevant rows, which means less IO (and more speed).

Adding it as an INCLUDED field would be useless. Since you are always including it in the filter, (but probably never adding it to the SELECT list), that field would never be referenced if you add it as an INCLUDE.

Answer 3

Since 85-90% of rows have DeletedDate are NULL, it is unlikely that the selectivity of an index consisting only of DeletedDate would be sufficient for SQL to use this index. However, used in conjunction with other more selective columns, appending DeletedDate to existing indexes should give some benefit if it increases the overall selectivity of the index.

As JNK says, DeletedDate would be of little use in covering indexes unless you use it in the SELECT clause.

Given that there is already more storage for indexes than for data, you might also want to see if there is any redundancy in the existing 15 NC indexes.