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
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]
