| |
 |
|
SQL Server
full scan vs. index scan
|
SQL Server Table vs. Index Scans
When are scans good? If the objects are small in physical size, it
is usually more efficient for SQL Server to cache the object and
scan it rather than utilizing an indexing strategy. If a query needs
to return all the rows in a particular table and a covering index
does not exist, a scan is the only door left open to SQL Server.
However, large table scans should be avoided as they take serious
resources to perform and often flood the buffer cache with data
pages that are not likely to be re-read. Indexing is usually the
remedy for such situations; however, it must be ensured that the
actual WHERE clause is not written in a way that negates the use of
an index.
If an indexing strategy will not work and only a subset of a table’s
data is needed, the DBA can investigate the use of partitioning to
cut down on scan times. In SQL Server 7 and 2000, tables can be
manually partitioned through a technique called horizontal
partitioning, but with SQL Server 2005, full object partitioning is
supported through DDL. Horizontal partitioning will be covered later
in an upcoming section.
In any event, the savvy DBA should be on the lookout for SCAN
operations that show up in the EXPLAIN plans versus SEEKs. SEEKs
attempt to go directly to the rows necessary to fulfill a request;
whereas scans read the whole object.
Finally, using some functions and expressions in WHERE predicates
can totally negate the use of indexes that could otherwise be used
by the optimizer. An example of a suppression WHERE predicate would
be as follows:
select
count(*)
from
patient
where
substring(patient_name,1,5) = 'JOHNS'
To utilize an index, the query could be rewritten in the following
fashion:
select
count(*)
from
patient
where
patient_name like 'JOHNS%'
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
