For a search with many predicates or joins, some trial
and error might be required.
The creation of the right index can be an iterative process that
involves the following steps:
- Creating an index
- Running statistics
- Testing the performance of the query and validating that the query
execution plan uses the new index.
Too many indexes can slow performance of inserts and updates. Drop
indexes that are not used in any query execution plan for the searches
that are issued against the tables involved.
Important: Database vendor tools such as design advisors
can be used, but vendor tools can suggest incorrect indexes, so always
follow the techniques that are presented here.