1002
Part VIII: Performance Tuning and Optimization
Icon Defi nition DescriptionIndex seek
(nonclustered)A nonclustered index seek navigates the B-tree index from
the root node, through the intermediate nodes, to the leaf
node, and fi nally to the row. The benefi t of a nonclustered
index seek is that it tends to be narrow (have few columns),
so more rows can fi t on a page.
When the correct row is identifi ed, if all the required col-
umns are found in the index, then the seek is complete
because the index covers the needs of the query.
If a range is required, an index seek operation can seek to
the start of the range and then sequentially read to the
end of the range.
RID lookup The RID lookup locates rows in the data pages of a heap
(table without a clustered index). Typically, a RID lookup
works with a nested-loop to locate the data pages follow-
ing a nonclustered index seek or scan.
Filter In some situations, SQL Server retrieves all the data from a
table and then uses fi lter operations to select the correct
rows.
Sometimes the Query Optimizer uses a Filter for perfor-
mance reasons, but it’s more often due to the lack of a
useful index.
Sort In some situations SQL Server retrieves data and needs to
sort it to prepare it for another operator. Such operations
could involve sorting a result set such as in an ORDER BY
clause or to prepare a result set to be received by a MERGE
JOIN. Proper indexes can usually alleviate some or all of
the overhead you see associated with a Sort operator. Sort
operations also often involve a memory grant, which is a
memory allocation that SQL Server must reserve to per-
form the operation.
Spool In a spool operation, SQL Server saves off a temporary set
of data and revisits the data in later operations.Summary
You can never become too good at performance tuning and optimization, and that skill is
always in high demand because it requires a knowledge of so many different parts of the
database engine.TABLE 44 -1 (continued)c44.indd 1002c44.indd 1002 7/31/2012 10:19:29 AM7/31/2012 10:19:29 AM
http://www.it-ebooks.info