In the previous blog about parameter sniffing, I mentioned about SQL server performing Table or Index scan vs seek operation.
Now what is this scan vs seek? I like Craig Freedman’s blog on this topic.
Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes. They appear in nearly every query plan.
A scan touches every row in the table whether or not it qualifies.
So the cost is proportional to the total number of rows in the table.
Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
However, if the table is large and if most of the rows do not qualify, SQL Server touches many more pages and rows and perform many more I/Os than is necessary.
For example, consider the following query:
select OrderDate from Orders where OrderKey = 2
With a scan, SQL Server reads each row in the orders table, evaluate the predicate “where OrderKey = 2” and, if the predicate is true (i.e., if the row qualifies), return the row.
A seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Thus, a seek is generally a more efficient strategy if we have a highly selective seek predicate query; that is, if we have a seek predicate that eliminates a large fraction of the table.
In the example, if there is an index on OrderKey, a seek may be a better plan. With a seek, SQL Server uses the index to navigate directly to those rows that satisfy the predicate.