Database Reference
In-Depth Information
Figure 2-13.
Index seek execution plan
As you can guess, index seek is usually more efficient than index scan because SQL Server processes just the
subset of rows and data pages, rather than scanning the entire table.
■
technically speaking, there are two kinds of index seek operations. the first is called a
singleton lookup
,
where sQl server seeks and returns a single row. You can think about the
where CustomerId = 2
predicate as an
example. the other type of index seek operation is called a
range scan
, and it requires sQl server to find the lowest or
highest value of the key and scan (either forward or backward) the set of rows until it reaches the end of scan range. the
predicate
where CustomerId between 4 and 7
leads to the range scan. both cases are shown as
INDEX SEEK
opera-
tions in the execution plans.
Important
as you can guess, in some cases, range scans force sQl server to process all or a large number of data pages from the
index. For example, if you changed the query to use a
where CustomerId > 0
predicate, sQl server would read all rows/
pages, even though you would have an index seek operator displayed in the execution plan. You must keep this behavior
in mind and always analyze the efficiency of range scans during query performance tuning.
There is a concept in relational databases called
SARGable predicates
, which stands for
S
earch
Arg
ument
able
.
The predicate is SARGable if SQL Server can utilize an index seek operation if the index exists. This limits the search
by specifying either an exact match or range of values to deal with. Obviously, it is beneficial to write queries using
SARGable predicates and utilize index seek whenever possible.
SARGable predicates include the following operators:
=
,
>
,
>=
,
<
,
<=
,
IN
,
BETWEEN
, and
LIKE
(in case of prefix
matching). Non-SARGable operators include
NOT
,
<>
,
LIKE
(in case of non-prefix matching), and
NOT IN
.
Another circumstance for making predicates non-SARGable are using functions or mathematical calculations
against the table columns. SQL Server has to call the function or perform the calculation for every row it processes.
Fortunately, in some of cases, you can refactor the queries to make such predicates SARGable. Table
2-1
shows a few
examples of this.
