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:

Key lookup is caused because the engine chose to use an index that doesn’t contain all columns you are trying to fetch. So the index is not covering the columns in the select and where statement.

To eliminate the key lookup you need to include the columns missing (the columns in the Output list of the key lookup) = ProducerContactGuid, QuoteStatusID, PolicyTypeID and ProducerLocationID or another way is to force the query to use the clustered index instead.

Note that 27 Non clustered indexes on a table may be bad for performance. When running an update, insert, or delete, SQL Server must update all indexes. This extra work may negatively affect performance.

You forgot to mention the volume of data involved in this query. Also why are you inserting into a temp table? If only you need to display then don't run an insert statement.

For the purposes of this query, tblQuotes does not need 27 non-clustered indexes. It needs 1 clustered index and 5 non-clustered indexes or, perhaps 6 non-clustered indexex.

This query would like indexes on these columns:

QuoteStatusID
ProducerLocationID
ProducerContactGuid
EffectiveDate
LineGUID
OriginalQuoteGUID

I also noticed the following code:

DATEDIFF(D, @EffDateFrom, tblQuotes.EffectiveDate) >= 0 AND 
DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <= 0

is NON Sargable i.e. it cannot utilize indexes.

To make that code SARgable change it to this:

tblQuotes.EffectiveDate >= @EffDateFrom 
AND  tblQuotes.EffectiveDate <= @EffDateFrom

To answer your main question, "why you are getting a key Look up":

You are getting KEY Look up because some of the column which is mention in the query are not present in a covering index.

You can google and study about Covering Index or Include index.

In my example suppose tblQuotes.QuoteStatusID is Non Clustered index then i can also cover DisplayStatus. Since you want DisplayStatus in Resultset. Any column which is not present in an index and is present in resultset can be covered to avoid KEY Look Up or Bookmark lookup. This is an example covering index:

create nonclustered index tblQuotes_QuoteStatusID 
on tblQuotes(QuoteStatusID)
include(DisplayStatus);

**Disclaimer :**Remember above is only my example DisplayStatus may be covered with other Non CI after analysis.

Similarly you will have to create index and covering index on the other tables involved in the query.

You are getting Index SCAN also in your plan.

This can happen because there is no Index on the table or when there is a large volume of data the optimizer may decide to scan rather than perform an index seek.

This can also occur due to High cardinality. Getting more number of rows than require due to faulty join. This can also be corrected.