Well-designed indexes
can reduce disk I/O operations and consume fewer system resources therefore
improving query performance. Indexes can be helpful for a variety of queries
that contain SELECT, UPDATE, DELETE, or MERGE statements. Consider the querySELECT Title, HireDate FROM
HumanResources.Employee WHERE EmployeeID = 250 in the AdventureWorks2012 database. When this query is
executed, the query optimizer evaluates each available method for retrieving
the data and selects the most efficient method. The method may be a table scan,
or may be scanning one or more indexes if they exist.
When performing a table
scan, the query optimizer reads all the rows in the table, and extracts the
rows that meet the criteria of the query. A table scan generates many disk I/O
operations and can be resource intensive. However, a table scan could be the
most efficient method if, for example, the result set of the query is a high
percentage of rows from the table.
When the query optimizer
uses an index, it searches the index key columns, finds the storage location of
the rows needed by the query and extracts the matching rows from that location.
Generally, searching the index is much faster than searching the table because
unlike a table, an index frequently contains very few columns per row and the
rows are in sorted order.
The query optimizer
typically selects the most efficient method when executing queries. However, if
no indexes are available, the query optimizer must use a table scan. Your task
is to design and create indexes that are best suited to your environment so
that the query optimizer has a selection of efficient indexes from which to
select. SQL Server provides theDatabase Engine
Tuning Advisor to help with the analysis of your database
environment and in the selection of appropriate indexes
No comments:
Post a Comment