Database Reference
In-Depth Information
After adding this new index, run the
UPDATE
command again.
UPDATE dbo.Test1
SET C1 = 1,
C2 = 1
WHERE C2 = 1;
The total number of logical reads for this
UPDATE
statement decreases from 47 to 20 (=15 + 5).
Table 'Test1'. Scan count 1, logical reads 15
Table 'Worktable'. Scan count 1, logical reads 5
■
a
worktable
is a temporary table used internally by SQL Server to process the intermediate results of a query.
Worktables are created in the tempdb database and are dropped automatically after query execution.
Note
The examples in this section have demonstrated that although having an index adds some overhead cost to
action queries, the overall result is a decrease in cost because of the beneficial effect of indexes on searching, even
during updates.
Index Design Recommendations
The main recommendations for index design are as follows:
WHERE
clause and
JOIN
criteria columns.
•
Examine the
•
Use narrow indexes.
•
Examine column uniqueness.
•
Examine the column data type.
•
Consider column order.
•
Consider the type of index (clustered versus nonclustered).
Let's consider each of these recommendations in turn.
Examine the WHERE Clause and JOIN Criteria Columns
When a query is submitted to SQL Server, the query optimizer tries to find the best data access mechanism for every
table referred to in the query. Here is how it does this:
1.
The optimizer identifies the columns included in the
WHERE
clause and the
JOIN
criteria.
2.
The optimizer then examines indexes on those columns.
3.
The optimizer assesses the usefulness of each index by determining the selectivity of the
clause (that is, how many rows will be returned) from statistics maintained on the index.
4.
Constraints such as primary keys and foreign keys are also assessed and used by the
optimizer to determine selectivity of the objects in use in the query.
5.
Finally, the optimizer estimates the least costly method of retrieving the qualifying rows,
based on the information gathered in the previous steps.
