• To me this is fine and the optimizer is doing it what seems to be an efficient way. In first case, the optimizer knows that it has to update the depending indexes as well. Hence, the optimizer feels that it is better to get [RuTestBatchStatus] and [UserStatus] values from the clustered index with key lookups and later perform the updates in clustered index and the dependent index i.e. IX_tblBIUsers_OCU which is the only index where values of [RuTestBatchStatus] and [UserStatus] are to be changed.

    In second case, when you did add [RuTestBatchStatus] and [UserStatus] columns as included columns in the IX_tblBIUsersRumbaPegasusMap index, the optimizer knows the values of the [RuTestBatchStatus] and [UserStatus] columns in advance. Hence, no key lookups were required. But then an additional cost is added of updating the IX_tblBIUsersRumbaPegasusMap itself. So the number of indexes to be updated increased from 2 to 3. I hope I am making sense to you 😉