Do we have any example which is behaving as cluster index scan when ever queried with range of values. I am trying to get the conclusion on optimizer behavior as when ever the select count increases to more than 10% even the table have cluster index optimizer will take cluster index scan.

The related Query asked in below url as well. Clarification on Clustered index scan and Clustered Index Seek

  • 1
    If you are performing a range query on the clustered index key columns and aren't doing anything to make it unsargable you won't ever get a clustered index scan if that's what you are asking. Even if in practice the range works out to be 100% of the table. There is no tipping point like for a non covering NCI. May 30 '16 at 18:05
  • You are right but my question is even we are having cluster index the optimizer will choose cluster index scan or seek depend up on the cost based like row counst.... So do we have any type of script to conclude the cluster seek(using B+ tree search) vs cluster scan(without B+ tree search) using 'where' codition.
    – Narendra
    Jun 1 '16 at 23:19

Benjamic Navarez has an excellent article on how the cost-based optimizer in SQL Server works.

Essentially, when SQL Server is asked to read a table it looks at several methods of returning the requested data, and chooses the "best" path it can by assigning costs to each type of operation. It then chooses the cheapest path, or plan. This may or may not include either seeking the clustered index, scanning the clustered index, or perhaps doing seeks or scans on the non-clustered indexes, if they exist.

Since the plan is cost-based, there is no single piece of code that will always perform a clustered-index scan, except perhaps for the case of SELECT * FROM dbo.[table with a clustered index];. Even adding an ORDER BY ... clause to that statement may make SQL Server choose a different plan.

In your question you mention that your query performs a clustered index scan whenever the selected number of rows climbs above 10% of the total rows in the table. The number of rows requested versus the total number of rows in the table is part of the cost-based-optimization, and is likely causing SQL Server to think it will be faster to simply scan the entire clustered index (the clustered index is the table), rather than using a series of index seeks followed by lookups.

Look at point #2 in this article by Aaron Bertrand. Rob Farley also has a good write-up about it.

Martin Smith's answer on this question also contains some excellent details on this topic.

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .