Sql-server – Eliminate Key Lookup (Clustered) operator that slows down performance

Key lookups of various flavors occur when the query processor needs to obtain values from columns that are not stored in the index used to locate the rows required for the query to return results.

Take for example the following code, where we're creating a table with a single index:

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    Table1ID int NOT NULL IDENTITY(1,1)
    , Table1Data nvarchar(30) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Table1ID);
GO

We'll insert 1,000,000 rows into the table so we have some data to work with:

INSERT INTO dbo.Table1 (Table1Data)
SELECT TOP(1000000) LEFT(c.name, 30)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Now, we'll query the data with the option to display the "actual" execution plan:

SELECT *
FROM dbo.Table1
WHERE Table1ID = 500000;

The query plan shows:

The query looks at the IX_Table1 index to find the row with Table1ID = 5000000 since looking at that index is much faster than scanning the entire table looking for that value. However, to satisfy the query results, the query processor must also find the value for the other columns in the table; this is where the "RID Lookup" comes in. It looks in the table for the row ID (the RID in RID Lookup) associated with the row containing the Table1ID value of 500000, obtaining the values from the Table1Data column. If you hover the mouse over the "RID Lookup" node in the plan, you see this:

The "Output List" contains the columns returned by the RID Lookup.

A table with a clustered index and a non-clustered index makes an interesting example. The table below has three columns; ID which is the clustering key, Dat which is indexed by a non-clustered index IX_Table, and a third column, Oth.

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    ID int NOT NULL IDENTITY(1,1) 
        PRIMARY KEY CLUSTERED
    , Dat nvarchar(30) NOT NULL
    , Oth nvarchar(3) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat);
GO

INSERT INTO dbo.Table1 (Dat, Oth)
SELECT TOP(1000000) CRYPT_GEN_RANDOM(30), CRYPT_GEN_RANDOM(3)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Take this example query:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

We're asking SQL Server to return every column from the table where the Dat column contains the word Test. We have a couple of choices here; we can look at the table (i.e. the clustered index) - but that would entail scanning the entire thing since the table is ordered by the ID column, which tells us nothing about which row(s) contain Test in the Dat column. The other option (and the one chosen by SQL Server) consists of seeking into the IX_Table1 non-clustered index to find the row where Dat = 'Test', however since we need the Oth column as well, SQL Server must perform a lookup into the clustered index using a "Key Lookup" operation. This is the plan for that:

If we modify the non-clustered index so that it includes the Oth column:

DROP INDEX IX_Table1
ON dbo.Table1;
GO

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat)
INCLUDE (Oth);        <---- This is the only change
GO

Then re-run the query:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

We now see a single non-clustered index seek since SQL Server simply needs to locate the row where Dat = 'Test' in the IX_Table1 index, which includes the value for Oth, and the value for the ID column (the primary key), which is automatically present in every non-clustered index. The plan: