Exam DP-300 topic 4 question 14 discussion

Estimated execution plan and Key Lookup

As per below, 2nd selection has to be "Key Lookup" (not Nested loop). The Key Lookup operator is a bookmark lookup on a table with a clustered index. The Argument column contains the name of the clustered index and the clustering key used to look up the row in the clustered index. Key Lookup is always accompanied by a Nested Loops operator. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the clustered index. The use of a Key Lookup operator in a query plan indicates that the query might benefit from performance tuning. For example, query performance might be improved by adding a covering index. https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver15

Shouldn't this be Index Seek rather than Key Lookup? A covering index would prevent the Key lookup completely, and the seek occurs before the lookup. Tuning the seek would prevent the key lookup

Box 1: Live Query Statistics Box 2: Key Lookup

Live Query/key lookup

Live statistics can be recognized from the Query Progress box top left. The nested loop operator is used wheneven no appropriate index is available for key lookup or index seek. An index is always faster, so Nested Loop means opportunity for performance improvement.

why nested loops rather than index?