Click on the banner to learn about and purchase my database training on Azure

SQL Server and Azure SQL Database: How to Identify Key Lookup occurrences through the plancache

This post is the 10 part of 10 in the series. Performance tuning
Reading Time: 5 minutes

Speak guys!
In this article I would like to share with you a script of Kendal Van Dyke to identify/find Key Lookup occurrences through the plancache, which can be very useful to easily identify possible good candidates for a performance analysis.

As you know, KeyLookup occurrences usually have a very big performance impact and can be easily fixed by creating/changing indexes.

What is and how to avoid Key Lookup and RID Lookup

As I already explained in the article Understanding Index Functioning in SQL Server, when a query is performed on a table, the SQL Server query optimizer will determine the best data access method according to the statistics collected and choose the one with the lowest cost.

As the clustered index is the table itself, generating a large volume of data, the lowest cost non-clustered index is generally used for the query.

This can generate a problem, because, many times, the non-clustered index will be used to search the indexed information (Index Seek NonClustered), but not all the columns requested in the select are part of the index.

When this happens, the clustered index will also have to be used to return the remaining information, using the pointer to the exact position of the information in the clustered index (or the ROWID, if the table does not have a clustered index).

SQL Server - 2 Key Lookup

This operation is called a Key lookup, for tables with clustered index or RID Lookup (RID = Row ID) for tables that do not have clustered index (called HEAP tables) and by generating 2 read operations for a single query, should be avoided whenever possible.

Key lookup
SQL Server - 2 Key Lookup Execution Plan

RID Lookup
SQL Server - RID Lookup Execution Plan 2

To avoid KeyLookup just use the Covering Index technique, which consists of adding to the NonClustered Index (INCLUDE) the main columns that are used in the table queries. This makes the query optimizer able to get all the information by reading only the chosen index, without having to read the clustered index as well.

However, close attention should be paid to index modeling. It is not recommended to add all table columns to the nonclustered index as it will become so large that it will no longer be effective and the query optimizer may even decide not to use it and prefer the Index Scan operator, which does so. sequential reading of the entire index, impairing query performance.

To avoid RID Lookup, simply create the clustered index on the table and pay attention to any Key Lookup events that may arise.

Important: I strongly recommend reading the article Understanding Index Functioning in SQL Server and also the articles that are part of the series Performance tuning.

How to Identify/Find Key Lookup occurrences through the plancache

Now that you understand what a Key Lookup is and how to fix it, let's now learn how to quickly and easily identify these events.

Method 1: Using a simple LIKE
This query below is relatively simple, doing just a query on some DMV's and a textual query using LIKE in the execution plan to look for the expression Lookup=”1″ inside the XML. The result is usually very accurate, but false positives can appear.

Result:

Method 2: Using XPath in the execution plan XML
This second method is much more complete, returning more information and using XPath formulas to navigate within the XML of the execution plan and return data more accurately and correctly.

Result:

Using one of the above queries, you will easily identify the queries that are presenting the Key Lookup operator. Now that you know what it is and how to solve it, you can analyze whether it is worth creating an index to cover this query or not (Increased Disk Space + Increase in Write Time vs. Read Improvement).

Don't forget to also review the Missing Indices from your environment. To learn more about this, read the article SQL Server - How to identify all missing indexes (Missing indexes) of a database.

A big hug and until next time!