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
