>If we want to avoid key lookup, we want to create covering index (e.g. the index that will include all the columns used in the query). This usually provides the optimal performance for the query, but it makes a wide index and so DML operations suffer.

We had seen operations on DML taken over a minute just to update a single field in our table. I do not know if this is related to those index which combines and includes several fields.

One thing interested however. On the test server, I was doing the same query over and over again and it was always taking about a minute to return about 139 records. So, to be sure all the included fields were not a factor, I removed all of them but the primary key of the main table. So, the select was only including one field. It was as slow. Then, I removed a join statement and it went fast. Then, I put it back and now it is always fast. So, it seems a join statement is causing the problem. Now, as to know why it was fast after is probably because of caching issue.