This is one of the blog post in a series of posts about optimizing SQL Server queries. A list of blog posts about query tuning can be found here – https://jbswiki.com/2021/04/15/sql-server-query-optimization-introduction/

-> As per article “https://technet.microsoft.com/en-us/library/ms180920(v=sql.105).aspx“. The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.

-> Create below database and objects,

create database JB_BookMark
go

use JB_BookMark
go

create table Table1(
Col1 int primary key identity(1,1),
Col2 int,
Col3 int,
Col4 varchar(100))
CREATE NONCLUSTERED INDEX IX_Table1 ON dbo.Table1(Col2)

-> Populate the table using below tsql query,

set nocount on
declare @i int=1
declare @j int=65
while (@i<=100000)
begin
if(@j=91)
begin
set @j=65
end
insert into Table1 values(@i,rand(@i*9)*1000,replicate(CHAR(@j),5))
set @i=@i+1
set @j=@j+1
end

-> Rebuild the indexes to remove any fragmentation,

USE [JB_BookMark]
GO
ALTER INDEX [PK__Table1__A259EE54D34C8103] ON [dbo].[Table1] REBUILD
GO
USE [JB_BookMark]
GO
ALTER INDEX [IX_Table1] ON [dbo].[Table1] REBUILD
GO

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=1
set statistics time off
set statistics IO off

-> The query cost is at 0.0065704. The operator “Index seek” returns 1 row(s).  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is just 1 time. Logical reads done is around 4. The query overall took 49 MS to complete.

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=10
set statistics time off
set statistics IO off

-> The query cost is at 0.0327403. The operator “Index seek” returns 10 rows.  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is 10 times. Logical reads done is around 22. The query overall took 62 MS to complete.

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=100
set statistics time off
set statistics IO off

-> The query cost is at 0.291569. The operator “Index seek” returns 100 rows.  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is 100 times. Logical reads done is around 217. The query overall took 370 MS to complete.

-> It seems like whenever the execution plan for a query has key lookup, its performance degrades with the number of rows that it has to process. In our case, below is the final test result,

-> We can create below index to avoid key lookup,

USE [JB_BookMark]
GO
CREATE NONCLUSTERED INDEX [IX_Table1_1]
ON [dbo].[Table1] ([Col2])
INCLUDE ([Col1],[Col3],[Col4])
GO

-> Execute the below query after the above index is created with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=100
set statistics time off
set statistics IO off

-> Once we remove the key lookup, the query completes in 1 MS with fewer cost and logical reads.

-> So this means, we can create covering index for all queries that has key lookup? Creating covering index will remove the key lookup and the performance of the query will definitely increase, but it will also increase the space used by the index, database maintenance time, backup size. So it is wise to test things before taking a decision.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.