When you consider adding nonclustered indexes, you must weigh the improvement in retrieval time against the increase in data modification time. In addition, you need to consider these questions:
How much space will the indexes use?
How volatile is the candidate column?
How selective are the index keys? Would a scan be better?
Are there a lot of duplicate values?
Because of data modification overhead, add nonclustered indexes only when your testing shows that they are helpful.
