Unexpected !!!!!!!!!!! having CLUSTERED INDEX SEEK

Question

Unexpected !!!!!!!!!!! having CLUSTERED INDEX SEEK

Bhuvnesh

SSC Guru

Points: 59351
More actions
January 12, 2010 at 3:56 am

#96792

HI
please see attached schema and
according to below query , its having clustered index seek but my guess is it shuld have been clustered index scan.
i am having clustered index on acct_id, evt_stub, cont_stub
query
select * from invitee
where acct_id = 2000122 and responded_through_id > 2
i have also attached exec plan
please help and give explanantion
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 7 posts - 1 through 7 (of 7 total)

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

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

Your clustered index has acct_id as the leading column and the query is filtering on acct_id. Hence there will be a clustered index seek. Why would you be expecting a scan?

What will happen here is that the seek will use the clustered index to locate the rows with the matching acct_id. After that a secondary filter will be applied (it'll show up as a predicate in the index seek operator) to do the second filter on responded_through_id.

From this, and previous questions, I get the impression that you don't understand how indexes work. May I suggest an introductory series of articles on indexes?

http://www.sqlservercentral.com/articles/Indexing/68439/

http://www.sqlservercentral.com/articles/Indexing/68563/

http://www.sqlservercentral.com/articles/Indexing/68636/

There are also several index-related posts on my blog. I recommend this one: http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Thanks a lot

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

hi

i read somewhere in your blog ( missd that ) . where you have mentioned about the link

from where i can douwnload eBook of Sql server 2005 : Query tuning and Optimization

can you please post that link , i searched alot but couldnt find that.

Thanks in advance.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

I have never posted such a link because, as far as I know, there's no legal downloadable eBook of any of the Inside SQL Server series. Go and buy the book, it's well worth the money.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Gail ,

Thanks a lot , your links helped me a lot.

i am very curiuos about how index works and found very interesting them.

mean while, i am also studying "SQL_Server_Execution_Plans" written by

Grant Fritchey, its really very worth reading.

but one question, i hope you will answer it.

its not necessary that query always has clustered index seek, it will often go for Clustered index scan and also for index scan.. so which one is better and why?.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Scan = read of all of the pages of the index. If that's the cluster, it's every single page in the table. Seek = navigating directly to the rows needed using the b-tree structure of the index.

http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/

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

http://sqlinthewild.co.za/index.php/2009/07/29/is-a-scan-a-bad-thing/

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