Table scan is happening inplace of nonclusetered index seek

Question

Table scan is happening inplace of nonclusetered index seek

vamshikrishnaeee

SSCrazy

Points: 2519
More actions
August 9, 2010 at 2:11 am

#236458

HI,
I am running below update query on my database.
I have one nonclusterd index on job_id,is_biz_rule_driven,biz_rule_id and
another nonclusterd index on detail_id,type_id,perspective_id,scenario_id,effective_period_id of user_override_indep_job_tree table.
update user_override_indep_job_tree
set uid_pk = fdd.uid_pk , fdd_row_exists = 1
from user_override_indep_job_tree uojt
join forecast_detail_data_b fdd
on fdd.detail_id = uojt.detail_id
and fdd.type_id = uojt.type_id
and fdd.perspective_id = uojt.perspective_id
and fdd.effective_period_id = uojt.effective_period_id
where
uojt.job_id = 1
and uojt.is_biz_rule_driven = 1
and biz_rule_id = 1
When I ran this query tablescan is happening on user_override_indep_job_tree table. The columns uid_pk, fdd_row_exists are not included.Even though they are not included lookups has to occur.But table scan is happening.I am not understanding this!
Please help me on this.
Thanks,
Vamsy

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

The query is probably returning too many rows for the lookups to be considered a good option. If that query is returning more than about 1% of the table, SQL won't chose to seek on a noncovering index.

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

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 2

vamshikrishnaeee

SSCrazy

Points: 2519

August 9, 2010 at 3:42 am

#1204035

Thanks Gail.

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Also make a note that table scan is not always BAD!! As mention by Gilamonster SQL Server is smart enough to select best execution plan for your query :-).

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 4

I have one more question on indexes

Let say I have one table 'test' with columns fid,fname,faddress,cid,cname,caddress with

one nonclustered index 'f' on fid with fname,faddress included and another nonclustered 'c' on cid

with cname,caddress included.

Now if I ran a query like:

select fid,fname,cid,cname from test

where fid=1 and cid=100

1. Now which index is selected 'f' or 'c' ?

2. If f is selected then what about cid,cname. I am thinking that lookups will occur for cid and cname ?

3.As these columns(cid,cname) are also indexed is there any performance improvement?

Please clarify this.

Thanks,

Vamsy

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

vamshikrishnaeee (8/9/2010)
1. Now which index is selected 'f' or 'c' ?

Depends which of fid and cid is more selective. There's no covering index here so it depends which one SQL thinks is better (will return fewer rows)

2. If f is selected then what about cid,cname. I am thinking that lookups will occur for cid and cname ?

Yes. In the case of cid, a lookup then a filter

3.As these columns(cid,cname) are also indexed is there any performance improvement?

Maybe. Depends if you're planning to widen an index or add a new one.

Please take a read through this series on indexing - http://www.sqlservercentral.com/articles/Indexing/68439/ (3 parts)

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