Sometime Index SCAN Operation outperform Index SEEK

Here I will try to stimulate an example where we will see Index scan outperform Index seek. But before that let me define theses two term.

Index Scan: Scan touches every row in a table, whether or not it qualifies the where predicate. It is good if your table is small or most rows qualifies where predicate.

Index Seek: Whereas Seek only touches those rows and pages which qualifies where predicate. It is efficient when you have big table and only a small set of data qualifies your predicate.

Lets come back to our problem statement, to stimulate the situation where index scan outperform we are creating a table with five column.

create table Tab_ScanVsSeek

(id int identity(1,1) primary key,

col1 varchar(300) not null default newid(),

col2 varchar(300) not null default newid(),

col3 varchar(300) not null default newid(),

col4 datetime not null default getdate())

go

Command(s) completed successfully.

Table is ready, lets insert some good amount of records in it.

set nocount on

go

insert into Tab_ScanVsSeek default values

go 100000

Beginning execution loop

Batch execution completed 100000 times.

select * from Tab_ScanVsSeek

Now here we are creating non clustered index on col1,col2,col3 leaving col4 out of index.

create nonclustered index ix_ScanVsSeek_Col1Col2Col3 on Tab_ScanVsSeek(col1, col2, col3)

go

Command(s) completed successfully.

Now lets fire the query to get the records where col1 starts with ‘a’. and see the statistic of the query.

set statistics io on

select *

from Tab_ScanVsSeek

where col1 like 'a%'

set statistics io off

Table 'Tab_ScanVsSeek'. Scan count 1, logical reads 1702, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So we can see Logical read is 1702 and in the execution plan below we can see even though we have non cluster index on col1, the index didn’t participate in query execution.

Now lets force the query to use the NON CLUSTERED index we created on the column col1, and see the result in term of logical read.

set statistics io on

select *

from Tab_ScanVsSeek with(index = ix_ScanVsSeek_Col1Col2Col3)

where col1 like 'a%'

set statistics io off

Table 'Tab_ScanVsSeek'. Scan count 1, logical reads 19352, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here we saw the Logical reads increased to 19352, which s extreme big number compare to previous approach. If you see execution plan Non cluster Index comes into play, and if dig more on Execution plan, the cost increased due to Key Lookup operator. So we found in this situation Index scan perform better.

Now lets execute both the query in single batch and find which index take less cost compare to other

set statistics io on

select *

from Tab_ScanVsSeek

where col1 like 'a%'

set statistics io off

set statistics io on

select *

from Tab_ScanVsSeek with(index = ix_ScanVsSeek_Col1Col2Col3)

where col1 like 'a%'

set statistics io off

So here we saw, from batch execution of both query at a time that query1 take cost of only 18% of total batch execution whereas Index seek takes 82% high cost. So the conclusion is in this example index seek outperform index scan.

Coder cannot afford to lose temperament; this is the heart and soul of every programming! J

 

Post Reference: Vikram Aristocratic Elfin Share