patternsqlModerate
Clarification on Clustered index scan and Clustered Index Seek
Viewed 0 times
clusteredseekclarificationscanandindex
Problem
I have created one table with 80000 records with
Showing Cluster Index Scan, So this is correct.
Execution plan shows ClusterIndex Seek, So this is correct.
Execution plan still shows ClusterIndex Seek why?
As per the statistics the row count is 90% then the optimizer have to use 'ClusterIndex Scan' but it's using 'Cluster Index Seek' Why? All statistics are up to date and I have checked the Estimated row and actual rows are same then why the optimizer choose 'Cluster Index Seek' For getting 90% of rows among total records.
Cluster Index on CustomerID column. When I queried the following statements the execution plan behaves in differently. Can anyone clarify the same.select * from customerShowing Cluster Index Scan, So this is correct.
select * from customer where CustomerID=80000Execution plan shows ClusterIndex Seek, So this is correct.
select * from customer where CustomerID between 1 and 70000Execution plan still shows ClusterIndex Seek why?
As per the statistics the row count is 90% then the optimizer have to use 'ClusterIndex Scan' but it's using 'Cluster Index Seek' Why? All statistics are up to date and I have checked the Estimated row and actual rows are same then why the optimizer choose 'Cluster Index Seek' For getting 90% of rows among total records.
Solution
Execution plan still shows ClusterIndex Seek why?
The initial seek down the b-tree is to find the first row where
From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon as a row is encountered that does not match the predicate
The effect is that the 'seek' is an initial seek, followed by a partial ordered scan of the index.
This is usually more efficient than scanning the whole index, even where 90% of the rows are expected to qualify. The key point to get straight in your mind is that a separate b-tree seek is not performed for each row.
The initial seek down the b-tree is to find the first row where
CustomerID >= 1.From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon as a row is encountered that does not match the predicate
CustomerID <= 70000.The effect is that the 'seek' is an initial seek, followed by a partial ordered scan of the index.
This is usually more efficient than scanning the whole index, even where 90% of the rows are expected to qualify. The key point to get straight in your mind is that a separate b-tree seek is not performed for each row.
Context
StackExchange Database Administrators Q#139905, answer score: 10
Revisions (0)
No revisions yet.