>>Do you have a problem with optimizer picking up that plan? How did you come up with the alternative index - was it suggested as missing index when you run this query through DTA?
>
>The execution plan clearly shows a Key Lookup (Clustered) with the object being used as the primary key and the Output list to be the two fields I need. So, the primary key is the clustered index. That one cannot have included fields. At first, I thought I would simply create the related index so to avoid a Key Lookup and move into an Index Seek for example or something better. But, when it was built, the execution plan still does not want to pick it up. I have created a new index on the primary key field with the two suggested included fields.
>
>I am using SSMS and the related execution plan option.
I suggest to drop that index for now, run the query in SSMS and see what it will suggest as missing index.
In addition, it may be a good idea to analyze this query with SQL Sentry Explorer as it provides more options and it's clearer.
If it's not broken, fix it until it is.
My Blog