1018
Part VII: Performance Tuning and Optimization
FIGURE 45-6
A clustered index seek navigates the B-tree index and locates the row effi ciently.
Clustered Index
PK_WorkOrder_WorkOrderIDConventional wisdom holds that this is the fastest possible query path, and it is snappy
when returning a single row; however, from rows returned on a per millisecond basis, it’s
one of the slowest query paths.A common myth is that seeks can return only single rows, and that’s why seeking mul-
tiple rows would be slow compared to scans. As the next two query paths indicate, that’s
not true.Query Path 3 — Range Seek Query
The third query path selects a narrow range of consecutive values using a between opera-
tor in the where clause:SELECT *
FROM Production.WorkOrder
WHERE WorkOrderID BETWEEN 10000 AND 10010;The query optimizer must fi rst determine if there’s a suitable index to select the range. In
this case it’s the same key column in the clustered index as in the Query Path 2.A range seek query has an interesting query execution plan. The seek predicate (listed in
the index seek properties), which defi nes how the query is navigating the B-tree, has both
a start and an end to the seek predicate, as shown in Figure 45-7. This means the operation
is seeking the fi rst row and then quickly scanning and returning every row to the end of
the range (refer to Figure 45-8).c45.indd 1018c45.indd 1018 7/31/2012 10:16:40 AM7/31/2012 10:16:40 AM
http://www.it-ebooks.info