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.

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

share|improve this question
add comment

migrated from stackoverflow.com Oct 12 '11 at 2:12

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

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