DP-300 · Question #179
Hotspot Question You have an Azure SQL database. You have a query and the associated execution plan as shown in the following exhibit. Use the drop-down menus to select the answer choice that…
The correct answer is The performance issue stems from the Key Lookup operator. = Yes; The performance issue can be resolved by adding include columns to the nonclustered index. = Yes. A Key Lookup operator in an execution plan indicates that SQL Server is using a nonclustered index to find rows but then must go back to the clustered index (base table) to retrieve additional columns not included in the nonclustered index - this is a well-known performance…
Question
Exhibits
Answer Area
- The performance issue stems from the Key Lookup operator.Yes
- The performance issue can be resolved by adding include columns to the nonclustered index.Yes
Explanation
A Key Lookup operator in an execution plan indicates that SQL Server is using a nonclustered index to find rows but then must go back to the clustered index (base table) to retrieve additional columns not included in the nonclustered index - this is a well-known performance bottleneck, especially when it affects many rows. The resolution is to add the missing columns as INCLUDE columns to the nonclustered index (using CREATE INDEX ... INCLUDE (col1, col2)), which makes the index a 'covering index' that satisfies the query entirely without the costly Key Lookup operation. This eliminates the extra round-trip to the clustered index and significantly reduces I/O and logical reads.
Topics
Community Discussion
No community discussion yet for this question.