From the course: SQL Server Performance for Developers

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Key lookup: When it's good and when it's bad

Key lookup: When it's good and when it's bad - SQL Server Tutorial

From the course: SQL Server Performance for Developers

Start my 1-month free trial

Key lookup: When it's good and when it's bad

“

- [Instructor] The next thing we're going to talk about in terms of query operators are key lookups. Key lookups are a specific type of operator that is typically associated with being very expensive and common when an index is missing in a SQL Server execution plan. Sometimes we can see that there's a index missing and that's associated with an index scan or an index seek, and what that means is SQL Server's going to make one operation for each row in the query plan, so these are typically very expensive operators, and they are most of the time bad, but not all key lookups are bad, and we're going to use this as an example of things to understand in general about query plans and performance tuning. So, what exactly is a key lookup, and here we have a pretty simple example of an execution plan. We have an index seek on an non-clustered index, and a key lookup against, in this case, a clustered column store index. This particular cost would be very expensive. We would have to look up…

Contents