Execution plan - index scan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Execution plan - index scan

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-08 : 10:43:28

select id, name, address
from students
where id=599

Assumption :
1. table alreday rebulid & update ststistics.
2. id colum is clusterd index.

But still its showing in execution plan index scan.

so how it will show index seek in execution plan or how it will run effectivley.

Regards,
avijit

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-08 : 11:03:01

Can you post the table definition (CREATE TABLE statement) and the index definitions please?

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:19:09

use covering index or add as included columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-08 : 11:32:30

If ID is a clustered index, then it's already covering.

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-08 : 11:41:41

is the table really small?

If the table is small enough then an index scan would be the quickest way?

Edit What process did you go through to isolate this as the bottleneck?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-08 : 22:51:24

Thanks .
Transact Charlie:
table size is really big. it has near about 1 million rows.

visakh16: id column is integer data type with size 20 max.
Is it will be effective if i introduce include column.

Regards,
avijit

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2010-03-09 : 00:14:52

This trick might help for you.
http://blogs.msdn.com/queryoptteam/archive/2006/04/12/575241.aspx

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-09 : 01:02:23

thanks for ur link

Regards,
avijit

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 04:08:04

quote:

visakh16: id column is integer data type with size 20 max.
Is it will be effective if i introduce include column.

What do you mean by that?

Can you post the sql to create the table?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-09 : 06:35:18

i mean how it will affective becoz i hv no idea.

Regards,
avijit

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 06:45:57

I meant this:

quote:

integer data type with size 20 max.

????

That doesn't make any sense in TSQL.

Can you *please* post the create script for the table.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-09 : 07:12:37

Table and index creation scripts please. Integers are 4 bytes in SQL, they can't be any other size.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources