1024
Part VII: Performance Tuning and Optimization
It’s a common saying that Select * is a poor practice because it returns too many, and
often unnecessary, columns in the result set — the extra data is considered wasteful if it
is not needed. I agree that Select * is a poor practice, but often the reason isn’t due to
the extra network traffi c; it’s the bookmark lookup that is almost always generated by a
Select *. However, the extra network traffi c associated with bringing back more data
than is needed can signifi cantly hinder application throughput.This query builds on the last bookmark lookup query and sheds a little more light on the
bookmark lookup problem; the difference is that this query requests only one column that’s
not available from the nonclustered index.SELECT WorkOrderID, StartDate
FROM Production.WorkOrder
WHERE ProductID = 757;Consider the performance difference (again, refer to Table 45-1) between this query path
and the select * bookmark lookup query path. Their performance is nearly identical.It doesn’t take many columns to force a bookmark lookup; a single column missing from
the nonclustered index means SQL Server must also look to the clustered index to solve the
query.There are only two ways to avoid the bookmark lookup problem:■ (^) Filter by the clustered index key columns, so the query can be satisfi ed using the
clustered index (Query Path 2 or 3).
■ (^) Design a covering index (the next query path).
Query Path 6 — Covering Index
If a nonclustered index includes every column required by the query (and that means every
column referenced by the query: select columns, join on condition columns, group by
columns, where clause columns, and windowing columns), SQL Server’s query optimizer
can choose to execute the query using only that nonclustered index. When this occurs the
index is said to cover the needs of the query, in other words, it’s a covering index.
This is an important concept to grasp to understand how nonclustered indexes operate.
A nonclustered index is a separate data structure than the base table. By default, non-
clustered indexes have the same number of rows as the base table. In this aspect, you can
think of a nonclustered index as a smaller sorted table — one that you can access quickly.
When it makes sense, the query optimizer can choose to use ONLY this separate nonclus-
tered index structure to satisfy a query. If this happens, the base table isn’t even used in
the query — only the nonclustered index.
A covering index is a concept that applies only to nonclustered indexes and only in the con-
text of a query. There is no such thing as a covering index as a single entity; it is applicable
only in the context of a query that uses the index.
c45.indd 1024c45.indd 1024 7/31/2012 10:16:41 AM7/31/2012 10:16:41 AM
http://www.it-ebooks.info