SQL Server Plans : difference between Index Scan / Index Seek

sql sql-server sql-execution-plan
cindi picture cindi · Jul 16, 2009 · Viewed 92.1k times · Source

In a SQL Server Execution plan what is the difference between an Index Scan and an Index Seek

I'm on SQL Server 2005.

Answer

Justin picture Justin · Jul 16, 2009

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 (see http://mattfleming.com/node/192 for an idea on how this works) - 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 proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
  • Note however that in certain situations an index scan can be faster than an index seek (sometimes significantly faster) - usually when the table is very small, or when a large percentage of the records match the predicate.

Related questions

How do I obtain a Query Execution Plan in SQL Server?

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?

Read More
Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"

I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me: 1. Run both and time each query 2. Run both and get "Query Cost" from the actual execution plan Here is …

Read More
How to improve performance on a clustered index seek

I'm trying to improve the performance on a query that is running very slowly. After going through the Actual Execution Plan; I found that a Clustered Index Seek was taking up 82%. Is there any way for me to improve the …

Read More