11

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?

Improve this question

3 Answers 3

Reset to default
12

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.

Improve this answer
7

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.

Improve this answer
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.