O'Reilly logo

Microsoft SQL Server 2012 Performance Tuning Cookbook by Bihag Thaker, Ritesh Shah

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required