SQL SERVER – Optimize Key Lookup by Creating Index with Include Columns

I love query tuning and performance tuning projects. In one of the recent Comprehensive Database Performance Health Check my client had a very interesting scenario where their queries were running pretty fast earlier but gradually started to run slow and eventually started to take over 10 minutes to execute. They reached out to me to tune their slow running query. While looking at the query execution plan, there is a big key lookup operation which was actually slowing down their query. I decided to create an index with include column on that table and resolve this issue.

Let us see how I did that with a real-world example.

First, let us create a test table.

USE TempDB
GO
-- Create a Sample Table
CREATE TABLE TestTable (ID INT, FirstName VARCHAR(100), LastName VARCHAR(100))
GO

Next, populate the test table with sample data.

-- Insert Sample Data
INSERT INTO TestTable (ID, FirstName, LastName)
VALUES(1,'Pinal', 'Dave')
GO
INSERT INTO TestTable (ID, FirstName, LastName)
VALUES(1,'Mike', 'Smith')
GO 10000

Right after that create one clustered index and non clustered index on the table.

-- Create Clustered index on column ID
CREATE CLUSTERED INDEX IX_TestTable_ID ON TestTable (ID)
GO
-- Create nonclustered index on column FirstName
CREATE NONCLUSTERED INDEX IXTestTable_FirstName ON TestTable (FirstName)
GO

Following that run our query with enabling actual execution plan. You can enable the actual execution plan by going into SSMS toolbar >> Query >> and “Include Actual Execution Plan”. Alternatively, you can also enable that by pressing CTRL + M.

Now you are ready to run the query. Right after running the query, open the execution plan.

-- Run a sample with execution plan CTRL + M
SELECT FirstName, LastName
FROM TestTable
WHERE FirstName = 'Pinal'
GO

In the execution plan, you will notice that there is a key lookup in addition to usage of the index. SQL Server has to do key lookup when the index does not have all the necessary columns which query is requesting.

In this case you have two options 1) Either alter the original index and include the columns which are visible in the Output List. 2) Create a brand new index including the columns from the output list in the include part of the index.

-- Create a covering index with include column
CREATE NONCLUSTERED INDEX IXTestTable_FirstName_Include ON TestTable (FirstName)
INCLUDE(LastName)
GO

Just for simplicity, I will be creating a new index in our example. Remember creating too many indexes on the table can slow down your index-related operations to multi-fold.

Right after creating the index, let us run the same query once again and check the execution plan.

-- Run a sample with execution plan
SELECT FirstName, LastName
FROM TestTable
WHERE FirstName = 'Pinal'
GO

You can see in the execution plan there is no more key lookup and query also uses our newly created index.

After fixing the key lookup for my customer the query started to run from 11 minutes to 12 seconds. If you are also seeing a lot of key lookup in your query, you can follow my advice to tune your query.

Here is the script to clean up the object which we have created.

-- clean up
DROP Table TestTable
GO

There are many similar tricks to tune your query we often discuss in our SQL Server Performance Tuning Practical Workshop.

Reference: Pinal Dave (https://blog.sqlauthority.com)