Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter