July 2, 2002 at 4:50 pm
What is the difference between a clustered index seek and scan? I'm assuming that a seek is preferred for performance, how can I make sure that by sprocs are doing this as much as possible?
thx
July 2, 2002 at 9:16 pm
Clustered Index Scan = Table Scan (just against a clustered data structure as opposed to a heap structure).
Clustered Index Seek is where it's only hitting part of the overall table structure, not the whole thing.
In order to maximize clustered indexes, there are a few general rules:
Ensure there is a high degree of selectivity in your clustered indexes. For instance, if there's a field or collection of fields that is very diverse, this a good idea. However, if the only two choices are 'Y' and 'N', a scan is almost certain.
Keep statistics up-to-date SQL Server has autostats, but if you aren't using SQL Server to maintain the statistics automatically, make sure you are updating them on a schedule which reflects how often data is changing.
Try to use clustered indexes for range queries.
A bit more from Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 3, 2002 at 8:10 pm
Hi
But isnt that the key benefit from clustered indexes? where high selectivity is not necessarilya key requirement, especially for range scans? the Y,N one is fair enough but certainly (eg. date) range scans are ideal. In this case you may still get a poor selectivity value but the benefit is you are full index scanning over a range of ordered leaf nodes.
A tad off topic I know 😉
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 12, 2002 at 10:37 pm
In a clustered scan the data is read sequentially from the top until the actual data is reached. You will tend to see these with likes. However a seek utilizes the leaf pages like an address book tabs. It finds the tabs for the data it needs and can jump to the pages faster so it is far more efficient.
A clustered index is best utilize on a column with high uniqueness and no nulls, even though it doesn't have to be.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 13, 2002 at 5:57 am
Keep in mind that the final query plan is built based on all indexes. Hopefully you have one or more other indexes that will refine the rows, then you can use the clustered index to leverage it's range friendly action. Not sure I agree that it's "best utilized" on a highly unique column. A better expression might be "most effective" maybe? For any index the more unique the better/faster it works. I usually fall back on the range part - common for me to use the cl index on date columns where I'll be using between a lot. Anything other than a pure Y/N column!
Andy
July 13, 2002 at 11:31 am
I agree with that change. Hard to always come up with the right statement exactly.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply