Seeks vs Scans
What is the difference between a seek and a scan? It can be quite a lot, actually!
By definition, a scan will touch on every row in the table or index and retrieve only those rows which match the query criteria. On the other hand, a seek will use available indexes to locate the data.
One thing to note is that I mentioned that a seek will use available indexes. While a table may have an index, SQL Server may still choose to perform a scan if the scan is considered less expensive – for example, a small table may be subject to a scan over a seek.
See the following examples of queries, IO statistics and the execution plan to demonstrate a seek and a scan. For these queries, I am using the AdventureWorks2008 database and SQL Server 2008. If you do not have a copy of this database, you can get it here : AdventureWorks2008 Download
Statistics Output :
Table ‘Person’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Why does this query perform a seek? Simply, there is an index (IX_Person_LastName_FirstName_MiddleName) on our search criteria. For this operation, I have also observed only 2 logical reads, which is excellent performance.
Now to demonstrate a scan.
Statistics Output :
Table ‘Person’. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If you now observe the execution plan, you will see that the query optimizer is now using an index scan, however, it is still scanning the same index that was used in the optimal search. Additionally, if you look at the statistics of this query, you will now see that there are 105 logical reads.
So why do we get a scan if, in the examples above, we exclude the surname from the where clause? The query optimiser, in this case, considers that a scan is more efficient because the index index is not selective enough. While the FirstName column is covered, with it not being the first column within the composite index, the optimiser decides the scan is a more efficient way of returning the resultset. If you change the where clause to just search on LastName with a search predicate of Williams, the optimiser will choose an index seek.
So is an index scan bad? Yes and no, really. If you have a large table, then an index scan will be resource intensive and possibly take a long time to complete. However, on a small table, such as the one in this article, performance is not really affected. However, there is a small caveat to this. If your query is executed several thousands of times an hour, an index scan will use a lot more resources over this time period than a seek, so you do need to understand the load of your queries and determine whether a scan is appropriate, even on a small table.