February 6, 2003 at 3:03 pm
Hi,
I have a clustered index on a table. The table has got around 15000 rows.
I did a Show Execution Plan on simple "SELECT * From <tablename>" query and it is basically doing a CLustered Index Scan whereas I expect it to do a Clustered Index Seek.
What might be going wrong here ?
Kindly help as all of my tables have clustered indexes on Primary keys.
Thanks Much.
February 6, 2003 at 6:35 pm
Can you tell me about the table and the index? It is hard to help when I don't know anything.
Also, what was the query that did a Scan?
February 6, 2003 at 9:20 pm
It used to be, that we tried not to create clustered indexes on primary keys if they were simply incrementing numbers, such as an identity column. Sql server's architecture adjusts for this a lot in current versions, but I've found that though the hot spots of disk activity are reduced, there are still concurrency issues with that methodology, just FYI....
And though I agree with Antares about More input, I think in your case, the performance you observed is normal. When you perform a select from Table, your selecting all rows. With no where clause, your not delimiting your data, and your selecting a full range. Hence the index scan. Were you to be selecting based on a criteria of the clustered index, I would expect the seek, picking out specific rows rather than a range. Unless of course, you were selecting based on a between, or something similar to cause SQL to need a range again.
February 7, 2003 at 4:00 am
Ahh, I didn't catch that previously. Was look at this late lastnight. Yes a straight SELECT with no conditions is going to perform a Scan. It will look for the first record and traverse the index to the last record and of course the index related to table structure is the Clustered one, this is why it is sorted on output based on the clustered index.
If you instead placed a WHERE CICOl = x then it would start at the Root page of the Clustered index, work thru the leafs doing key lookups until it gets to the data. In otherwords it actively is seeking to find the data based on the criteria.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply