Key Lookup

This article is related to Key Lookup operators and how we eliminate them to improve the performance of our query.

Introduction

Key lookup operator is a bookmark lookup on a table where clustered index exists. It is quite expensive in terms of performance, so we have to eliminate them to maintain the performance factors.

Key lookups occur when you have an index seek against a table, but our query requires additional columns that are not in that index. This causes SQL Server to have to go back and retrieve those extra columns.

In other words a key lookup occurs when data is found in a non-clustered index, but additional data is needed from the clustered index to satisfy the query and therefore a lookup occurs.  If the table does not have a clustered index then a RID Lookup occurs instead.

Generating the Key Lookup

CREATE TABLE my_emp

       (empId     INT  NOT NULL IDENTITY(1,1) PRIMARY KEY,

        empFname  VARCHAR(50) NOT NULL,

        empLname  VARCHAR(50) NOT NULL)

       

GO

 

INSERT INTO my_emp

       (empFname,  empLname)

VALUES ('Joydeep', 'Das'),            

         ('Tuhin', 'Shina'),

         ('Sangram', 'Jit'),

         ('Sudip', 'Das'),

         ('Sukamal', 'Jana'),

         ('Rajib', 'Mandal')

        

GO

        

CREATE NONCLUSTERED INDEX [IX_empLname]

ON [dbo].[my_emp] (empLname)

GO      

SELECT *

FROM   my_emp WITH(INDEX(IX_empLname)) WHERE empLname ='Das'

Execution plan:

How to eliminate Key Lookup

One way to reduce or even eliminate key lookups is to remove some or all of the columns that are causing the key lookups from the query. The common tendency of the developer is to use all the columns like "SELECT *" statements in the Query. We can easily remove some extra columns which are responsible for Key lookup.

The second thins is using "Covered Index" that satisfies the entire query or at least eliminates the key lookups.

Using sys.dm_db_index_usage_stats

This view gives us the information about overall access methods to your indexes. 
There are several columns that are returned from this DMV. Some of the helpful columns are mentioned bellow.

• user_seeks - number of index seeks
• user_scans- number of index scans
• user_lookups - number of index lookups (Key lookup)
• user_updates - number of insert, update or delete operations

The Query is mentioned bellow

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],

         I.[NAME] AS [INDEX NAME],

         SUM(USER_SEEKS) USER_SEEKS,

         SUM(USER_SCANS)USER_SCANS,

         SUM(USER_LOOKUPS) USER_LOOKUPS,

         SUM(USER_UPDATES)USER_UPDATES

FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S

         INNER JOIN SYS.INDEXES AS I

           ON I.[OBJECT_ID] = S.[OBJECT_ID]

              AND I.INDEX_ID = S.INDEX_ID

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

         AND S.object_id=object_id('my_Table')

GROUP BY S.[OBJECT_ID], I.[NAME]

Hope you like it.

Posted by: MR. JOYDEEP DAS