>You can force usage of a specific index, but normally it's not a recommended approach. Is the query very complex?
>
>Are you running in development environment where you can afford to get rid of all plans?
No, I wouldn't want to force it. I want SQL Server to be smart enough and use the more optimized index.
However, this is not the first time I see this. Whenever the primary key is used in a combined index, sometimes, SQL Server will decide to simply rely on the clustered index of the primary key and use that one. However, we cannot have included fields in a clustered index. So, this is why I created another index, on the primary key, with the two included fields I need. This is necessary to avoid the Key Lookup in the execution plan.
So, I really do not know how to workaround this. It is a very particular situation.