Introducing Key Lookups, finding them in execution plans, and resolving them
Key Lookup is a bookmark lookup on a table with a clustered index. Key Lookup is used by SQL Server while retrieving information regarding non-key column. All the queries that use non-clustered index wouldn't have Key Lookup but all Key Lookup occurrences are accompanied by a non-clustered index. One more thing to remember is that Key Lookup always enjoys the company of Nested Loop operator.
Getting ready
We are going to create a table to see different effects of Key Lookup operator in execution plan. In order to generate the case of Key Lookup, we need two essential things to be present on the table:
- Clustered index
- Non-clustered index
When you have predicate based on key ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
