In this article we shall examine the two modes of data search and retrieval using indexes- index seek and index scan, and the differences between the two.
Firstly, let us revisit indexes briefly. An index in a SQL Server database is analogous to the index at the start of a book. That is, its function is to allow you to quickly find the data you are searching for inside the book; in the case of a database, the “book” is a table.
An index scan means that SQL Server reads all rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all of the rows of the index are examined instead of the table directly. This is sometimes contrasted to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.
You may wonder why the Query Optimizer may choose to do an index or table scan. Surely it is much faster to first look up data using an index than to go through all the rows in a table? In fact, for small tables data retrieval via an index or table scan is faster than using the index itself for selection. This is because the added overhead of first reading the index, then reading the pages containing the rows returned by the index, does not offer any performance improvement for a table with only a few rows.
Other reasons to use an index scan would be when an index is not selective enough, and when a query will return a large percentage (greater than 50%) of rows from the table. In such cases the additional overhead of first using the index may result in a small degradation of performance.
An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage (less than 10 or 15%) of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; this is highly beneficial, in performance terms, when a table has a very large number of rows.
It is also worth noting that it is usually not worthwhile to create indexes on low-cardinality columns as they would rarely be used by the Query Optimizer. A low-cardinality column is one that contains a very small range of distinct values, for example a ‘Gender’ column would have only two distinct values- Male or Female. An example of a high-cardinality column is of course the primary key column, in which each value is distinct.
In summary, the Query Optimizer generally tries to perform an index seek. If this is not possible or beneficial (for example when the total number of rows is very small) then an index scan is used instead.
Reference : Pinal Dave (https://blog.sqlauthority.com)
31 Comments. Leave new
Very nice query.
With an extra field sic.key_ordinal and order by clause more readible, because the column fields are then in the exact order of the index:
SELECT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sic.key_ordinal, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates, sis.last_user_seek
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID(‘prdomgcod’) AND sis.OBJECT_ID in
( OBJECT_ID(‘oas_docline’), OBJECT_ID(‘oas_balance’))
order by
OBJECT_NAME(sis.OBJECT_ID), si.name, sic.key_ordinal, sc.Name,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates, sis.last_user_seek
GO
Hi Pinal,
Its really nice article about difference between Index seek and scan
Hi Dave,
It is really nice article, to understanding the between Index seek and Index scan seek.
Awesome article Dave! :)
Also, here you can find a complementary description of a index seek vs. index scan with practical examples of when an index scan or an index seek is generated based on the query string.
Also, there are some guides and tricks to convert an index scan into an index seek.
Feel free to take a review!
Congratulations for your blog :)
Awesome!
very helpful article.. great explanation of index scan VS seek
Well Defined and beautifully explained……Thanks ( Regards, Shaikh Faiyazuddin)
about
“not worthwhile to create indexes on low-cardinality columns”
Sometimes it also dependes in the data, and the use you make of it.
Imagin you have a table of tasks, with a bit column that says they have been executed
and 99% of the time you just need to query the tasks not executed to display in a grid on a web page
and 1% to query for the executed ones to generating monthly reports
is that column a good candidate for an index ?
In my table arround 2laks records are there ..and we are fetching each and every time all records..when i checked execution plan index scan is happening..so my doubt is, if we are fetching all records from table what is the befst index scan or index seek ?