Slow query performance with Table Spool operator

-> Table Spool operator is used to store a copy of data in a worktable that can be used at a later stage in same or another part of the execution plan without having to produce the same data by calling its child operator all over again.

-> Table spool operator in SQL Server Management Studio graphical execution plan looks as below,

-> Table spool operators helps query performance most of the time by storing data in a worktable without requiring SQL Server to recompute the required data multiple times.

-> We will discuss a scenario where this operator slows down a query execution,

Create a database called JBSWIKI,

create database JBSWIKI

Create required objects,

use JBSWIKI
GO

create table Table1(
COL1 int identity(1,1),
Col2 nvarchar(max))

create table Table2(
COL1 int identity(1,1),
Col2 nvarchar(max))

create table Table3(
COL1 int identity(1,1),
Col2 nvarchar(max))

Populate the objects,

set nocount on

insert into Table1 
SELECT '+' + convert(varchar(15),convert(bigint,RAND() * 10000000000))
GO 500000

insert into Table2
SELECT '+' + convert(varchar(15),convert(bigint,RAND() * 10000000000))
GO 50

insert into Table3
SELECT '+' + convert(varchar(15),convert(bigint,RAND() * 10000000000))
GO 120

Execute below query,

set statistics time,io on
select * into #Temp_Table from Table1 where Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table2)
and Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table3)
drop table #Temp_Table
set statistics time,io off

-> As per below details this query took 6.8 seconds duration and utilized 35439 MS CPU. Logical reads performed on Table3 and Worktable is around 500009 and 1000792 respectively.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘Table3‘. Scan count 17, logical reads 500009, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable‘. Scan count 8, logical reads 1000792, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Table2’. Scan count 8, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Table1’. Scan count 9, logical reads 2514, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 35439 ms, elapsed time = 6851 ms.

-> From the execution plan, Table spool operator stands out when compared to other operators. The logical reads performed for Worktable is comparatively very high.

-> Zooming out specifically to the Spool operator in the execution plan,

-> Object Table3 has 120 rows. Table spool operator estimates and processes 25 Million rows and this explains high logical reads for Worktable.

-> Lets try removing the table spool operator from execution plan using trace flag 8690 as advised in this article.


set statistics time,io on
select * into #Temp_Table from Table1 where Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table2)
and Col2 not in (select SUBSTRING(Col2,CHARINDEX(':',Col2)+1,len(Col2)-CHARINDEX(':',Col2)) from [dbo].Table3) OPTION (QUERYTRACEON 8690)
drop table #Temp_Table
set statistics time,io off

-> Table spool operator is no longer present. But the resource utilization is really high. This query now takes 2 Minutes 16 Seconds duration and 941780 MS CPU.

Table ‘Table3‘. Scan count 8, logical reads 500000, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Table2‘. Scan count 8, logical reads 500000, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Table1’. Scan count 9, logical reads 2514, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 941780 ms, elapsed time = 136280 ms.

-> Removing Table Spool operator from execution plan using trace flag 8690 did not help.

-> I rewrote the query to use a JOIN instead of NOT IN. Below are the results,

set statistics time,io on
select a.* into #Temp_Table from [dbo].Table1 a LEFT OUTER JOIN [dbo].Table2 b ON a.Col2 = SUBSTRING(b.Col2,CHARINDEX(':',b.Col2)+1,len(b.Col2)-CHARINDEX(':',b.Col2))
LEFT OUTER JOIN [dbo].Table3 c ON a.Col2 =SUBSTRING(c.Col2,CHARINDEX(':',c.Col2)+1,len(c.Col2)-CHARINDEX(':',c.Col2)) 
drop table #Temp_Table
set statistics time,io off

-> Table spool operator is not present. The resource utilization has completely come down. This query now takes just 960 MS duration and 1 logical reads on object Table3. I dont see any logical reads for worktable at all.

Table ‘Table3‘. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Table2’. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Table1’. Scan count 9, logical reads 2514, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 4500 ms, elapsed time = 960 ms.

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.

Optimizing queries using XML in SQL Server

-> I recently worked on tuning a query that scans through XML data.
-> Below is the query,

set statistics io on
SELECT t.COL1 FROM Table1 t WHERE COL3=N'1' or COL3=N'10' or COL3=N'102'
or COL3=N'103' or COL3=N'104' or COL3=N'105' or COL3=N'106' or COL3=N'11'
or COL3=N'12' or COL3=N'120' or COL3=N'121' or COL3=N'122' or COL3=N'123'
or COL3=N'124' or COL3=N'125' or COL3=N'126' or COL3=N'127' or COL3=N'129'
or COL3=N'13' or COL3=N'130' or COL3=N'131' or COL3=N'132' or COL3=N'133'
or COL3=N'134' or COL3=N'135' or COL3=N'136' or COL3=N'137' or COL3=N'138'
or COL3=N'139' or COL3=N'14' or COL3=N'141' or COL3=N'143' or COL3=N'144'
or COL3=N'145' or COL3=N'146' or COL3=N'147' or COL3=N'148' or COL3=N'149'
or COL3=N'15' or COL3=N'150' or COL3=N'151' or COL3=N'152' or COL3=N'153'
or COL3=N'154' or COL3=N'155' or COL3=N'156' or COL3=N'157' or COL3=N'158'
or COL3=N'159' or COL3=N'16' or COL3=N'160' or COL3=N'161' or COL3=N'162'
or COL3=N'163' or COL3=N'164' or COL3=N'166' or COL3=N'167' or COL3=N'168'
or COL3=N'169' or COL3=N'17' or COL3=N'170' or COL3=N'171' or COL3=N'173'
or COL3=N'174' or COL3=N'175' or COL3=N'176' or COL3=N'177' or COL3=N'178'
or COL3=N'179' or COL3=N'18' or COL3=N'184' or COL3=N'185' or COL3=N'186'
or COL3=N'187' or COL3=N'188' or COL3=N'189' or COL3=N'19' or COL3=N'190'
or COL3=N'191' or COL3=N'192' or COL3=N'193' or COL3=N'194' or COL3=N'195'
or COL3=N'196' or COL3=N'197' or COL3=N'198' or COL3=N'199' or COL3=N'2'
or COL3=N'20' or COL3=N'200' or COL3=N'201' or COL3=N'202' or COL3=N'203'
or COL3=N'204' or COL3=N'205' or COL3=N'206' or COL3=N'207' or COL3=N'208'
or COL3=N'209' or COL3=N'21' or COL3=N'210' or COL3=N'211' or COL3=N'212'
or COL3=N'213' or COL3=N'214' or COL3=N'215' or COL3=N'216' or COL3=N'217'
or COL3=N'218' or COL3=N'22' or COL3=N'227' or COL3=N'228' or COL3=N'229'
or COL3=N'23' or COL3=N'230' or COL3=N'233' or COL3=N'234' or COL3=N'235'
or COL3=N'236' or COL3=N'237' or COL3=N'238' or COL3=N'239' or COL3=N'24'
or COL3=N'240' or COL3=N'241' or COL3=N'242' or COL3=N'25' or COL3=N'26'
or COL3=N'27' or COL3=N'28' or COL3=N'29' or COL3=N'3' or COL3=N'30'
or COL3=N'309' or COL3=N'31' or COL3=N'32' or COL3=N'33' or COL3=N'34'
or COL3=N'35' or COL3=N'36' or COL3=N'37' or COL3=N'38' or COL3=N'39'
or COL3=N'4' or COL3=N'40' or COL3=N'41' or COL3=N'42' or COL3=N'43'
or COL3=N'44' or COL3=N'45' or COL3=N'46' or COL3=N'47' or COL3=N'48'
or COL3=N'49' or COL3=N'5' or COL3=N'51' or COL3=N'52' or COL3=N'53'
or COL3=N'54' or COL3=N'55' or COL3=N'56' or COL3=N'57' or COL3=N'58'
or COL3=N'59' or COL3=N'6' or COL3=N'62' or COL3=N'64' or COL3=N'66'
or COL3=N'67' or COL3=N'68' or COL3=N'69' or COL3=N'7' or COL3=N'70'
or COL3=N'71' or COL3=N'72' or COL3=N'73' or COL3=N'74' or COL3=N'76'
or COL3=N'77' or COL3=N'78' or COL3=N'79' or COL3=N'8' or COL3=N'80'
or COL3=N'81' or COL3=N'82' or COL3=N'83' or COL3=N'84' or COL3=N'85'
or COL3=N'86' or COL3=N'87' or COL3=N'88' or COL3=N'89' or COL3=N'9'
or COL3=N'90' or COL3=N'91' or COL3=N'92' or COL3=N'93' or COL3=N'94'
or COL3=N'95' or COL3=N'96' or COL3=N'97' or COL3=N'98'
ORDER BY COL6 ,COL5
,ISNULL(COL2.value('(/row/c9)[1]', 'VARCHAR(256)'), CHAR(1)) ,COL1
set statistics io off

-> Object Table1 definition,

CREATE TABLE [dbo].[Table1](
[COL1] [varchar](255) NOT NULL,
[COL2] [xml] NULL,
[COL3] AS ([dbo].[udTable1_C4]([COL2])) PERSISTED,
[COL4] AS ([dbo].[udTable1_C2]([COL2])) PERSISTED,
[COL5] AS ([dbo].[udTable1_C8]([COL2])) PERSISTED,
[COL6] AS ([dbo].[udTable1_C1]([COL2])) PERSISTED,
[COL7] AS ([dbo].[udTable1_C3]([COL2])) PERSISTED,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[COL1] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-> Execution plan for the above query,
Blog46_1

-> Checking the “Table Valued Function” operator from the execution plan,
Blog46_2

-> We are spending most of our time reading the XML data from Table Table1.

(3227544 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table1’. Scan count 1, logical reads 2097889, physical reads 0, read-ahead reads 0, lob logical reads 424529, lob physical reads 0, lob read-ahead reads 36.

-> Lets introduce a new computed column using below code to avoid the costly read on XML column,

CREATE FUNCTION [dbo].[udTable1_C9](@COL2 XML)
RETURNS nvarchar(20)
WITH SCHEMABINDING
BEGIN
RETURN ISNULL(@COL2.value('(/row/c9)[1]', 'VARCHAR(256)'), CHAR(1))
END
GO

ALTER TABLE dbo.Table1 ADD COL8 AS ([dbo].[udTable1_C9]([COL2])) PERSISTED
GO

CREATE INDEX IX_TABLE1_C3_C6_C5_C8_C1 ON Table1 (COL3) INCLUDE (COL6,COL5,COL8,COL1)
GO

-> Executing below query. The only change to this query is that we are using the computed column in order by.

set statistics io on
SELECT t.COL1 FROM Table1 t WHERE COL3=N'1' or COL3=N'10' or COL3=N'102'
or COL3=N'103' or COL3=N'104' or COL3=N'105' or COL3=N'106' or COL3=N'11'
or COL3=N'12' or COL3=N'120' or COL3=N'121' or COL3=N'122' or COL3=N'123'
or COL3=N'124' or COL3=N'125' or COL3=N'126' or COL3=N'127' or COL3=N'129'
or COL3=N'13' or COL3=N'130' or COL3=N'131' or COL3=N'132' or COL3=N'133'
or COL3=N'134' or COL3=N'135' or COL3=N'136' or COL3=N'137' or COL3=N'138'
or COL3=N'139' or COL3=N'14' or COL3=N'141' or COL3=N'143' or COL3=N'144'
or COL3=N'145' or COL3=N'146' or COL3=N'147' or COL3=N'148' or COL3=N'149'
or COL3=N'15' or COL3=N'150' or COL3=N'151' or COL3=N'152' or COL3=N'153'
or COL3=N'154' or COL3=N'155' or COL3=N'156' or COL3=N'157' or COL3=N'158'
or COL3=N'159' or COL3=N'16' or COL3=N'160' or COL3=N'161' or COL3=N'162'
or COL3=N'163' or COL3=N'164' or COL3=N'166' or COL3=N'167' or COL3=N'168'
or COL3=N'169' or COL3=N'17' or COL3=N'170' or COL3=N'171' or COL3=N'173'
or COL3=N'174' or COL3=N'175' or COL3=N'176' or COL3=N'177' or COL3=N'178'
or COL3=N'179' or COL3=N'18' or COL3=N'184' or COL3=N'185' or COL3=N'186'
or COL3=N'187' or COL3=N'188' or COL3=N'189' or COL3=N'19' or COL3=N'190'
or COL3=N'191' or COL3=N'192' or COL3=N'193' or COL3=N'194' or COL3=N'195'
or COL3=N'196' or COL3=N'197' or COL3=N'198' or COL3=N'199' or COL3=N'2'
or COL3=N'20' or COL3=N'200' or COL3=N'201' or COL3=N'202' or COL3=N'203'
or COL3=N'204' or COL3=N'205' or COL3=N'206' or COL3=N'207' or COL3=N'208'
or COL3=N'209' or COL3=N'21' or COL3=N'210' or COL3=N'211' or COL3=N'212'
or COL3=N'213' or COL3=N'214' or COL3=N'215' or COL3=N'216' or COL3=N'217'
or COL3=N'218' or COL3=N'22' or COL3=N'227' or COL3=N'228' or COL3=N'229'
or COL3=N'23' or COL3=N'230' or COL3=N'233' or COL3=N'234' or COL3=N'235'
or COL3=N'236' or COL3=N'237' or COL3=N'238' or COL3=N'239' or COL3=N'24'
or COL3=N'240' or COL3=N'241' or COL3=N'242' or COL3=N'25' or COL3=N'26'
or COL3=N'27' or COL3=N'28' or COL3=N'29' or COL3=N'3' or COL3=N'30'
or COL3=N'309' or COL3=N'31' or COL3=N'32' or COL3=N'33' or COL3=N'34'
or COL3=N'35' or COL3=N'36' or COL3=N'37' or COL3=N'38' or COL3=N'39'
or COL3=N'4' or COL3=N'40' or COL3=N'41' or COL3=N'42' or COL3=N'43'
or COL3=N'44' or COL3=N'45' or COL3=N'46' or COL3=N'47' or COL3=N'48'
or COL3=N'49' or COL3=N'5' or COL3=N'51' or COL3=N'52' or COL3=N'53'
or COL3=N'54' or COL3=N'55' or COL3=N'56' or COL3=N'57' or COL3=N'58'
or COL3=N'59' or COL3=N'6' or COL3=N'62' or COL3=N'64' or COL3=N'66'
or COL3=N'67' or COL3=N'68' or COL3=N'69' or COL3=N'7' or COL3=N'70'
or COL3=N'71' or COL3=N'72' or COL3=N'73' or COL3=N'74' or COL3=N'76'
or COL3=N'77' or COL3=N'78' or COL3=N'79' or COL3=N'8' or COL3=N'80'
or COL3=N'81' or COL3=N'82' or COL3=N'83' or COL3=N'84' or COL3=N'85'
or COL3=N'86' or COL3=N'87' or COL3=N'88' or COL3=N'89' or COL3=N'9'
or COL3=N'90' or COL3=N'91' or COL3=N'92' or COL3=N'93' or COL3=N'94'
or COL3=N'95' or COL3=N'96' or COL3=N'97' or COL3=N'98'
ORDER BY COL6 ,COL5 ,ISNULL(COL8, CHAR(1)) ,COL1
set statistics io off

-> Below execution plan uses the index created. It no longer performs a read on the XML column,
Blog46_3

-> Execution stats below for the query,

(3227544 row(s) affected)
Table ‘Table1’. Scan count 202, logical reads 20644, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-> Reads are greatly reduced and the query performs comparably good. All good till now as far as scalar promotion is concerned. Lets look at issues that this computed column can cause,

1) All queries using Table1 will be serial after computed column with scalar function is defined on Table1.
2) Parallel rebuild on this table will not be possible if SQL Server Enterprise edition is used.
3) More space used by the computed column and indexes.
4) Many more….

-> It is very important to test with important workloads before using computed columns in production.

-> In my case the script cannot be rewritten and this query is very important for our business. Further Table1 had computed columns with scalar function before I created this, so went with this choice.

-> I would have definitely thought twice if this was the first computed column for Table1.

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

Why Bookmark Lookup is BAD?

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

Blog20_3.png

Blog20_4.PNG

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

Blog20_1.png

Blog20_2.PNG

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

Blog20_5.png

Blog20_6.PNG

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

Blog20_7

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

Blog20_8.png

Blog20_9

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