Expensive Key Lookups

Expensive Key Lookups

We warn about these when a single Key Lookup is >=50% of the total plan cost.

A key lookup occurs when SQL uses a nonclustered index to satisfy all or some of a query’s predicates, but it doesn’t contain all the information needed to cover the query. This can happen in two ways: either the columns in your select list aren’t part of the index definition, or an additional predicate isn’t.

Key Lookups effectively join the nonclustered index back to the clustered index, on the clustered index key columns to retrieve the necessary information.

This is what a Key Lookup operator looks like. Key Lookups are always tied to Nested Loops Joins, executing once for each row that needs to be retrieved. This can be especially painful in instances of parameter sniffing, when a clustered index scan would have been more appropriate.

To find which columns are needed to fix it, hover over it and look at the output list.

In this case, we only have one column being retrieved, and this would be easy enough to fix by altering our index definition to INCLUDE it.

Some key lookups may need to retrieve additional information on a predicate, and this is what that will look like.

I’m calling out the Seek Predicate here, because this can be confusing sometimes. It’s in the first screen cap too, and this is the column that’s being used to join the two indexes together. It’s the clustered index key column. The top predicate is what SQL is going in to additionally search on. Like before, you can add this column to the index definition, either as another key column or as an included column. How you do this depends on your existing index definition.

Where this gets complicated

When you use SELECT * or equivalent queries
When you’re selecting large, or MAX column types
When your queries have multi-column predicates (5+)

You don’t necessarily want to add all or most of your table’s columns as INCLUDES, and you probably don’t want to carry around large or MAX data type files in the key of your index. In these cases, first re-examine query patterns to see if you can select fewer columns, or first narrow the number of rows you’re returning down before selecting them.

For multi-column predicates, you may want to look at using a CTE to satisfy a subset of selective predicates first, or dumping the results to a temp table, and then querying the smaller result set further.