Query Execution Plan

Question

Query Execution Plan

Ashok Teotia

SSCrazy

Points: 2407
More actions
May 31, 2011 at 3:20 am

#250973

Hi Everybody,
Can please helpto understand the below question
I have a table and when i execute a query against it and check query execution plan i find there is a table scan performed on the table even though i am using index columns in the where clause, Please refer below script. Thanks a lot in advance.
create table abc(id int, name varchar(20),address varchar(250))
insert into abc
select 1,'a','z'
union
select 2,'b','y'
union
select 3,'c','x'
union
select 4,'d','w'
union
select 5,'e','v'
union
select 6,'f','u'
union
select 7,'g','t'
union
select 8,'f','s'
create index idx_abc_name on abc(name,id)
select id,name,address from abc where name='a' and id=3
Regards
Ashok

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

The query optimizer calculates the cost for a table scan to be less that an index seek+RID lookup (the address column). The reason is the low number of rows that the table contains. Try inserting more rows, and you'll eventually see a index seek+RID lookup.

If you force the query to use your index, and compare the cost with the table scan, you will see that the estimated cost for table scan is lower than for the one with index seek.

On my dev server, the cost is 0,0065select id,name,address from abc with(index (idx_abc_name)) where name='a' and id=3

And this gives a cost of 0,0032select id,name,address from abc where name='a' and id=3

Thats why (I guess).

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

Create Clustered index and check the result

Clustered Index seek must be there in the execution plan

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Syed Jahanzaib Bin hassan (5/31/2011)
Create Clustered index and check the result

Unless he makes the address column the clustered index, that will have no effect. The index will still not be covering.

Clustered Index seek must be there in the execution plan

Why?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

try it,it will work

create table abc(id int, name varchar(20),address varchar(250))

insert into abc

select 1,'a','z'

union

select 2,'b','y'

union

select 3,'c','x'

union

select 4,'d','w'

union

select 5,'e','v'

union

select 6,'f','u'

union

select 7,'g','t'

union

select 8,'f','s'

create clustered index idx_abc_name on abc(name,id)

select id,name,address from abc where name='a' and id=3

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Sure, but that's a completely different thing to what the OP was doing. The rules for when SQL will do a clustered index seek are different to a nonclustered index seek. The clustered index is always covering and so the 'tipping point' doesn't come into consideration

However just saying 'change the index to a cluster' is not helpful, you can't make all indexes on a table clustered and it does nothing for understanding why SQL chooses not seek on an index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 7

Thanks all for their valuable comments,

Hi Syed Jahanzaib Bin hassan,

Can you please share logic behind it, why it happens, I mean different execution plan for non clustered and clustered index in the same situation. Reply will be highly appreciated .Thanks again.

Regards

Ashok

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ashok.faridabad1984 (5/31/2011)
I mean different execution plan for non clustered and clustered index in the same situation. Reply will be highly appreciated .Thanks again.

Please go and read the article I referenced in my first post here. It explains and shows exactly why SQL's ignoring the nonclustered index.

Also note that it's near-impossible to draw meaningful conclusions of behaviour on just 8 rows. Put a few thousand rows into the table at least and then play and see how things work. With more rows in the table you'll see that for very small row counts (<1% of the total in the table) SQL will do a nonclustered index seek (and key lookups) and for larger row counts it will switch to a table scan.

Also note that you should (on dev boxes only) clear the proc cache on each run to see different behaviours

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 9

Hi ,

Please explain then why execution plan is index seek for the below query in the same situation ,index created on the table is still non clustered :

select name,id from abc where name='a' and id=3

it has nothing to do with

"With more rows in the table you'll see that for very small row counts (<1% of the total in the table)

SQL will do a nonclustered index seek (and key lookups) and for larger row counts it will switch to a table scan"

Regards

Ashok

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

ashok.faridabad1984 (5/31/2011)
Hi ,
Please explain then why execution plan is index seek for the below query in the same situation ,index created on the table is still non clustered :
select name,id from abc where name='a' and id=3
it has nothing to do with
"With more rows in the table you'll see that for very small row counts (<1% of the total in the table)
SQL will do a nonclustered index seek (and key lookups) and for larger row counts it will switch to a table scan"
Regards
Ashok

That's all CLEARLY explained, demonstrated and proven with test data and code in Gail awesome blog post. So please do yourself a big favor and RTFM (fine).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

ashok.faridabad1984 (5/31/2011)
Please explain then why execution plan is index seek for the below query in the same situation ,index created on the table is still non clustered :

Please read the blog post I referenced in my first post.

Your new query is covered by the index, so no key lookups are required. The first query you gave was not covered by the index, so key lookups were required.

it has nothing to do with
"With more rows in the table you'll see that for very small row counts (<1% of the total in the table)
SQL will do a nonclustered index seek (and key lookups) and for larger row counts it will switch to a table scan"

It has everything to do with that statement. That is precisely what you're seeing, but the row count in the table is so low that even 1 row is above the tipping point where a scan is more efficient than a seek + lookup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 12

Thanks a lot , I would like to feel regretted if you felt bit pain, I am really sorry for that.

Thanks a ton, I got the point... 😎