locked
Is index seek always better than index scan RRS feed

  • Question

  • When the num of rows in a table is small(almost 3000 rows ), the optimization choose index scan not of index seek, is this situation normal?
    Wednesday, May 9, 2012 7:10 AM

Answers

  • Hi Catherine,

    An index scan is where SQL server reads the whole of the index looking for matches. The time this takes is proportional to the size of the index.

    An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records. Time taken is only proportional to the number of matching records.

    In general an index seek is preferable to an index scan (when the number of matching records is proportionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the total number of records in your table.

    Note however that in certain situations an index scan can actually be faster than an index seek, usually when the table is very small, or when a large percentage of the records match the predicate.

     

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Thursday, May 17, 2012 8:55 AM
    Thursday, May 10, 2012 4:42 AM
  • When the num of rows in a table is small(almost 3000 rows ), the optimization choose index scan not of index seek, is this situation normal?

    This is very common -- especially if the index is a simple index and the query requires a lookup for each row processed through an index seek.  What happens is that if the selectivity for the target index is not sufficient, more I/O is required to do the lookup after the seek than might be required to do a scan; in this situation the scan can be a better query plan than the combination of (1) index seek plus (2) lookup.

    This does not, however, mean that the index scan is optimal.  More discussion is required to figure out what is optimal.  You need to supply (1) the query, (2) the ddl of the table and (3) the current query plan that is being used; more information is probably also needed, but let's start with these three items.

    • Proposed as answer by Naomi NEditor Thursday, May 10, 2012 8:26 PM
    • Marked as answer by Maggie Luo Thursday, May 17, 2012 8:55 AM
    Wednesday, May 9, 2012 12:54 PM

All replies

  • Hi Catherine,

    I think seek is better.

    But its depend on the execution plan.

    An Index seek means that you are looking for specific value(s) and the index  provides you with the best way to do it. This also applies to specific ranges of data.As Lowell described it's very fast.

    suppose you need to find ID=7 by seek you can directly goes to that pointer

    An Index scan isnothing but the entire table/index is read in. A scan isn't always bad, because it it might just mean that the data is organized the way your query needs it .

    Did you read this blog

    http://blog.sqlauthority.com/2009/01/18/sql-server-difference-between-index-scan-and-index-seek/

    Wednesday, May 9, 2012 7:33 AM
  • How much rows does SELECT return? 

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 9, 2012 7:49 AM
  • When the num of rows in a table is small(almost 3000 rows ), the optimization choose index scan not of index seek, is this situation normal?

    This is very common -- especially if the index is a simple index and the query requires a lookup for each row processed through an index seek.  What happens is that if the selectivity for the target index is not sufficient, more I/O is required to do the lookup after the seek than might be required to do a scan; in this situation the scan can be a better query plan than the combination of (1) index seek plus (2) lookup.

    This does not, however, mean that the index scan is optimal.  More discussion is required to figure out what is optimal.  You need to supply (1) the query, (2) the ddl of the table and (3) the current query plan that is being used; more information is probably also needed, but let's start with these three items.

    • Proposed as answer by Naomi NEditor Thursday, May 10, 2012 8:26 PM
    • Marked as answer by Maggie Luo Thursday, May 17, 2012 8:55 AM
    Wednesday, May 9, 2012 12:54 PM
  • In addition to Kent's reply it really depends as there are cases when Index Scan is definitely better than Index Seek

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 9, 2012 1:15 PM
  • Hi Catherine,

    An index scan is where SQL server reads the whole of the index looking for matches. The time this takes is proportional to the size of the index.

    An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records. Time taken is only proportional to the number of matching records.

    In general an index seek is preferable to an index scan (when the number of matching records is proportionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the total number of records in your table.

    Note however that in certain situations an index scan can actually be faster than an index seek, usually when the table is very small, or when a large percentage of the records match the predicate.

     

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Thursday, May 17, 2012 8:55 AM
    Thursday, May 10, 2012 4:42 AM