SQL Server Blog

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.

You may have seen the term SARGable in blogs or forums and wondered what it means. To start with, SARGable is pseudo-acronym which stands for Search ARGument. A query is considered sargable if the WHERE clause can take advantage of an index to speed up the query. A non-sargable query implies that the WHERE clause (or part of it) cannot use any available indexes which could result in a table or index scan and possibly slow down query performance.

You could possibly have a non-sargable query if you use filters like <>, !=, NOT, NOT IN, LIKE ‘%Clive’, NOT LIKE and NOT EXISTS. These filters could cause the optimiser to not use an index, although not always!

Filters such as =, >, <, <=, >=, IN, LIKE ‘Clive%’ could allow the optimiser to use available indexes to speed up query performance.

As databases evolve, you may find that some indexes are no longer used. This is something I have seen a lot in my current job.

This script will allow you to see which indexes are used and whether they are used for seeks, scans or key lookups.

SELECT
   SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]) AS [Object Name],
   I.[name] AS [Index Name],
   S.[user_seeks] AS [User Seeks],
   S.[user_scans] AS [User Scans],
   S.[user_lookups] AS [User Lookups]
FROM sys.dm_db_index_usage_stats S INNER JOIN sys.indexes I
   ON I.[object_id] = S.[object_id] AND I.[index_id] = S.[index_id]
INNER JOIN sys.tables T
   ON T.[object_id] = I.[object_id]
INNER JOIN sys.schemas SC
   ON T.[schema_id] = SC.[schema_id]
WHERE
   OBJECTPROPERTY(S.[object_id],’IsUserTable’) = 1 AND
   OBJECTPROPERTY(S.[object_id], ‘IsMSShipped’) = 0 AND
   S.[database_id] = DB_ID(DB_NAME())
ORDER BY
   SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]), I.[index_id]