Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.

What is the difference between and index seek and an index scan?

Which performs better?

How does SQL choose one over the other?

I realise this is 3 questions but I think answering the first one will explain the others.

share|improve this question
3  
You have a nice reference on use-the-index-luke. –  Marian May 20 '13 at 8:42
3  
Not all scans are bad - sometimes it is the most efficient way to satisfy the query. Also note that not all seeks are seeks - often they are actually range scans, and the seek only indicates how it got to the start of the range. –  Aaron Bertrand May 20 '13 at 13:36
    
If the query is running more slowly than you would expect you might want to look at msdn.microsoft.com/en-us/library/ms181034%28v=sql.105%29.aspx and see if you need to refresh the statistcs or query plan so that it scans/seeks in the right places. –  George May 20 '13 at 16:58
    
@George - thanks for the link. Query is now running ok when I added some indexes (it was doing table scans). Some of the table scans became index seeks and others index scans so just wanted to clarify the difference –  Greg May 20 '13 at 20:53

4 Answers 4

up vote 15 down vote accepted

Short version: seek is much better

Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows in the affected table.

It helps to cover the whole family for data finding operations to fully understand the performance implications.

Table Scans: With no indexes at all that are relevant to your query the planner is forced to use a table scan meaning that every row is looked at. This can result in every page relating to the table's data being read from disk which is often the worst case. Note that for some queries it will use a table scan even when a useful index is present - this is usually because the data in the table is so small that it is more hassle to traverse the indexes (if this is the case you would expect the plan to change as the data grows, assuming the selectivity measure of the index is good).

Index Scans with Row Lookups: With no index that can be directly used for a seek is found but an index containing the right columns is present an index scan may be used. For instance if you have a large table with 20 columns an index on column1,col2,col3 and use issue SELECT col4 FROM exampletable WHERE col2=616, in this case scanning the index to query col2 than to scan the whole table. Once matching rows are found then the data pages need to be read to pickup col4 for output (or further joining) which is what the "bookmark lookup" stage is when you see it in query plans.

Index Scans without Row Lookups: If the above example was SELECT col1, col2, col3 FROM exampletable WHERE col2=616 then the extra effort to read data pages is not needed: once index rows matching col2=616 are found all the requested data is known. This is why you sometimes see columns that will never be searched on, but are likely to be requested for output, added to the end of indexes - it can save row lookups. When adding columns to an index for this reason and this reason only, add them with the INCLUDE clause to tell the engine that it doesn't need to optimise index layout for querying based on these columns (this can speed up updates made to those columns). Index scans can result from queries with no filtering clauses too: SELECT col2 FROM exampletable will scan this example index instead of the table pages.

Index Seeks (with or without row lookups): In a seak not all of the index is considered. For the query SELECT * FROM exampletable WHERE c1 BETWEEN 1234 AND 4567 the query engine can find the first row that will match by doing a tree-based search on the index on c1 then it can navigate the index in order until it gets to the end of the range (this is the same with a query for c1=1234 as there could be many rows matching the condition even for an = operation). This means that only relevant index pages (plus a few needed for the initial search) need to be read instead of every page in the index (or table).

Clustered Indexes: With a clustered index the table data is stored in the leaf nodes of that index instead of being in a separate heap structure. This means that there will never need to be any extra row lookups after finding rows using that index no matter what columns are needed [unless you have off-page data like TEXT columns or VARCHAR(MAX) columns containing long data]. You can only have one clustered index for this reason, so if you use one chose where you put it carefully in order to get maximum gain.

share|improve this answer

Generally, seeks are good, scans are bad.

Seeks are where the query is able to make effective use of the index, and use it to find the rows it needs.

Scans are where the query is looking through the whole index trying to find what it needs.

How does SQL choose? Deep in the internals of the query optimiser, the decision is made based on your query and the indexes available and the statistical information associated with those indexes.

There are a few books to read that might be of interest here - Both from the Red-Gate bookstore at http://www.red-gate.com/community/books/

  • SQL Server Execution Plans by Grant Fritchey
  • Inside the Query Optimizer by Benjamin Nevarez
  • SQL Server Statistics by Holger Schmeling
share|improve this answer
4  
For the same plan a single table scan is good, a million seeks is bad. So your first statement is not entirely correct. –  Marian May 20 '13 at 8:36
    
Good point, well made. I'll make an edit. –  Thomas Rushton May 20 '13 at 8:37

If you wish to dig the subject, a very helpful book (at least for me) is SQL Server Execution Plans by Grant Fritchey, freely available at RedGate here.

If you have a query such as

SELECT *
FROM myTable

SQL Server will likely use an Index scan, as it needs to go through all the rows to display the required results.

On the contrary,

SELECT *
FROM myTable
WHERE myID = 1

will certainly result in an Index seek. SQL Server will use the B-tree structure of the myID index and retrieving the proper line will be much faster.

share|improve this answer
    
I don't know if I agree with "certainly" - even if an index has myID as a leading column, a seek may not be the optimal answer (depends on a lot of things, such as whether it is unique - which may be true in the customers table but not for customerID in the orders table, how many columns need to be covered but aren't in the index, etc). –  Aaron Bertrand Nov 18 '13 at 22:29

Others have defined well enough the differences between seek and scan. In this instance, your query itself and the execution planner should give you the information you need to see which values are used as predicates (filters) for the query in each part. Typically it's a good practice to always add non clustered indexes on foreign keys, and depending on the use cases in the program code, you might want to look into creating additional multi-column indexes or included column indexes as well. With the terminology presented here, a google search will give decent results on examples on each.

But as an example, say your code is querying for Column A and Column B on given filters, but you also want to return the values of Column C and Column E, you might want to create an index on Column A and B with the INCLUDE option containing Column C and E. That way a single index seek will return everything you need, as there's no need to use index / table scan to retrieve the other values on the same row.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.