SQL Server Query optimizer – Scan Vs Seek

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

Scan
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.

Seek
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.

More References:

Reference 1

Reference 2

Advertisements

One thought on “SQL Server Query optimizer – Scan Vs Seek

  1. Pingback: SSRS Report is taking forever – OPTION (OPTIMIZE FOR UNKNOWN) hint – Parameter sniffing | Vikas D More

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s